Monitoring oracle RDBMS Standard Edition

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 ;