dba_hist_system_event
The
dba_hist_system_event view displays information about the
count of total waits and time waited in microseconds.
SQL> desc DBA_HIST_SYSTEM_EVENT
Name Null? Type
----------------------------------------- -------- ----------------
SNAP_ID NUMBER
DBID NUMBER
INSTANCE_NUMBER NUMBER
EVENT_ID NUMBER
EVENT_NAME VARCHAR2(64)
WAIT_CLASS_ID NUMBER
WAIT_CLASS VARCHAR2(64)
TOTAL_WAITS NUMBER
TOTAL_TIMEOUTS NUMBER
TIME_WAITED_MICRO NUMBER
This view stores snapshots of the
v$system_event system dynamic view. The following
sys_event_int_10g.sql query can be used to retrieve
wait events data for a particular snapshot interval:
select
event "Event Name",
waits "Waits",
timeouts "Timeouts",
time "Wait Time (s)",
avgwait "Avg Wait (ms)",
waitclass "Wait Class"
from
(select e.event_name event
, e.total_waits - nvl(b.total_waits,0) waits
, e.total_timeouts - nvl(b.total_timeouts,0) timeouts
, (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time
, decode ((e.total_waits - nvl(b.total_waits, 0)), 0,
to_number(NULL),
((e.time_waited_micro -
nvl(b.time_waited_micro,0))/1000) / (e.total_waits -
nvl(b.total_waits,0)) ) avgwait
, 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
In the above and some subsequent queries the following parameters
need to have appropriate values substituted for them:
§
BgnSnap: the start snapshot number for the AWR
snapshot interval of interest.
§
EndSnap: the finish snapshot number for the AWR
snapshot interval of interest.
§
DbId: the database identified of the target database.
§
InstNum: the instance number of the target database.
The sample output for this query looks like following:
SQL> @ Sys_event_int_10g.sql
Event Name Waits Timeouts Wait Time (s) Avg Wait
(ms) Wait Class
---------------------------- ----- -------- -------------
------------- ----------
control file parallel write 11719 0 119.13
10.17 System I/O
class slave wait 20 20 102.46
5,122.91 Other
Queue Monitor Task Wait 74 0 66.74
901.86 Other
log file sync 733 6 20.60
28.11 Commit
db file sequential read 1403 0 14.27
10.17 User I/O
log buffer space 178 0 10.17
57.16 Configuration
process startup 114 0 7.65
67.07 Other
db file scattered read 311 0 2.14
6.87 User I/O
control file sequential read 7906 0 1.33
.17 System I/O
latch free 254 0 1.13
4.45 Other
log file switch completion 20 0 1.11
55.67 Configuration
The Ion tool has a report named System Wait Events that runs the
above query with some additional information:
Figure 4.3:
AWR System Wait Events chart
in Ion
Figure 4.3 is a Ion screenshot that shows database wait event
details for a particular time period. The above chart was produced
using the System Wait Events report and shows how and when
particular wait events stressed the database the most during
observed snapshot interval.
Using the above chart, it is a quick task to identify instances when
the database spent the most wait time and what particular wait
events caused the wait.
The
dba_hist_event_name view can be used to help tune Oracle. Let’s
take a closer look.
awr_wait_events.sql
select
sn.end_interval_time,
trunc((e.time_waited_micro-b.time_waited_micro)/
(1000*(e.total_waits-b.total_waits)))
"ave read
milliseconds",
(e.total_waits-b.total_waits) "number of
reads",
b.event_name
from
see code depot for
full script
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_snapshot sn
where
b.event_name='db file sequential read'
and
e.event_name=b.event_name
and
e.snap_id=b.snap_id+1
and
e.instance_number=1
and
b.instance_number=e.instance_number
and
e.snap_id=sn.snap_id
and
e.instance_number=sn.instance_number
order by
e.snap_id;
display average wait microseconds script
col c1 heading "end Interval|time"
col c2
heading "number of|waits"
col c3 heading "average|microseconds"
select
sn.end_interval_time
c1,
(e.total_waits-b.total_waits)
c2,
(e.time_waited_micro-b.time_waited_micro)/
(e.total_waits-b.total_waits) c3
b.event_name
"wait name"
from
see code depot for full script
dba_hist_system_event b,
dba_hist_system_event e,
dba_hist_snapshot sn
where
b.event_name='&WAITNAME'
and
e.event_name=b.event_name
and
e.snap_id=b.snap_id+1
and
e.instance_number=1
and
b.instance_number=e.instance_number
and
e.snap_id=sn.snap_id
and
e.instance_number=sn.instance_number
and
(e.total_waits-b.total_waits) > 50000
order
by
e.snap_id;
In this wait event script, you will be prompted for one of these
wait events:
WAITNAME values
class slave wait
control file parallel write
control file sequential read
db file parallel
write
db file scattered read
db file sequential read
direct path read temp
direct path write temp
latch free
latch: redo allocation
log buffer space
log file parallel write
log file switch completion
log
file sync
process startup
Queue Monitor Task Wait
See the code depot page for instructions on downloading a
free copy of Ion
and include the URL for the page.
