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.
See the code depot page for instructions on downloading a
free copy of Ion
