Average wait times for all events
                                                 Last update (2012-06-24 11:36:41)
                                                                                                                   Date added (2008-02-13 19:50:24)

Summary
All the average times waited for the important wait events. The query results comes from 2 Oracle10g RAC database for E-Business Suite (one with ASM) and an oracle10g Data warehouse. The column AVG_WAIT_MSEC is in milliseconds
SELECT * FROM 
(SELECT event, total_waits, ROUND(100 * (total_waits / sum_waits),2) pct_waits, time_wait_sec,
       ROUND(100 * (time_wait_sec / GREATEST(sum_time_waited,1)),2) pct_time_waited, total_timeouts,
       ROUND(100 * (total_timeouts / GREATEST(sum_timeouts,1)),2) pct_timeouts, AVG_wait_Msec
FROM (SELECT event, total_waits, ROUND((time_waited / 100),2) time_wait_sec, total_timeouts, 
ROUND((average_wait),2)*10 AVG_wait_Msec
FROM sys.v_$system_event 
WHERE event NOT IN (SELECT NAME FROM v$event_name WHERE wait_class = 'Idle') 
AND event NOT LIKE 'DFS%' 
AND event NOT LIKE 'KXFX%'),
(SELECT SUM(total_waits) sum_waits, SUM(total_timeouts) sum_timeouts, 
SUM(ROUND((time_waited / 100),2)) sum_time_waited
 FROM sys.v_$system_event WHERE event NOT IN 
 (SELECT NAME FROM v$event_name WHERE wait_class = 'Idle') 
AND event NOT LIKE 'DFS%' 
AND event NOT LIKE 'KXFX%')
ORDER BY 2 DESC, 1 ASC) 
WHERE (pct_time_waited <> 0 OR total_timeouts <> 0 OR pct_timeouts <> 0 OR avg_wait_msec <> 0) 
AND pct_waits <> 0;

Tip: The query for Oracle10g and Oracle11g, filters only the non idle wait events! 
If you are using Oracle 9i, then the column  wait_class = 'Idle' does not exists.

RAC E-Business Suite HP-UX 2012-06-21
EVENT                                                            TOTAL_WAITS  PCT_WAITS TIME_WAIT_SEC PCT_TIME_WAITED TOTAL_TIMEOUTS PCT_TIMEOUTS AVG_W
---------------------------------------------------------------- ----------- ---------- ------------
db file sequential read                                           4267828500      16,89    13626233,6           25,58              0            0           3,2
SQL*Net message to client                                         4071044244      16,11       4060,29             ,01              0            0             0
gc cr multi block request                                         3332722924      13,19    1706078,58             3,2            956            0            ,5
latch: cache buffers chains                                       3186116661      12,61    4186676,88            7,86              0            0           1,3
gc cr grant 2-way                                                 1721914241       6,82    1478699,45            2,78            127            0            ,9
db file scattered read                                             961204462        3,8    4583436,26             8,6              0            0           4,8
gc current block 2-way                                             943721903       3,74    1220482,79            2,29           2436            0           1,3
CGS wait for IPC msg                                               894388711       3,54       6764,65             ,01      894388702        61,27             0
SQL*Net more data to client                                        635321390       2,51     877008,01            1,65              0            0           1,4
gc buffer busy                                                     625598685       2,48    3766040,16            7,07         837269          ,06             6
gcs log flush sync                                                 314388126       1,24     828428,95            1,55      192343520        13,18           2,6
control file sequential read                                       308488748       1,22     130652,95             ,25           2009            0            ,4
gc cr block 2-way                                                  303520283        1,2     341056,23             ,64             71            0           1,1
db file parallel write                                             295189685       1,17     668034,04            1,25              0            0           2,3
SQL*Net break/reset to client                                      292416223       1,16     363738,77             ,68              0            0           1,2
gc current grant 2-way                                             246757628        ,98      326772,3             ,61             90            0           1,3
direct path read                                                   220774672        ,87     960604,96             1,8              0            0           4,4
PX Deq: reap credit                                                215292266        ,85       3377,66             ,01      209319821        14,34             0
log file parallel write                                            209544456        ,83    1277437,58             2,4              0            0           6,1
ksxr poll remote instances                                         186455862        ,74       2187,34               0       85485541         5,86           
read by other session                                              106866735        ,42     185007,75             ,35            453            0           1,7
PX Deq Credit: send blkd                                           105625340        ,42     9180243,5           17,23        3715408          ,25          86,
buffer busy waits                                                   99021713        ,39     241603,34             ,45           5672            0           2,4
db file parallel read                                               96391984        ,38     876089,01            1,64              0            0           9,1
row cache lock                                                      77708301        ,31     113961,02             ,21            186            0           1,5
library cache lock                                                  68036204        ,27     148798,79             ,28           6746            0           2,2
gc cr block busy                                                    63493978        ,25     800527,07             1,5          22473            0          12,6
gc cr disk read                                                     46506283        ,18      40094,64             ,08              2            0            ,9
SQL*Net more data from client                                       46121912        ,18     572525,91            1,07              0            0          12,4
log file sync                                                       37231283        ,15     732099,22            1,37          22336            0          19,7
gc current grant busy                                               36749102        ,15      94801,52             ,18            523            0           2,6
ksdxexeotherwait                                                    28801240        ,11         76,41               0       28801240         1,97             0
latch: library cache                                                26818390        ,11     116639,51             ,22             71            0           4,3
gc current block busy                                               18492951        ,07     525916,87             ,99           8479            0          28,4
latch: KCL gc element parent latch                                  16371860        ,06       9547,99             ,02              5            0            ,6
enq: TX - row lock contention                                       16215657        ,06    2137686,93            4,01        4244646          ,29        
undo segment extension                                              15558952        ,06         79,62               0       15555226         1,07             0
direct path read temp                                               15263162        ,06      43563,62             ,08              0            0           2,9
gc current multi block request                                      12439469        ,05      24472,16             ,05             42            0             2
latch: object queue header operation                                12040293        ,05       8137,01             ,02             95            0            ,7
enq: PS - contention                                                11637780        ,05      15915,67             ,03            106            0           1,4
direct path write                                                   11548809        ,05       7023,31             ,01              0            0            ,6
reliable message                                                    10072058        ,04      12345,23             ,02           1567            0           1,2
direct path write temp                                               9886069        ,04      39695,78             ,07              0            0             4
PX qref latch                                                        9671556        ,04         57,06               0        8481421          ,58             0
Log archive I/O                                                      8601529        ,03      18073,64             ,03              0            0           2,1
gc cr grant congested                                                8427938        ,03      75746,85             ,14             15            0             9
log file sequential read                                             8200745        ,03     106733,18              ,2              0            0            13
PX Deq: Signal ACK                                                   8119078        ,03       3868,29             ,01        2458992          ,17            ,5
IPC send completion sync                                             7415639        ,03       2113,31               0        7384319          ,51            ,
enq: HW - contention                                                 7032259        ,03      18244,83             ,03           1948            0           2,6
control file parallel write                                          6925063        ,03      46103,18             ,09              0            0           6,7
buffer deadlock                                                      6405161        ,03       3567,66             ,01        6282894          ,43            ,6
enq: TT - contention                                                 5668018        ,02       7810,92             ,01             10            0           1,4
LGWR wait for redo copy                                              5253648        ,02        2101,2               0          16845            0            ,4
enq: TM - contention                                                 5077468        ,02      32157,27             ,06          52387            0           6,3
latch free                                                           4428558        ,02       7411,04             ,01             97            0           1,7
enq: TX - index contention                                           4375597        ,02      69430,43             ,13           7767            0          15,9
TCP Socket (KGAS)                                                    4071950        ,02     171338,16             ,32              0            0          42,1
gc current block congested                                           3490433        ,01      33175,76             ,06             49            0           9,5
SQL*Net more data from dblink                                        3158567        ,01      42888,11             ,08              0            0          13,6
enq: TQ - TM contention                                              2864560        ,01       6185,55             ,01             21            0           2,2
library cache pin                                                    2705569        ,01      35588,66             ,07           1885            0          13,2
rdbms ipc reply                                                      2627515        ,01       3498,75             ,01              5            0           1,3
latch: cache buffers lru chain                                       2497317        ,01      12115,03             ,02              0            0           4,9
enq: DV - contention                                                 1871125        ,01       5469,31             ,01              1            0           2,9
enq: UL - contention                                                 1657378        ,01      56986,19             ,11         107454          ,01          34,4
enq: FB - contention                                                 1508130        ,01      12165,48             ,02             36            0           8,1
latch: undo global data                                              1384720        ,01       4577,82             ,01              0            0           3,3
gc current grant congested                                           1318830        ,01      13678,49             ,03             18            0          10,4
enq: SQ - contention                                                 1277094        ,01       14719,6             ,03             21            0          11,5

71 rows selected.
DataWareHouse  2012-06-21 

EVENT                                                            TOTAL_WAITS  PCT_WAITS TIME_WAIT_SEC PCT_TIME_WAITED TOTAL_TIMEOUTS PCT_TIMEOUTS AVG_W
---------------------------------------------------------------- ----------- ---------- ------------
db file sequential read                                           1040366792      23,53    3544920,64            8,89              7            0           3,4
SQL*Net more data to client                                        890042047      20,13      26687,52             ,07              0            0             0
SQL*Net more data from dblink                                      372445311       8,42    1606830,31            4,03              0            0           4,3
db file scattered read                                             158875866       3,59    1245347,95            3,12              0            0           7,8
latch: cache buffers chains                                        139278295       3,15     254772,12             ,64              0            0           1,8
Backup: sbtwrite2                                                  122188170       2,76     557999,98             1,4              0            0           4,6
read by other session                                              104580696       2,37     555103,86            1,39           5945          ,01           5,3
PX Deq Credit: send blkd                                           100945021       2,28    26098831,6           65,44        7357502        12,61         258,
RMAN backup & recovery I/O                                          70803593        1,6     610861,68            1,53              0            0           8,6
buffer busy waits                                                   66037690       1,49     562447,16            1,41         102310          ,18           8,5
control file sequential read                                        50929824       1,15      11869,26             ,03              0            0            ,2
free buffer waits                                                   38268474        ,87     721935,04            1,81       35699117        61,17          18,9
SQL*Net message from dblink                                         37112251        ,84     165356,64             ,41              0            0           4,5
io done                                                             35718365        ,81     430758,88            1,08        6752804        11,57          12,1
log file parallel write                                             14624505        ,33      204928,3             ,51              0            0            14
latch: shared pool                                                  12922659        ,29      50603,95             ,13              5            0           3,9
Datapump dump file I/O                                              10965031        ,25     114282,62             ,29              0            0          10,4
PX Deq: Signal ACK                                                  10775634        ,24       4991,43             ,01         934600          1,6            ,5
db file parallel read                                               10033835        ,23      75890,73             ,19              0            0           7,6
log file sync                                                        6590556        ,15     185847,84             ,47         977160         1,67          28,2
log file sequential read                                             6211558        ,14      44984,67             ,11              0            0           7,2
Log archive I/O                                                      6177320        ,14      72309,14             ,18              0            0          11,7
LGWR wait for redo copy                                              4981856        ,11       2038,86             ,01            741            0            ,4
latch: row cache objects                                             4340159         ,1      19679,53             ,05              0            0           4,5
row cache lock                                                       4331228         ,1      32256,45             ,08           4555          ,01           7,4
latch: redo allocation                                               4182851        ,09      45677,92             ,11              0            0          10,9
latch: session allocation                                            4141977        ,09        8735,4             ,02              1            0           2,1
latch: library cache                                                 3318666        ,08      28650,42             ,07              0            0           8,6
cursor: pin S wait on X                                              3295507        ,07      62512,63             ,16        3284934         5,63            1
latch free                                                           2308503        ,05      24599,76             ,06            177            0          10,7
enq: HW - contention                                                 2223577        ,05      33520,26             ,08           3609          ,01          15,1
log buffer space                                                     1959426        ,04     193123,85             ,48          23524          ,04          98,6
control file parallel write                                          1795521        ,04       5043,72             ,01              0            0           2,8
db file single write                                                 1758624        ,04       2033,13             ,01              0            0           1,2
rdbms ipc reply                                                      1363637        ,03     120144,98              ,3          50107          ,09          88,1
enq: PS - contention                                                 1206125        ,03       1951,81               0            567            0           1,6
PX qref latch                                                        1150781        ,03         40,64               0        1083247         1,86             0
enq: TX - index contention                                            938033        ,02      30359,38             ,08           2349            0          32,4
buffer deadlock                                                       900537        ,02        661,41               0         875231          1,5            ,7
local write wait                                                      817524        ,02      420346,1            1,05         385976          ,66         514,2
latch: parallel query alloc buffer                                    809319        ,02      43429,14             ,11              0            0          53,7
enq: TX - contention                                                  787507        ,02       9779,23             ,02           2574            0          12,4
cursor: mutex X                                                       591638        ,01        127,02               0              0            0            ,2
enq: TX - allocate ITL entry                                          471389        ,01     125232,28             ,31          45848          ,08         
change tracking file synchronous read                                 467115        ,01        605,09               0              0            0           1,3
PX Deq: Table Q qref                                                  447577        ,01        626,28               0              3            0           1,4
SQL*Net break/reset to client                                         440015        ,01       1721,98               0              0            0           3,9
enq: CI - contention                                                  424308        ,01     389838,35             ,98         110662          ,19         918,8
enq: TX - row lock contention                                         393316        ,01      21481,09             ,05           1514            0          54,6
latch: library cache lock                                             356962        ,01       1173,95               0              0            0           3,3
resmgr:cpu quantum                                                    345456        ,01      15002,12             ,04              0            0          43,4
log file switch (private strand flush incomplete)                     339358        ,01     104097,46             ,26          
Data file init write                                                  339098        ,01       3447,59             ,01              0            0          10,2
enq: RO - fast object reuse                                           235177        ,01     262101,03             ,66          74357          ,13        11
SQL*Net more data from client                                         229400        ,01        150,75               0              0            0            ,7

55 rows selected.
Reviews
Categories
Filters
Search