http://www.kylehailey.com/category/oracle/wait-events-oracle/
The wait events are rolled up in to groups called wait classes. For wait class we have the following views:
- V$SYSTEM_WAIT_CLASS – cumulative since start up
- V$WAITCLASSMETRIC – last 60 seconds deltas
- V$WAITCLASSMETRIC_HISTORY – 60 seconds deltas for last hour
select
sum(NVL(h.TIME_WAITED, 0) ) time_waited,
n.WAIT_CLASS
from
v$waitclassmetric_history h,
v$system_wait_class n
where
h.WAIT_CLASS# = n.WAIT_CLASS#
group by
n.WAIT_CLASS
order by
1 desc
SELECT b.username,
a.stat_name,
sum(round((a.value / 1000000),3) ) time_secs
FROM v$sess_time_model a,
v$session b
WHERE a.sid = b.sid
group by
b.username, a.stat_name
ORDER BY 1, 3 DESC
Latencies for specific I/O Wait Events
For specific I/O latencies there are two choices – v$eventmetric and
v$system_event. With v$system_event it requires running multiple
queries and taking the deltas but the deltas are are already calculated
in v$eventmetric. Here is an example of getting I/O latencies from
v$eventmetric
Latencies in the past minute
col name for a25
select m.intsize_csec,
n.name ,
round(m.time_waited,3) time_waited,
m.wait_count,
round(10*m.time_waited/nullif(m.wait_count,0),3) avgms
from v$eventmetric m,
v$event_name n
where m.event_id=n.event_id
and n.name in (
'db file sequential read',
'db file scattered read',
'direct path read',
'direct path read temp',
'direct path write',
'direct path write temp',
'log file sync',
'log file parallel write'
)
/
INTSIZE_CSEC NAME TIME_WAITED WAIT_COUNT AVGMS
------------ ------------------------- ----------- ---------- ----------
6017 log file parallel write 2.538 4 6.345
6017 log file sync 2.329 1 23.287
6017 db file sequential read 0 0
6017 db file scattered read 0 0
6017 direct path read 0 0
6017 direct path read temp 0 0
6017 direct path write 0 0
6017 direct path write temp 0 0
but the big drawback with wait event and/or wait class views is that
they lack information on CPU load. CPU load can be found in the system
statistics but it’s just easier to do it all in one query using v$active_session_history. Here is a query using ASH to calculate AAS load on the database over the last 60 seconds:
select
round(count(*)/secs.var,3) AAS,
decode(session_state,'ON CPU','CPU',wait_class) wait_class
from v$active_session_history ash,
(select 60 var from dual) secs
where
SAMPLE_TIME > sysdate - (secs.var/(24*60*60)) and
SESSION_TYPE = 'FOREGROUND'
group by decode(session_state,'ON CPU','CPU',wait_class) , secs.var
Though with v$sysmetric it’s pretty easy to join to v$waitclassmetric
select n.wait_class, round(m.time_waited/m.INTSIZE_CSEC,3) AAS
from v$waitclassmetric m,
v$system_wait_class n
where m.wait_class_id=n.wait_class_id
and n.wait_class != 'Idle'
union
select 'CPU', round(value/100,3) AAS from v$sysmetric
where metric_name='CPU Usage Per Sec'
and group_id=2 ;
| 
Меню сайта