Introduction
A top-down approach is strongly recommended when analyzing database performance issues. The sequence of topics in this guide should not be interpreted as a fixed starting point; effective troubleshooting depends on assessing the system holistically and narrowing the focus based on observed symptoms.
Begin at the infrastructure level and progressively move toward finer-grained components:
Database Server Level
Assess overall system health, including CPU utilization, memory and swap usage, disk I/O, and network performance using operating system monitoring tools.
Oracle Instance Configuration
Review instance-level settings such as SGA sizing, buffer cache configuration, PGA usage, and background process behavior.
Database Objects and Storage
Examine tablespace usage, table and index sizes, partitioning strategies, and storage layout to identify design or capacity-related bottlenecks.
SQL Workload Analysis
Analyze executed SQL statements to detect inefficient queries, excessive executions, suboptimal execution plans, or missing indexes.
For additional reference and deeper diagnostics, consult relevant Oracle Knowledge Base (KB) documentation.
Troubleshooting Performance Issues (Doc ID 1377446.1)
Identify long running SQL queries
v$session_longops => Operations running for longer than 6 sec (incl. internal functions, e.g. collecting stats, rman activities)
SELECT
sid,
serial#,
TO_CHAR(RAWTOHEX(sys_guid())) AS joinid,
opname,
username,
target,
( start_time - DATE '1970-01-01' ) * 86400 AS start_time,
( sofar / totalwork ) * 100 AS completion,
time_remaining
FROM
v$session_longops
WHERE
time_remaining > 0;
SID SERIAL# JOINID OPNAME USERNAME TARGET START_TIME COMPLETION TIME_REMAINING
--- ------- ---------------------------------- ---------- -------- ----------------------------- ---------- ---------- --------------
18 18878 0EAC47BD9DF579BEE063AA07030A4C4B Table Scan SYS SYS.PART_TEST_ACCINS_ROWID 1705006168 1.19047619 94039
Further actions: Check Wait Events. If query is slow it usually means there are wait events caused by locks, high IO due to missing indexes, Oracle background processes blocking the requested data blocks
Top 10 SQL statements with the highest user I/O waits
COL sql_text FOR A50
SELECT *
FROM (
SELECT
sql_text,
sql_id,
elapsed_time,
cpu_time,
user_io_wait_time
FROM
sys.v_$sqlarea
ORDER BY
user_io_wait_time DESC
)
WHERE
ROWNUM < 11;
SQL_TEXT SQL_ID ELAPSED_TIME CPU_TIME USER_IO_WAIT_TIME
-------------------------------------------------- ------------ ------------- ---------- ------------------
SELECT * FROM PART_TEST_ACCINS_ROWID 9b8f4kzq7m3p 824563210 123456789 701106421
SELECT COUNT(*) FROM TRANSACTION_LOG 2c4m1s9x8h2r 543210987 98765432 444445555
UPDATE CUSTOMER_DATA SET STATUS = :1 7a6c9f2m4q9t 312456789 234567890 77788899
INSERT INTO AUDIT_LOG VALUES (:1,:2,:3) 3f8d9k2p4s1r 198765432 98765432 100000000
DELETE FROM TEMP_STAGE WHERE CREATED < :1 6m9p8f4r2k1s 154321987 65432100 88889987
Blocking Sessions
Check for blocking/waiting sessions:
SQL> select SID from v$lock where block>0;
SQL> SELECT blocking_session, sid "BLOCKED_SESSION", seconds_in_wait/60 "WAIT_TIME(MINUTES)" FROM v$session WHERE blocking_session is not NULL order by 3;
BLOCKING_SESSION BLOCKED_SESSION WAIT_TIME(MINUTES)
---------------- --------------- ------------------
1262 1164 4.7
963 3272 4.96666667
1262 3147 4.96666667
1262 597 6.28333333
Find affected table/process/…
SQL> SELECT
b.sid,
c.object_name,
d.type,
d.lmode,
d.request,
d.block
FROM
v$locked_object a,
v$session b,
dba_objects c,
v$lock d
WHERE
b.sid IN (1262, 963)
AND b.sid = a.session_id
AND a.object_id = c.object_id
AND b.sid = d.sid;
SID OBJECT_NAME TY LMODE REQUEST BLOCK
---------- ----------------------- -- ------ ------- ------
963 DEVICE_PARAMETER_VAL AE 4 0 0
1262 DEVICE_PARAMETER_VAL TM 3 0 0
1262 DEVICE_PARAMETER_VAL TX 6 0 1
1262 DEVICE_PARAMETER_VAL AE 4 0 0
-- NOTE:
-- BLOCK = 1 indicates this session is blocking other sessions
-- LMODE / REQUEST = 6 represents an Exclusive (X) lock
SQL> SELECT
ses.sid,
sql_text
FROM
v$session ses,
v$sqltext sql
WHERE
ses.sql_hash_value = sql.hash_value
AND ses.sid = 1262
ORDER BY
sql.piece;
SID SQL_TEXT
---------- ----------------------------------------------------------------
1262 UPDATE DEVICE_PARAMETER_VAL
1262 SET VAL = :1
1262 WHERE DEVICE_ID = :2
1262 AND DEVICE_PARAMETER_ID = :3
Get serial id (to kill session via sqlplus), OS PID, … of blocking session:
SQL> SELECT SES.SID, SES.PROCESS OS_ID, SES.SERIAL#, SES.STATUS, SES.PROGRAM, PRC.SPID FROM V$SESSION SES, V$PROCESS PRC WHERE SES.PADDR=PRC.ADDR AND SES.SID=1262;
SID OS_ID SERIAL# STATUS PROGRAM SPID
---------- ------------------------ ---------- -------- ------------------------------------------------ ------------------------
1262 1234 60287 ACTIVE JDBC Thin Client 194778
kill session holding lock:
ALTER SYSTEM KILL SESSION '1262,60287';
Show indexes for table
SQL> select TABLE_NAME, COLUMN_NAME, INDEX_NAME from dba_ind_columns where TABLE_NAME= 'table_name'
Show query execution plan
SQL> explain plan for sql statement;
SQL> select * from table(dbms_xplan.display);
Response time metrics
https://www.oracle.com/technical-resources/articles/schumacher-analysis.html
:warning: no analysis of historical bottlenecks. Snapshots v$sysmetric/V_$SYSMETRIC_SUMMARY 1h, v$sysmetric_history 2h !
DBA_HIST_SYSMETRIC_SUMMARY / DBA_HIST_SYSMETRIC_HISTORY might help for post-mortem analysis.
- v$sysstat: RAW Live performance metrics
- v$sysmetric: RAW Live metrics derived from sysstat or somewhere else
- v$sysmetric_history: Last one hour, metrics for each metric in v$sysmetric for about 15sec or 60 sec interval
- v$sysmetric_summary: Last one hour, metrics with aggregated values of history of one hour for each metric, i.e max,min,avg
- dba_hist*: Last seven days
Snapshot of Database Wait Time Ratio and Database CPU Time Ratio
SQL> select METRIC_NAME, VALUE from SYS.V_$SYSMETRIC where INTSIZE_CSEC=(select max(INTSIZE_CSEC) from SYS.V_$SYSMETRIC) AND METRIC_NAME IN ('Database CPU Time Ratio','Database Wait Time Ratio');
METRIC_NAME VALUE
---------------------------------------------------------------- ----------
Database Wait Time Ratio 86.2720309
Database CPU Time Ratio 13.7279691
CPU Time Ratio: CPU expended in the database / time spent by the database on user-level calls, should be ~90..95%
CPU Time Ratio last hour
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> select end_time, value from sys.v_$sysmetric_history where metric_name='Database CPU Time Ratio' order by 1;
END_TIME VALUE
------------------- ----------
2023-10-25 15:01:21 11.0031582
2023-10-25 15:02:21 21.1394656
Database efficiency
SQL> col METRIC_NAME for a50
SQL> select CASE METRIC_NAME
WHEN 'SQL Service Response Time' then 'SQL Service Response Time (secs)'
WHEN 'Response Time Per Txn' then 'Response Time Per Txn (secs)'
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MINVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MINVAL/100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((MAXVAL/100),2)
WHEN 'Response Time Per Txn' then ROUND((MAXVAL/100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN 'SQL Service Response Time' then ROUND((AVERAGE/100),2)
WHEN 'Response Time Per Txn' then ROUND((AVERAGE/100),2)
ELSE AVERAGE
END AVERAGE
from SYS.V_$SYSMETRIC_SUMMARY
where METRIC_NAME in ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio',
'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn',
'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec')
ORDER BY 1;
METRIC_NAME MININUM MAXIMUM AVERAGE
-------------------------------------------------- ---------- ---------- ----------
CPU Usage Per Sec 0 159.68303 102.073783
CPU Usage Per Txn 0 5.01134626 1.11262634
Database CPU Time Ratio 0 66.5159457 13.8153065
Database Wait Time Ratio 0 98.1949155 86.1846935
Executions Per Sec 0 4637.74779 1741.67583
Executions Per Txn 0 50.632561 17.1732087
Response Time Per Txn (secs) 0 .56 .12
SQL Service Response Time (secs) 0 .01 0
User Transaction Per Sec 0 155.30568 101.860536
Response Time per transaction: derived from total time that user calls spend in the database (DB time) and the number of commits and rollbacks performed
User activities responsible for high load
SQL> select case db_stat_name
when 'parse time elapsed' then'soft parse time'
else db_stat_name
end db_stat_name,
case db_stat_name
when 'sql execute elapsed time' then time_secs - plsql_time
when 'parse time elapsed' then time_secs - hard_parse_time
else time_secs
end time_secs,
case db_stat_name
when 'sql execute elapsed time' then round(100 * (time_secs - plsql_time) / db_time,2)
when 'parse time elapsed' then round(100 * (time_secs - hard_parse_time) / db_time,2)
else round(100 * time_secs / db_time,2)
end pct_time
from
(select stat_name db_stat_name, round((value/1000000),3) time_secs from sys.v_$sys_time_model where stat_name not in ('DB time', 'background elapsed time', 'background cpu time','DB CPU')),
(select round((value / 1000000),3) db_time from sys.v_$sys_time_model where stat_name = 'DB time'),
(select round((value / 1000000),3) plsql_time from sys.v_$sys_time_model where stat_name = 'PL/SQL execution elapsed time'),
(select round((value / 1000000),3) hard_parse_time from sys.v_$sys_time_model where stat_name = 'hard parse elapsed time')
order by 2 desc;
DB_STAT_NAME TIME_SECS PCT_TIME
---------------------------------------------------------------- ---------- ----------
sql execute elapsed time 3620824.56 24.57
soft parse time 45450.294 .31
connection management call elapsed time 28296.733 .19
PL/SQL execution elapsed time 6645.352 .05
hard parse elapsed time 5277.531 .04
...
Description of metrics: see https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SESS_TIME_MODEL.html
Historical response times
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL "Min Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Avg Response Time (msecs)" FORMAT 999,999,999,999.99
COL "Max Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
ROUND (MINVAL * 10, 2) "Min Response Time (msecs)",
ROUND (AVERAGE * 10, 2) "Avg Response Time (msecs)",
ROUND (MAXVAL * 10, 2) "Max Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME ASC;
BEGIN_TIME END_TIME Min Response Time (msecs) Avg Response Time (msecs) Max Response Time (msecs)
----------------- ----------------- ------------------------- ------------------------- -------------------------
16-OCT-2023 23:00 16-OCT-2023 23:59 .00 .58 .63
16-OCT-2023 23:59 17-OCT-2023 00:59 .00 .58 .68
17-OCT-2023 00:59 17-OCT-2023 02:00 .00 .58 .64
17-OCT-2023 02:00 17-OCT-2023 03:00 .00 .59 .72
...
25-OCT-2023 11:59 25-OCT-2023 12:59 .00 .55 .60
25-OCT-2023 12:59 25-OCT-2023 14:00 .00 .56 .61
25-OCT-2023 14:00 25-OCT-2023 15:00 .00 .56 .60
208 rows selected.
## per second ...
SET LINESIZE 200 PAGESIZE 50000
COL BEGIN_TIME FORMAT A17
COL END_TIME FORMAT A17
COL "Response Time (msecs)" FORMAT 999,999,999,999.99
SELECT TO_CHAR (BEGIN_TIME, 'DD-MON-YYYY HH24:MI') BEGIN_TIME,
TO_CHAR (END_TIME, 'DD-MON-YYYY HH24:MI') END_TIME,
ROUND (VALUE * 10, 2) "Response Time (msecs)"
FROM DBA_HIST_SYSMETRIC_HISTORY
WHERE METRIC_NAME = 'SQL Service Response Time'
ORDER BY BEGIN_TIME ASC;
BEGIN_TIME END_TIME Response Time (msecs)
----------------- ----------------- ---------------------
25-OCT-2023 14:59 25-OCT-2023 15:00 .57
25-OCT-2023 14:58 25-OCT-2023 14:59 .54
25-OCT-2023 14:57 25-OCT-2023 14:58 .58
..
25-OCT-2023 14:58 25-OCT-2023 14:59 .54
25-OCT-2023 14:59 25-OCT-2023 15:00 .57
12480 rows selected.
## detailed overview:
set arraysize 5000
set termout off
set echo off verify off
COLUMN blocksize NEW_VALUE _blocksize NOPRINT
select distinct block_size blocksize from v$datafile;
COLUMN dbid NEW_VALUE _dbid NOPRINT
select dbid from v$database;
COLUMN instancenumber NEW_VALUE _instancenumber NOPRINT
select instance_number instancenumber from v$instance;
ttitle center 'Database Performance Metrics' skip 2
set pagesize 50000
set linesize 300
col AAS format 99.00 heading "A|A|S"
col Sessions format 9999 heading "Sess"
col Logons format 9999 heading "Log|CNT"
col OSProcs format 99 heading "OS|Load|Avg"
col CPUSec format 999.00 heading "CPU|Sec"
col DBTIMESec format 99999.00 heading "DB|Sec"
col CallsSec format 9999 heading "User|Calls|Sec"
col SQLResponseSec format 99 heading "SQL|Resp|Sec"
col CommitsSec format 99999 heading "Commit|Sec"
col BlocksSec format 99999 heading "Block|Change|Sec"
col ExecSec format 99999 heading "Exec|Sec"
col HParseSec format 99999 heading "HPARSE|Sec"
col Cursors format 99999 heading "Open|Cursor|Count"
col PGAUsed format 99999 heading "PGA|Used|MB"
col TempUsed format 99999 heading "Temp|Used|MB"
col PReadsSec format 99999999 heading "PReads|Direct|Sec"
col LreadsSec format 99999999 heading "Logical|Reads|Sec"
col SpoolFree% format 999 heading "SPool|Free%"
col NworkMB/s format 99999 heading "Nwork|MB|Sec"
col PReadsMB/s format 9999 heading "PREADS|MB|Sec"
col PWritesMB/s format 9999 heading "PWRITES|MB|Sec"
col TotalMB/s format 999999999 heading "Total|I/O|MB|Sec"
col FullScans format 999 heading "FTab|Scans|Sec"
Col RedoSec format 999 heading "Redo|MB|Sec"
col LockWait format 999 heading "Lock|Waits|Sec"
set pagesize 120;
set linesize 300;
col day for a8;
break on metric_name;
select
snap_id,to_char(BEGIN_TIME,'DD-MON HH24') SNAPTIME,
max(decode(metric_name,'Average Active Sessions',MAXVAL,0)) "AAS",
Max(decode(metric_name,'Current Logons Count',MAXVAL,0)) "Sessions",
max(decode(metric_name,'Session Count',MAXVAL,0)) "Logons",
Max(decode(metric_name,'Current OS Load',MAXVAL,0)) "OSProcs",
max(decode(metric_name,'CPU Usage Per Sec',MAXVAL,0)) "CPUSec",
max(decode(metric_name,'Database Time Per Sec',MAXVAL,0)) "DBTIMESec",
max(decode(metric_name,'User Calls Per Sec',MAXVAL,0)) "CallsSec",
max(decode(metric_name,'SQL Service Response Time',MAXVAL,0)) "SQLResponseSec",
max(decode(metric_name,'User Commits Per Sec',MAXVAL,0)) "CommitsSec",
max(decode(metric_name,'DB Block Changes Per Sec',MAXVAL,0)) "BlocksSec",
max(decode(metric_name,'Redo Generated Per Sec',MAXVAL/1024/1024,0)) "RedoSec",
max(decode(metric_name,'Executions Per Sec',MAXVAL,0)) "ExecSec",
max(decode(metric_name,'Hard Parse Count Per Sec',MAXVAL,0)) "HParseSec",
max(decode(metric_name,'Current Open Cursors Count',MAXVAL,0)) "Cursors",
max(decode(metric_name,'Total PGA Allocated',MAXVAL/1024/1024,0)) "PGAUsed",
max(decode(metric_name,'Temp Space Used',MAXVAL/1024/1024,0)) "TempUsed",
max(decode(metric_name,'Physical Reads Direct Per Sec',MAXVAL,0)) "PReadsSec",
max(decode(metric_name,'Logical Reads Per Sec',MAXVAL,0)) "LreadsSec",
max(decode(metric_name,'Shared Pool Free %',MAXVAL,0)) "SpoolFree%",
max(decode(metric_name,'Enqueue Waits Per Sec',MAXVAL,0)) "Lockwait",
max(decode(metric_name,'Long Table Scans Per Sec',MAXVAL/1024/1024,0)) "FullScans",
max(decode(metric_name,'Network Traffic Volume Per Sec',MAXVAL/1024/1024,0)) "NworkMB/s",
max(decode(metric_name,'Physical Read Bytes Per Sec',MAXVAL/1024/1024,0)) "PReadsMB/s",
max(decode(metric_name,'Physical Write Bytes Per Sec',MAXVAL/1024/1024,0)) "PWritesMB/s",
max(decode(metric_name,'Physical Read Bytes Per Sec',MAXVAL/1024/1024,0))+
max(decode(metric_name,'Physical Write Bytes Per Sec',MAXVAL/1024/1024,0)) "TotalMB/s"
from dba_hist_sysmetric_summary A
where BEGIN_TIME > trunc(sysdate) - 1
and a.metric_name in
('Average Active Sessions',
'Current Logons Count',
'Session Count',
'Host CPU Utilization (%)',
'Session Count',
'Current OS Load',
'CPU Usage Per Sec',
'Database Time Per Sec',
'User Calls Per Sec',
'SQL Service Response Time',
'User Commits Per Sec',
'DB Block Changes Per Sec',
'Executions Per Sec',
'Hard Parse Count Per Sec',
'Current Open Cursors Count',
'Total PGA Allocated',
'Temp Space Used',
'Physical Reads Direct Per Sec',
'Logical Reads Per Sec',
'Network Traffic Volume Per Sec',
'Physical Read Bytes Per Sec',
'Physical Write Bytes Per Sec',
'Long Table Scans Per Sec','Enqueue Waits Per Sec','Shared Pool Free %','Redo Generated Per Sec'
)
group by snap_id, to_char(BEGIN_TIME,'DD-MON HH24')
order by to_char(BEGIN_TIME,'DD-MON HH24');
DB response time: see col “SQL Resp Time” + “User Calls” => SQL Service Response time per user call.
Log Switches per hour
Database spending lot of time on frequent log switches during normal business will impact performance.
Oracle recommendation is not more than 4..5 log switches per hour (more log switches for short periods of high load might be acceptable), otherwise consider to increase redo log size/number of log files.
Check log switch frequency (Example BDST with 1 period of high load after DB outage when loads of incoming meter reads was written):
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;
DAY HOUR TOTAL
--------------- ---- -----
2023-10-10 05 4
2023-10-10 06 3
2023-10-10 07 3
2023-10-10 08 3
2023-10-10 09 3
2023-10-10 10 3
2023-10-10 11 4
2023-10-10 12 4
2023-10-10 13 4
2023-10-10 14 3
2023-10-10 15 3
DAY HOUR TOTAL
--------------- ---- -----
2023-10-10 16 4
2023-10-10 17 10
2023-10-10 18 12
2023-10-10 19 3
2023-10-10 20 3
2023-10-10 21 4
2023-10-10 22 3
2023-10-10 23 4
2023-10-11 00 3
2023-10-11 01 3
2023-10-11 02 3
Performance Reports
Oracle keeps performance data in 1h snapshots with retention time of ~7 days. Reports usually list available snapshots to choose from.
:warning: If you generate AWR report for too many snapshots/long period Oracle might hide the root cause of performance issues by too much “averaging” of values. If period is too short the events pointing to root cause might be missing.
AWR (Automatic Workload Repository) Performance Report Scripts
- awrrpt.sql (AWR RePorTs for current single instance database)
- RAC: awrgrpt.sql (AWR Global RePorT for RAC)
Example standalone db:
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
WORKLOAD REPOSITORY report for
...
Summary of Findings
-------------------
Description Active Sessions Recommendations
Percent of Activity
-------------------------- ------------------- ---------------
1 Commits and Rollbacks 1.41 | 70.54 1
2 Undersized Redo Log Buffer .26 | 13.24 1
3 Top SQL Statements .23 | 11.74 2
...
Findings and Recommendations
----------------------------
Finding 1: Commits and Rollbacks
...
AWR Report covers (concentrate on):
- Check if number of sessions in report header at begin and end, to get an idea if general load was constant/increasing/decreasing during snapshot time:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 36419 22-Jun-23 15:00:03 263 4.4
End Snap: 36424 22-Jun-23 20:00:23 261 4.5
- Instance Efficiency: should be near 100%
- Buffer Nowait/Buffer Hit low (<95%) => increase buffer cache size
- Library Hit low => consider to increase shared pool
- Redo NoWait low => check redo buffer + redo log size
- In-memory Sort <100% => sorts happens on disk (slow!). Check parameter pga_aggregate_target, sort_area_size, hash_area_size.
- Soft Parse => how often executed SQL stmts were found in cursor cache.
- Latch Hit => (Latches = internal short-term locks to prevent concurrent processes from simultaneously updating same area of the SGA) If low check for latch free wait event in Top 10 list, and Latch sections in AWR
- Non-Parse CPU low => CPU works on parsing SQLs instead of executing it
- Execute to Parse => how often was SQl executed/how often parsed, reusage of SQL
- Parse CPU to Parse Elapsd (CPU time spent to parse SQL) low => waiting for resources to parse SQL
2. Top 10 Foreground (=application/user sessions) (also background = Oracle) Events by Total Wait Time:
most important section. Top wait events can quickly point to the overall database bottleneck. Check for
- “Concurrency” Wait class in Top 10
- high Total Wait Time + Wait Avg, or just high Wait Avg
- Examples:
- high “enq TX – row lock contention” => waiting for row locks. Go to
- high “User I/O” events / much of % DB time IO related => check “File IO Stats” and disk latency in OS monitoring, or “SQL ordered by User I/O Wait Time”
- high “db file scattered read” (“User I/O” wait class) => lots of full table scans, check for partitioning etc.
- high “log file sync” (“Commit” Wait class) => check “Background Wait Events” Avg wait for related events, e.g. “log file parallel write” (LGWR waiting to complete writes to redo)
- “Background Wait Events”: Avg wait for “log file parallel write” + “Log archive I/O” should be <10ms => storage/network latency
- for largest waits look at the “Wait Event Histogram”
- DB CPU >80-85%: check “SQL ordered by CPU Time” section and find sql causing CPU busy
- Examples:
- DB host Operating System Statistics: external resources like I/O, CPU, memory, network
- Time Model Statistics: high “DB time”
- Load Profile: physical reads, physical writes, hard parse to parse ratio, transactions per second
- hard parses: how often SQL is being fully parsed
- rows per sort: check for large (= expensive) sorts
- physical reads: data blocks not found in cache. Look at “SQL by Physical reads”
- Executes (SQL): If executes per second looks enormous then its a red flag
- Service Statistics + Service Wait Class Stats: which users/services are consuming most resources, check Time Waited (Wt Time) for Wait Classes with high Total Wts
Example:
Service Name
----------------------------------------------------------------
User I/O User I/O Concurcy Concurcy Admin Admin Network Network
Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time Total Wts Wt Time
--------- --------- --------- --------- --------- --------- --------- ---------
ZONOS.DST.COM
1742313 15724 13633 16 0 0 36023221 50
## 15724 Wt Time / 1742313 Total Wts User I/O ==> 0,009s (9ms) per wait => best wait time we can get is 5 ms, disks are stressed
- SQL ordered by Elapsed Time, CPU Time, Gets and Reads
- Buffer Pool Advisory: check if increasing cache would reduce physical IO
- PGA Memory Advisory: check if increasing PGA memory would increase Est PGA Cache Hit Ratio
- SGA Target Advisory: check if increase SGA target size would increase performance
- Findings and Recommendations
More Details:
- https://expertoracle.com/2018/02/06/performance-tuning-basics-15-awr-report-analysis/
- http://dbakeeda.blogspot.com/2016/05/step-by-step-how-to-analyze-awr-report.html
- https://www.linkedin.com/pulse/oracle-awr-reports-dummies-ravi-yogesh
AWR SQL Report Script
awrsqrpt.sql (statistics of a particular SQL statement)
SQL> @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
requests sql_id (v$session/v$sql)!
SQL> select sql.sql_id, sql.sql_text from v$sql sql, v$session sess where sql.sql_id = sess.sql_id and
AWR Compare Periods Report Scripts
awrddrpt.sql (RAC awrgdrpi. sql) – compare database performance between 2 time periods
SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
Example: compare 10-Oct-23 17-21h and 12-Oct-23 17-21h
SQL> @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
...
Enter value for num_days: ## select begin and end snapshot for 1st period
...
Enter value for num_days2: ## select begin and end snapshot for 2nd period
...
Enter value for report_name: /var/tmp/awrdiff_10Oct_12Oct
...
ASH (Active Session History) Report Script
ashrpt.sql (inspecting particular set of sessions, diagnose shorter duration performance issues that can go under-the-radar in an AWR report)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
RAC: ashrpti.sql (accepts “Report Targets”, e.g. SQL_ID, MODULE name, WAIT_CLASS, …)
SQL> @$ORACLE_HOME/rdbms/admin/ashrpti.sql
ASH Report covers:
- Top User/Background Events
- Top Service/Module
- Top Client IDs
- Top SQL Command Types, Top Phases of Execution
- Top Sessions, Top Blocking Sessions
- Top DB Objects, Top DB Files
- Example: Create ash report for 2 hours, starting at 10 Oct 18h
SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql
Wait Classes / Wait Events
might help to diagnose performance bottlenecks by identifying wait activities of current sessions.
Detailed description see
Examples Wait Events:
- Enqueue waits, e.g. “enq: TX – row lock contention“: Session waiting to get a lock on any DB object held by another session
- log file sync: (“commit latency”) Session waiting on commit/rollback for LGWR (Log Writer Process) confirmation finished writing log buffer to redo logs
- db file sequential read: SQL waiting for a single block to be read into the SGA from disk to complete
- db file scattered read: waits related to full table scans/fast full index scan, multiblock read into SGA
- buffer busy waits: waiting to access block in buffer cache because another sesison is modifying this block
Examples Wait Classes:
- Concurrency: waits for internal database resources such as latches
- User I/O: Waits for reading blocks from disk
- Application: Waits for row/table locks
- Commit: wait for redo log write confirmation after a commit (log file sync)
- “idle” class of wait events such as SQL*Net message from client merely indicate an inactive session, can be ignored
Oracle wait events and related wait class:
SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY wait_class, name;
ASH
V$ACTIVE_SESSION_HISTORY (DBA_HIST_ACTIVE_SESS_HISTORY) => statistics of where time was spent (what is session/sql stmt waiting for, …), samples non-idle sessions every 1 (10) sec
Most common wait events from V$ACTIVE_SESSION_HISTORY:
SQL> col TOTAL_WAIT_TIME for 99999999999999999999999999
SQL> SELECT event, SUM(wait_time + time_waited) total_wait_time FROM v$active_session_history GROUP BY event ORDER BY total_wait_time DESC fetch first 10 rows only;
EVENT TOTAL_WAIT_TIME
---------------------------------------------------------------- ---------------------------
23790749758
enq: TX - allocate ITL entry 2759997600
db file parallel write 963509324
virtual circuit wait 897412768
log file sync 255909096
gc current grant busy 249640043
enq: TX - row lock contention 172907246
Affected DB objects:
SQL> col OBJECT_NAME for A30
SQL> set lines 200
SQL> SELECT a.current_obj#, d.object_name, d.object_type, a.event, SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, dba_objects d
WHERE a.current_obj# = d.object_id and d.owner like '%10%'
GROUP BY a.current_obj#,
d.object_name,
d.object_type,
a.event
ORDER BY total_wait_time desc;
CURRENT_OBJ# OBJECT_NAME OBJECT_TYPE EVENT TOTAL_WAIT_TIME
------------ ------------------------------ ----------------------- ---------------------------------------------------------------- ---------------
79726 MDUS_UTIL_TIME_SERIES_REL TABLE enq: TX - allocate ITL entry 2692664642
79825 DEVICE_JOB TABLE enq: TX - row lock contention 143033059
79694 DEVICE_TELEMETRY TABLE enq: TX - allocate ITL entry 67332958
127205 MW_DMS_VEE INDEX direct path read 38416125
80337 pk_REGISTER_STATISTICS INDEX db file sequential read 21271670
79826 SYS_LOB0000079825C00018$$ LOB virtual circuit wait 18324129
79853 SYS_LOB0000079852C00013$$ LOB log file sync 17734847
79751 SYS_LOB0000079750C00014$$ LOB log file sync 17141698
79826 SYS_LOB0000079825C00018$$ LOB direct path write 14303998
135039 DEVICE_MEASUREMENT TABLE PARTITION db file sequential read 11703602
80485 k_SAP_JOB_UUID INDEX direct path write temp 10556774
SQL queries waits:
SQL> col USERNAME for a20
SQL> col SQL_TEXT for a100
SQL> SELECT a.user_id, u.username, s.sql_text, SUM(a.wait_time + a.time_waited) total_wait_time
FROM v$active_session_history a, v$sqlarea s, dba_users u
WHERE a.sql_id = s.sql_id AND a.user_id = u.user_id
GROUP BY a.user_id, s.sql_text, u.username
order by TOTAL_WAIT_TIME desc fetch first 20 rows only ;
USER_ID USERNAME SQL_TEXT TOTAL_WAIT_TIME
---------- -------------------- ---------------------------------------------------------------------------------------------------- ---------------------------
0 SYS select sql_id, sql_exec_id, dbop_name, dbop_exec_id, to_char(sql_exec_start, 'mm:dd:yyyy hh24:mi:ss' 3576510409
), to_char(first_refresh_time, 'mm:dd:yyyy hh24:mi:ss'), to_char(last_refresh_time, 'mm:dd:yyyy hh24
:mi:ss'), elapsed_time, px_servers_allocated, sid, session_serial#, key, con_id from v$sql_monitor w
here report_id = 0 and status != 'EXECUTING' and status != 'QUEUED' and px_qcsid i
s null and last_refresh_time > (select nvl(last_cycle_time, sysdate-(5/1440))
from v$sys_report_stats)
116 ZONOS_OSS_10220_SBGA UPDATE MDUS_UTIL_TIME_SERIES_REL SET LAST_INSERT_TS = :1, LAST_ACCOUNTING_TS = :2, NEW_INSERT_TS = : 2692946618
G 3, NEW_ACCOUNTING_TS = :4, REQUEST_UUID = :5 WHERE TIME_SERIES_ID = :6 AND ACTIVE_SINCE = :7
116 ZONOS_OSS_10220_SBGA UPDATE DEVICE_JOB SET DEVICE_JOB_STATE = :1 , 837926809
Heaviest SQL during last 10 min:
SQL> col SQL_OPNAME for a20
SQL> col SQL_TEXT for a50
SQL> select a.sql_id,a.sql_opname, count(*) cnt , 100*trunc(ratio_to_report(count(*)) over (),4) "%" , t.sql_text
from v$active_session_history a left join v$sqlarea t on a.sql_id = t.sql_id
where 1=1 and sample_time > sysdate-10/60/24 and a.sql_id is not null group by a.sql_id, a.sql_opname,t.sql_text
order by count(*) desc;
SQL_ID SQL_OPNAME CNT % SQL_TEXT
------------- -------------------- ---------- ---------- --------------------------------------------------
bpf6n23radbxv SELECT 262 19.98 SELECT * FROM ( SELECT DEVICE_JOB_
ID FROM DEVICE_JOB
WHERE DEVICE_JOB_START_TIMEST
AMP <= :1 AND DEVICE_JOB_STAT
E = 1 AND MOD(DEVICE_ID, 1) =
:2 ORDER BY PRIORITY, DEVICE_JOB_
START_TIMESTAMP ) WHERE ROWNUM <=
100
0n4r4gwu677wt DELETE 183 13.95 DELETE FROM MDUS_PROCESS_QUEUE WHERE MDUS_PROCESS_
ID = :1
89rrhqufbbkk8 UPDATE 63 4.8 UPDATE DEVICE_JOB SET
DEVICE_JOB_STATE = :1 ,
Show time period covered by the ASH:
SQL> select
extract(second from sysdate - max(sample_time)) seconds_since_most_recent ,
extract(minute from sysdate - min(sample_time)) minutes_since_least_recent,
extract(minute from max(sample_time) - min(sample_time)) minutes_covered
from v$active_session_history;
SECONDS_SINCE_MOST_RECENT MINUTES_SINCE_LEAST_RECENT MINUTES_COVERED
------------------------- -------------------------- ---------------
.968 5 5
Session ID of session with most wait time:
SQL> select A.SID, B.USERNAME, A.EVENT, A.WAIT_TIME
from V$SESSION_WAIT_HISTORY A, V$SESSION B
where A.SID = B.SID and B.USERNAME IS NOT NULL order by WAIT_TIME desc fetch first 10 rows only;
SID USERNAME EVENT WAIT_TIME
---------- -------------------- ---------------------------------------------------------------- ----------
1825 ZONOS_OSS_10220_SBGA SQL*Net message from client 17271088
659 ZONOS_OSS_10220_SBGA SQL*Net message from client 6659913
More Performance Views (if no Diagnostic Pack license to create AWR reports etc):
- V$WAITCLASSMETRIC_HISTORY => metric values of wait classes for all intervals in the last one hour
- V$SYSMETRIC_HISTORY => system metric values (CPU, Executes, Parses, Commits, Reads, ..) for all intervals in the last one hour
Historical performance issues
Customers might request Support/DBA to analyse performance issues already fixed, and affected sessions gone due to service restarts,
- AWR reports are based on historical performance data, so it can be used to analyse performance problems after the events are observed.
- DBA_HIST_ACTIVE_SESS_HISTORY
Example:
SQL> select SESSION_ID, USER_ID, SQL_ID , SQL_EXEC_START, COUNT(SQL_ID)*10 AS total_wait_time, SESSION_STATE, BLOCKING_SESSION_STATUS, BLOCKING_SESSION , PROGRAM, MACHINE, WAIT_CLASS
from dba_hist_active_sess_history
where BLOCKING_SESSION is not null and SQL_EXEC_START>=TO_DATE('08/AUG/2023','dd/mon/yyyy HH24:MI:SS') and SQL_EXEC_START<=TO_DATE('10/AUG/2023','dd/mon/yyyy HH24:MI:SS')
GROUP BY SESSION_ID, USER_ID, SQL_ID , SESSION_STATE, SQL_EXEC_START, BLOCKING_SESSION_STATUS, BLOCKING_SESSION , PROGRAM, MACHINE , WAIT_CLASS order by SQL_EXEC_START;
SESSION_ID USER_ID SQL_ID SQL_EXEC_START TOTAL_WAIT_TIME SESSION BLOCKING_SE BLOCKING_SESSION PROGRAM MACHINE WAIT_CLASS
---------- ---------- ------------- ------------------- --------------- ------- ----------- ---------------- ---------------------------------------- ------------------------------ --------------------
...
3172 116 5303rfpntfnpr 2023-08-09 10:25:27 10 WAITING VALID 2308 JDBC Thin Client lt-co1s Application
1835 116 2n07a8ymk77cg 2023-08-09 10:26:34 30 WAITING VALID 2663 JDBC Thin Client lt-co1p Other
641 116 4ydyadq4rnqgb 2023-08-09 10:26:46 20 WAITING VALID 643 zonos-module-uaa-service@lt-sb1p (TNS V1 lt-sb1p Cluster
-V3)
2807 116 4ydyadq4rnqgb 2023-08-09 10:26:46 20 WAITING VALID 643 zonos-module-uaa-service@lt-sb1p (TNS V1 lt-sb1p Cluster
-V3)
Index Rebuild
Rebuilding indexes is a resource intensive and blocking task. For some particular cases it might be worth to check if rebuilding indexes should be considered but make sure that expected performance gain is worth the cost and effort of rebuilding.
In general, this should be done during a scheduled maintenance window.
- High index fragmentation, e.g after deleting lots of rows:
- index has height greater than four (HEIGHT in INDEX_STATS)
- deleted leaf rows >20% (DEL_LF_ROW in INDEX_STATS)
- High index scan access plans: index fast-full scans and index range scans in AWR report
How to check if index needs rebuild:
Find indexes on table:
SQL> select owner, table_name, index_name, num_rows from dba_indexes where owner/table_name ...
"OWNER","TABLE_NAME","INDEX_NAME","NUM_ROWS"
"CUSTOMER_10121","DEVICE_ALARM_OCCURRENCE","PK_DEVICE_ALARM_OCCURRENCE",3243032
"CUSTOMER_10121","DEVICE_ALARM_OCCURRENCE","K_OCCURRED_AT",3243032
Analyse:
SQL> ANALYZE INDEX CUSTOMER_10121.K_OCCURRED_AT VALIDATE STRUCTURE;
Index analyzed.
SQL> SELECT name, height,lf_rows,lf_blks,del_lf_rows FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS
--------------------------- ---------- ---------- ---------- -----------
K_OCCURRED_AT 3 3448237 17523 0
Tablespace Growth
SELECT part.tsname tablespace_name,
Max(part.used_size) "Current Size (MB)", /* Current size of tablespace */
Round(Avg(inc_used_size), 2) "Growth Per Day(MB)" /* Growth of tablespace per day */
FROM
(SELECT sub.days,
sub.tsname,
used_size,
used_size - Lag (used_size, 1)
over (PARTITION BY sub.tsname ORDER BY sub.tsname, sub.days) inc_used_size /* getting delta increase using analytic function */
FROM
(SELECT TO_CHAR(hsp.begin_interval_time,'MM-DD-YYYY') days,
hs.tsname,
MAX((hu.tablespace_usedsize* dt.block_size )/(1024*1024)) used_size
from
dba_hist_tbspc_space_usage hu, /* historical tablespace usage statistics */
dba_hist_tablespace_stat hs , /* tablespace information from the control file */
dba_hist_snapshot hsp, /* information about the snapshots in the Workload Repository */
dba_tablespaces dt
where
hu.snap_id = hsp.snap_id
and hu.TABLESPACE_ID = hs.ts#
and hs.tsname = dt.tablespace_name
AND hsp.begin_interval_time > SYSDATE - 7 /* gathering info about last 7 days */
GROUP BY To_char(hsp.begin_interval_time, 'MM-DD-YYYY'),
hs.tsname
order by hs.tsname,days) sub) part
GROUP BY part.tsname
ORDER BY part.tsname;
DB Objects with highest number of block changes per day
SELECT trunc(begin_interval_time,'DD') Day,
dhso.object_name , sum(db_block_changes_delta) BLOCK_CHANGED
FROM dba_hist_seg_stat dhss,
dba_hist_seg_stat_obj dhso,
dba_hist_snapshot dhs
WHERE dhs.snap_id = dhss.snap_id
AND dhs.instance_number = dhss.instance_number
AND dhss.obj# = dhso.obj#
AND dhss.dataobj# = dhso.dataobj#
AND dhso.OWNER like '%10%'
GROUP BY trunc(begin_interval_time,'DD'),
dhso.object_name
HAVING sum(db_block_changes_delta) > 0
ORDER BY 3 desc;
DAY OBJECT_NAME BLOCK_CHANGED
------------------ ------------------------------ -------------
11-NOV-25 IDX_DEVICE_VIEW 30127472
10-NOV-25 IDX_DEVICE_VIEW 30020640
13-NOV-25 IDX_DEVICE_VIEW 29704752
11-NOV-25 pk_IDX_DEVICE_VIEW 29314288
14-NOV-25 IDX_DEVICE_VIEW 29164624
13-NOV-25 pk_IDX_DEVICE_VIEW 29104736
10-NOV-25 pk_IDX_DEVICE_VIEW 28567680
14-NOV-25 pk_IDX_DEVICE_VIEW 27970096
