| |
 |
|
Oracle Tips by Burleson |
Oracle10g Wait Events
With the release of Oracle10g came a standard Automatic Workload
Repository (AWR) report which contains a Wait Events section that
displays top wait events. The following query can also be used to
allow retrieval of top wait events for a particular AWR snapshot
interval:
* wt_events_int_10g.sql
-- *************************************************
-- Copyright © 2005 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties. Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact info@rampant.cc
-- *************************************************
select event
, waits "Waits"
, time "Wait Time (s)"
, pct*100 "Percent of Total"
, waitclass "Wait Class"
from (select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/
(select sum(e1.time_waited_micro - nvl(b1.time_waited_micro,0)) from
dba_hist_system_event b1 , dba_hist_system_event e1
where b1.snap_id(+) = b.snap_id
and e1.snap_id = e.snap_id
and b1.dbid(+) = b.dbid
and e1.dbid = e.dbid
and b1.instance_number(+) = b.instance_number
and e1.instance_number = e.instance_number
and b1.event_id(+) = e1.event_id
and e1.total_waits > nvl(b1.total_waits,0)
and e1.wait_class <> 'Idle'
) pct
, e.wait_class waitclass
from
dba_hist_system_event b ,
dba_hist_system_event e
where b.snap_id(+) = &pBgnSnap
and e.snap_id = &pEndSnap
and b.dbid(+) = &pDbId
and e.dbid = &pDbId
and b.instance_number(+) = &pInstNum
and e.instance_number = &pInstNum
and b.event_id(+) = e.event_id
and e.total_waits > nvl(b.total_waits,0)
and e.wait_class <> 'Idle'
order by time desc, waits desc
)
The sample output of this query looks like the following:
SQL> @ wt_events_int_10g.sql
EVENT Waits Wait Time (s) Percent of Total Wait Class
------------------------------ ------- ------------- --------
control file parallel write 11719 119.13 34,1611762 System I/O
class slave wait 20 102.46 29,3801623 Other
Queue Monitor Task Wait 74 66.74 19,1371008 Other
log file sync 733 20.60 5,90795938 Commit
db file sequential read 1403 14.27 4,09060416 User I/O
log buffer space 178 10.17 2,91745801 Configuration
process startup 114 7.65 2,19243344 Other
db file scattered read 311 2.14 ,612767501 User I/O
control file sequential read 7906 1.33 ,380047642 System I/O
latch free 254 1.13 ,324271668 Other
log file switch completion 20 1.11 ,319292495 Configuration
As shown above, the output of the wt_events_int_10g.sql script
displays the wait events ordered by wait times in seconds.
The above book excerpt is from:
Oracle
Tuning Power Scripts
With 100+ High Performance
SQL Scripts
Oracle In-Focus Series
ISBN
0-9744486-7-2
Mike Ault, Donald K.
Burleson. Harry Conway
http://www.rampant-books.com/book_2005_1_power_tuning.htm |
