| |
 |
|
Oracle
Metric Total Wait Time
Oracle Tips by Burleson Consulting
|
The
Total Wait Time
Oracle metric is the total amount of time waited for this
event, in hundredths of a second.
The next query against the
dba_hist_active_sess_history
view reports a list of resources that were in high demand in the
last hour. This query does not reflect idle wait events.
select
e.name
"Wait Event",
SUM(h.wait_time +
h.time_waited ) "Total Wait Time"
from
v$active_session_history h,
v$event_name
e
where
h.event_id = e.event_id
and
e.wait_class <> 'Idle'
group by
e.name
order by 2 DESC;
This query produces a listing
like the one below, showing aggregate wait time for each event:
Wait Event
Total Wait Time
------------------------------
---------------
log buffer
space 9,638,484
db file sequential read
8,442,918
log file switch
completion 5,231,711
write complete
waits 5,200,368
db file scattered
read 4452,153
process
startup 3623,464
rdbms ipc
reply 917,765
log file
sync 662,224
latch
free 550,241
latch: library
cache 370,696
db file parallel
write 364,641
free buffer
waits 319,151
latch: redo
allocation 64,984
LGWR wait for redo
copy 63,647
read by other
session 52,757
log file sequential
read 46,126
null
event 33,011
log file parallel
write 26,280
SQL*Net E "SQL*Net" more data
to client 8,894
latch: cache buffers
chains 7,005
control file sequential
read 3,966
direct path read
temp 395
direct path write
temp 229
SQL*Net E "SQL*Net" message
to client 74
From the listing above, one can see that the DBA
has an issue with the
log buffer space wait
event that may
indicate the need to increase the
log_buffer parameter
to increase the cache in order to minimize this possible
bottleneck.
Using the AWR ASH view, the DBA can also
retrieve a list of database users who have experienced high wait
times during the time period between any two snapshots. The
following query can be used to identify these target users
select
s.sid,
s.username,
sum(h.wait_time + h.time_waited E "time_waited"
) "total wait time"
from
v$active_session_history h,
v$session s,
v$event_name e
where
h.session_id = s.sid
and
e.event_id = h.event_id
and
e.wait_class <> 'Idle'
and
s.username IS NOT NULL
group by
s.sid, s.username
order by 3;
This sample output shows the total wait time,
both by process ID (SID) and by individual users.
SID USERNAME
total wait time
---------- ---------------
---------------
261
SYS 1,537,288
259
SYS 12,247,007
254
SYS 18,640,736
|
