System events and wait times
-- -- Author: Ahbaid Gaffoor -- Date: Tuesday 25th February 2003 -- File: waitdetails.sql -- -- Purpose: Display system event wait times -- -- License: You may reuse this software at will provided you quote and retain this header -- -- Disclaimer: No warranty is provided with this software, no liability or guarantees -- are given in any form -- -- -- Some Important events: -- ================= -- async disk IO -- control file parallel write -- control file sequential read -- db file parallel write -- db file scattered read -- db file sequential read -- direct path read -- direct path write -- log file parallel write -- log file sync -- set lines 132 set pages 1000 clear breaks clear columns column c1 heading 'ID' format 999 column c2 heading 'Process' format A10 column c3 heading 'Event' format A30 column c4 heading 'Total Waits' format 999,999 column c5 heading 'Wait Time(s)' format 999,999 column c6 heading 'Timeouts' format 999,999 column c7 heading 'Avg. Wait(s)' format 999 column c8 heading 'Max Wait(s)' format 999 select b.sid c1, decode(b.username,NULL,c.name,b.username) c2, event c3, a.total_waits c4, round((a.time_waited / 100),2) c5, a.total_timeouts c6, round((average_wait / 100),2) c7, round((a.max_wait / 100),2) c8 from sys.v_$session_event a, sys.v_$session b, sys.v_$bgprocess c where event not like 'DFS%' and event not like 'KXFX%' and a.sid = b.sid and b.paddr = c.paddr (+) and event not in ( 'lock element cleanup', 'pmon timer', 'rdbms ipc message', 'smon timer', 'SQL*Net message from client', 'SQL*Net break/reset to client', 'SQL*Net message to client', 'SQL*Net more data to client', 'dispatcher timer', 'Null event', 'io done', 'parallel query dequeue wait', 'parallel query idle wait - Slaves', 'pipe get', 'PL/SQL lock timer', 'slave wait', 'virtual circuit status', 'WMON goes to sleep') order by 4 desc ;
TESTDB: SYSTEM> @waitdetails
ID Process Event Total Waits Wait Time(s) Timeouts Avg. Wait(s) Max Wait(s)
------ ---------- ------------------------------ ----------- ------------ -------- ------------ -----------
3 LGWR control file parallel write 30,067 295 0 0 0
2 DBW0 log file parallel write 13,303 0 13,303 0 0
3 LGWR control file sequential read 12,042 0 0 0 0
9 ARC0 control file sequential read 7,520 0 0 0 0
1 PMON db file parallel write 4,256 0 2,125 0 0
7 CJQ0 wakeup time manager 2,912 87,054 2,912 30 30
2 DBW0 LGWR wait for redo copy 640 0 0 0 0
7 CJQ0 db file sequential read 108 1 0 0 0
4 CKPT db file scattered read 75 1 0 0 0
1 PMON control file sequential read 68 0 0 0 0
4 CKPT db file sequential read 62 0 0 0 0
1 PMON direct path read 46 0 0 0 0
2 DBW0 control file sequential read 30 0 0 0 0
2 DBW0 direct path read 23 0 0 0 0
10 ARC1 db file sequential read 21 0 0 0 0
2 DBW0 direct path write 20 0 0 0 0
4 CKPT rdbms ipc reply 19 0 0 0 0
2 DBW0 control file parallel write 16 0 0 0 0
4 CKPT buffer busy waits 11 0 0 0 0
7 CJQ0 db file scattered read 9 0 0 0 0
2 DBW0 log file sequential read 6 0 0 0 0
2 DBW0 log file single write 6 0 0 0 0
8 QMN0 control file sequential read 4 0 0 0 0
6 RECO db file sequential read 2 0 0 0 0
4 CKPT library cache load lock 2 0 0 0 0
10 ARC1 log file sync 1 0 0 0 0
5 SMON db file sequential read 1 0 0 0 0
27 rows selected.
TESTDB: SYSTEM>