Wait Class
- Application : eg - row level locking
- Administration : eg Index rebuilding
- Commit : eg - redo log confirmation after commit
- Concurrency - eg - buffer cache latch |lock contention
- Configuration - Undersize cache
- User I/O- block read from disk for foreground process
- Network Communications - transferring data| latency
- Idle- application foreground process connected but idle
Displaying Events statistics
Common wait events
- buffer busy waits : wait until buffer available
- free buffer waits : wait until buffer gets free
- db sequential reads: sequential reads from database
- db scattered reads: read multiple blocks
- Enqueue : serial access from database
- library cache: locked object in library cache
- log buffer space : wait free space in log buffer cache
- log file : wait for log file to finish
Query to check instance level statisticsSQL>col NAME format A50
col CLASS format A10
SELECT NAME,
DECODE(TO_CHAR(CLASS),
'1','User',
'2','Redo',
'4','Enqueue',
'8','Cache',
SQL> SQL> 2 3 '16','OS', 4
'32','RAC',
'33','RAC-User',
'40','RAC-Cache',
'64','SQL',
5 6 7 8 9 10 11 12 '72','SQL-Cache',
'128','Debug',
13 14 '192','Debug-SQL',
15 TO_CHAR(CLASS)
) CLASS, VALUE
16 FROM V$SYSSTAT
17 18 ORDER BY CLASS,NAME;
NAME CLASS VALUE
-------------------------------------------------- ---------- ----------
redo non-durable records skipped 34 0
CR blocks created Cache 30965
DBWR checkpoint buffers written Cache 1135880
DBWR checkpoints Cache 1538
DBWR lru scans Cache 0
DBWR object drop buffers written Cache 2797
DBWR parallel query checkpoint buffers written Cache 11128
DBWR revisited being-written buffer Cache 0
DBWR tablespace checkpoint buffers written Cache 404
DBWR thread checkpoint buffers written Cache 0
DBWR transaction table writes Cache 22774
query for stats of database Full table and index scans SQL> SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE (NAME LIKE 'table%' or NAME LIKE 'index%') AND VALUE<>0
ORDER BY NAME; 2 3 4
NAME VALUE
-------------------------------------------------- ----------
index crx upgrade (positioned) 116414
index fast full scans (full) 37029
index fetch by key 118133973
index scans kdiixs1 43995778
table fetch by rowid 416240355
table fetch continued row 108874
table scan blocks gotten 79206961
table scan disk non-IMC rows gotten 3957922038
table scan rows gotten 4863040663
table scans (direct read) 11789
table scans (long tables) 13158
NAME VALUE
-------------------------------------------------- ----------
table scans (rowid ranges) 11789
table scans (short tables) 2327683
13 rows selected.
Top sessions of specific statistics:SQL> SELECT S.SID, H.USERNAME, T.NAME, S.VALUE
FROM V$SESSTAT S, V$STATNAME T, V$SESSION H
WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID
AND T.NAME = 'parse time cpu'
AND H.USERNAME IS NOT NULL
ORDER BY S.VALUE DESC; 2 3 4 5 6
SID USERNAME NAME VALUE
---------- ---------- -------------------- ----------
1077 TCPRODMANA parse time cpu 496
GER
1082 TCPRODMANA parse time cpu 378
GER
777 TCPRODMANA parse time cpu 60
GER
1076 TCPRODMANA parse time cpu 60
GER
SID USERNAME NAME VALUE
---------- ---------- -------------------- ----------
626 TCPRODMANA parse time cpu 12
GER
3 INFODBA parse time cpu 8
164 INFODBA parse time cpu 6
774 SYS parse time cpu 4
10 INFODBA parse time cpu 3
1084 INFODBA parse time cpu 2
776 INFODBA parse time cpu 2
1083 INFODBA parse time cpu 1
Statement text in the output:SQL> col SQL_TEXT format a25
SQL> SELECT S.SID, H.USERNAME, T.NAME, S.VALUE, SUBSTR(Q.SQL_TEXT,1,25) SQL_TEXT
FROM V$SESSTAT S, V$STATNAME T, V$SESSION H, V$SQL Q
WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID AND H.SQL_ID=Q.SQL_ID(+)
AND T.NAME = 'parse time cpu'
AND H.USERNAME IS NOT NULL
ORDER BY S.VALUE DESC; 2 3 4 5 6
SID USERNAME NAME VALUE SQL_TEXT
---------- ---------- -------------------- ---------- -------------------------
1077 TCPRODMANA parse time cpu 496
GER
1082 TCPRODMANA parse time cpu 378
GER
1076 TCPRODMANA parse time cpu 60
GER
777 TCPRODMANA parse time cpu 60
GER
SID USERNAME NAME VALUE SQL_TEXT
---------- ---------- -------------------- ---------- -------------------------
626 TCPRODMANA parse time cpu 12
GER
3 INFODBA parse time cpu 8
164 INFODBA parse time cpu 6
774 SYS parse time cpu 5 SELECT S.SID, H.USERNAME,
774 SYS parse time cpu 5 SELECT S.SID, H.USERNAME,
10 INFODBA parse time cpu 3
1084 INFODBA parse time cpu 2
776 INFODBA parse time cpu 2
SQL> SELECT S.SID, H.USERNAME, T.NAME, S.VALUE
FROM V$MYSTAT S, V$STATNAME T, V$SESSION H
WHERE S.STATISTIC# = T.STATISTIC# AND S.SID = H.SID
AND T.NAME = 'parse time cpu'
AND H.USERNAME IS NOT NULL
ORDER BY S.VALUE DESC; 2 3 4 5 6
SID USERNAME NAME VALUE
---------- ---------- -------------------- ----------
774 SYS parse time cpu 5
VIEWING WAIT EVENTS
SQL> col EVENT format a40
col WAIT_CLASS format a11
SELECT EVENT, AVERAGE_WAIT,
TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS
FROM V$SYSTEM_EVENT
WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'
ORDER BY TIME_WAITED;SQL> SQL> 2 3 4 5
Display all the non-idle wait event statistics at the instance level.
SQL> col EVENT format a40
col WAIT_CLASS format a11
SELECT EVENT, AVERAGE_WAIT,
TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS, WAIT_CLASS
FROM V$SYSTEM_EVENT
WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'
ORDER BY TIME_WAITED;SQL> SQL> 2 3 4 5
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
AQ Background Master: slave start .11 0 Other
get branch/thread/sequence enqueue .01 0 Other
enq: SQ - contention .28 0 Configurati
on
latch: gc element 0 0 Other
latch: cache buffers chains 0 0 Concurrency
latch: shared pool 0 0 Concurrency
latch: active service list 0 0 Other
enq: CT - state .02 0 Other
enq: BR - file shrink .04 0 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
Sync ASM discovery .76 0 Other
ges LMON to get to FTDONE .34 0 Other
enq: FU - contention .05 0 Other
enq: AS - service activation .51 0 Other
enq: FL - Flashback database log .03 0 Other
gc cr block lost .03 0 Cluster
enq: TT - contention .03 0 Other
enq: DW - contention .04 0 Other
enq: TX - allocate ITL entry .61 0 Configurati
on
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
IMR mount phase II completion 3.01 0 Other
CSS group registration .15 0 Other
enq: TX - contention .21 0 Other
ges global resource directory to be froz 2 0 Other
en
latch: ges resource hash list 0 0 Other
db file single write .05 0 User I/O
ASM: MARK subscribe to msg channel 4.23 0 Other
CGS skgxn join retry 5.25 0 Other
enq: XL - fault extent map .77 0 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
LGWR wait for redo copy 0 0 Other
ges DFS hang analysis phase 2 acks .81 0 Other
enq: WT - contention .03 0 Other
GCR CSS group update 0 0 Other
ADR file lock 0 0 Other
Parameter File I/O .09 0 User I/O
cursor: pin S .11 0 Concurrency
GCR CSS group query 0 0 Other
recovery area: computing dropped files .96 0 Other
gc current split .11 0 Cluster
ges generic event 2.04 0 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
log file single write .03 0 System I/O
asynch descriptor resize 0 0 Other
enq: TK - Auto Task Serialization .03 0 Other
CSS group membership query .05 0 Other
recovery read .15 0 System I/O
PX Deq: Table Q Get Keys .09 0 Other
enq: MW - contention .03 0 Other
enq: DR - contention .04 0 Other
latch: call allocation .01 0 Other
log file switch completion 1.21 0 Configurati
on
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
enq: AF - task serialization .03 0 Other
enq: US - contention .03 0 Other
enq: JS - queue lock .25 0 Other
recovery area: computing backed up files 2.5 0 Other
enq: TQ - DDL contention 5.66 0 Other
enq: RS - prevent file delete .03 0 Other
enq: TO - contention .03 0 Other
checkpoint completed .74 0 Configurati
on
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
GPnP Get Item .15 0 Other
enq: SM - contention .03 0 Other
enq: TD - KTF dump entries .03 0 Other
switch logfile command 3.08 0 Administrat
ive
GPnP Termination .16 0 Other
kfk: async disk IO .13 0 System I/O
ADR block file write 0 0 Other
parallel recovery coord wait for reply .32 0 Other
enq: HW - contention .05 1 Configurati
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
on
Log archive I/O .13 1 System I/O
library cache load lock .58 1 Concurrency
enq: FB - contention .07 1 Other
kksfbc child completion 4.24 1 Other
DIAG lock acquisition 5.14 1 Other
PX Deq: Signal ACK EXT 0 1 Other
enq: TA - contention .03 1 Other
enq: ZH - compression analysis .03 1 Other
rdbms ipc reply .48 1 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
enq: TX - index contention .79 1 Concurrency
db file async I/O submit 0 1 System I/O
enq: TS - contention .03 1 Other
local write wait .7 1 User I/O
library cache: mutex X .05 1 Concurrency
CDB: Per Instance Query for PDB Info 101.76 1 Other
cursor: pin S wait on X .59 1 Concurrency
LGWR worker group ordering .1 1 Other
CSS operation: query .01 1 Other
enq: CR - block range reuse ckpt .22 2 Other
enq: WL - contention 3.19 2 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
resmgr:internal state change 10.24 2 Concurrency
recovery area: computing obsolete files 20.98 2 Other
gcs resource directory to be unfrozen 8.74 2 Other
control file single write .1 2 System I/O
SQL*Net more data from client 0 2 Network
enq: JS - job run lock - synchronize .04 2 Other
enq: TM - contention .06 2 Application
enq: RO - fast object reuse .06 2 Application
ges cgs registration 20.22 3 Other
row cache cleanup 0 3 Other
buffer busy waits .21 3 Concurrency
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
GPnP Initialization 1.26 3 Other
enq: PV - syncstart 6.43 3 Other
ges enter server mode 38.61 3 Other
enq: TX - row lock contention 2.69 4 Application
CSS operation: action .04 4 Other
undo segment extension .76 4 Configurati
on
external table read 408.28 4 User I/O
enq: CF - contention .47 4 Other
control file heartbeat 409 4 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
enq: TC - contention 8.08 4 Other
SQL*Net break/reset to client 0 4 Application
direct path write temp .35 5 User I/O
row cache process 0 5 Other
ges lms sync during dynamic remastering 2.49 5 Other
and reconfig
library cache pin .02 5 Concurrency
ksxr poll remote instances 0 6 Other
flashback log file read .17 6 System I/O
enq: PR - contention 2.48 6 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
ASM background starting .67 6 Other
gc current multi block request .04 7 Cluster
LGWR all worker groups 1.98 7 Other
CSS initialization .13 7 Other
ges inquiry response .03 7 Other
gc buffer busy release .76 7 Cluster
enq: WF - contention .25 8 Other
CSS operation: data update .04 8 Other
CRS call completion 3.7 9 Other
JS kgl get object wait 10.24 10 Administrat
ive
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
CSS operation: data query .04 10 Other
latch free .08 10 Other
enq: KO - fast object checkpoint .03 11 Application
libcache interrupt action by LCK 0 11 Concurrency
ADR block file read 0 12 Other
DFS lock handle .02 13 Other
gc cr block busy .27 14 Cluster
gc cr grant 2-way .02 15 Cluster
gc buffer busy acquire .16 15 Cluster
library cache lock .05 15 Concurrency
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
KSV master wait 0 23 Other
read by other session .2 27 User I/O
gc current block busy .5 28 Cluster
gcs log flush sync .32 28 Other
log file sequential read .38 29 System I/O
row cache lock .01 29 Concurrency
gc current block 2-way .03 33 Cluster
gc current grant busy .05 34 Cluster
LGWR any worker group 4.71 35 Other
os thread creation .09 36 Other
gc current grant 2-way .03 40 Cluster
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
PX Deq: Slave Session Stats .02 42 Other
gc cr block 2-way .03 44 Cluster
ASM file metadata operation .01 45 Other
CGS wait for IPC msg 0 49 Other
db file parallel read 1.72 49 User I/O
SQL*Net more data to client 0 51 Network
SQL*Net message to client 0 56 Network
enq: PS - contention .03 61 Other
PX Deq: Slave Join Frag .04 65 Other
Disk file operations I/O .03 89 User I/O
direct path read .35 91 User I/O
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
IPC send completion sync .02 112 Other
enq: IV - contention .04 118 Other
target log write size .11 121 Other
name-service call wait 7.67 169 Other
RMAN backup & recovery I/O .26 182 System I/O
PX Deq: reap credit 0 195 Other
reliable message .58 211 Other
log switch/archive 756.42 219 Other
control file parallel write .07 233 System I/O
gc cr multi block request .04 332 Cluster
oracle thread bootstrap 1.09 461 Other
EVENT AVERAGE_WAIT TIME_SECONDS WAIT_CLASS
---------------------------------------- ------------ ------------ -----------
direct path write .83 694 User I/O
db file parallel write .04 814 System I/O
gc cr disk read .03 1,055 Cluster
control file sequential read .05 1,378 System I/O
log file sync .08 1,443 Commit
flashback log file sync .42 1,455 User I/O
log file parallel write .08 1,602 System I/O
flashback log file write .4 1,700 System I/O
db file scattered read .2 3,245 User I/O
db file sequential read .04 3,521 User I/O
177 rows selected.
Total wait time by wait event class.
SQL> col WAIT_CLASS format a25
col TIME_SECONDS format a25
SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS
FROM V$SYSTEM_WAIT_CLASS
WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'
ORDER BY TIME_WAITED;SQL> SQL> 2 3 4
WAIT_CLASS TIME_SECONDS
------------------------- -------------------------
Configuration 5
Administrative 10
Application 24
Concurrency 69
Network 110
Commit 1,443
Cluster 1,625
Other 2,167
System I/O 5,949
User I/O 9,181
10 rows selected.
Display the percentage for each wait class
SQL> col PCT format a5
SELECT WAIT_CLASS, TO_CHAR(ROUND(TIME_WAITED/100),'999,999,999') TIME_SECONDS,
'%' || ROUND(RATIO_TO_REPORT(TIME_WAITED) over ()*100) PCT
FROM V$SYSTEM_WAIT_CLASS
WHERE TIME_WAITED>0 AND WAIT_CLASS<>'Idle'
ORDER BY TIME_SECONDSQL> S; 2 3 4 5
WAIT_CLASS TIME_SECONDS PCT
------------------------- ------------------------- -----
Configuration 5 %0
Administrative 10 %0
Application 24 %0
Concurrency 69 %0
Network 110 %1
Commit 1,445 %7
Cluster 1,628 %8
Other 2,169 %11
System I/O 5,952 %29
User I/O 9,187 %45
10 rows selected.
Eg. Display the current sessions with the total wait time of the wait event 'log file sync' in each session
SQL> set linesize 180
col EVENT format a25
SELECT E.SID, S.USERNAME, E.EVENT,
TO_CHAR(ROUND(E.TIME_WAITED/100),'999,999,999') TIME_SECONDS,
E.WAIT_CLASS
FROM V$SESSION_EVENT E, V$SESSION S
WHERE E.SID=S.SID AND ROUND(E.TIME_WAITED/100)>0
SQL> SQL> AND S.USERNAME='SOE' AND E.EVENT='log file sync'
ORDER BY TIME_WAITED; 2 3 4 5 6 7
no rows selected
Current sessions which are currently waiting for the event 'log file sync'
SQL> col USERNAME format a4
col WAIT_CLASS format a10
SELECT SID, USERNAME, EVENT, WAIT_TIME, WAIT_CLASS
FROM V$SESSION
WHERE USERNAME='SOE'
AND EVENT='log file sync'
ORDER BY WAIT_TIME;SQL> SQL> 2 3 4 5
no rows selected
SQL>
SQL> col USERNAME format a4
col WAIT_CLASS format a10
SELECT SID, USERNAME, EVENT, WAIT_TIME, WAIT_CLASS
FROM V$SESSION
WHERE USERNAME='INFODBA'
ORDER BY WAIT_TIME;SQL> SQL> 2 3 4
SID USER EVENT WAIT_TIME WAIT_CLASS
---------- ---- ------------------------- ---------- ----------
3 INFO SQL*Net message from clie 0 Idle
DBA nt
1084 INFO SQL*Net message from clie 0 Idle
DBA nt
7 INFO SQL*Net message from clie 0 Idle
DBA nt
10 INFO SQL*Net message from clie 0 Idle
DBA nt
SID USER EVENT WAIT_TIME WAIT_CLASS
---------- ---- ------------------------- ---------- ----------
13 INFO SQL*Net message from clie 0 Idle
DBA nt
160 INFO SQL*Net message from clie 0 Idle
DBA nt
162 INFO SQL*Net message from clie 0 Idle
DBA nt
164 INFO SQL*Net message from clie 0 Idle
</div>