Monitoring Exadata with Wait Events

You can monitor cell and Exadata functionality specific wait events as you would normally in 11gR2, but some key additional wait events have been added to provide additional detailed information.

The events above are cell-related wait events, but there are also Flash Cache related events as well, with the scripts provided in a subsection below.

In addition to the normal wait interface view above, additional cell-related views exist with explanations below:

  • V$CELL_STATE
  • V$CELL_THREAD_HISTORY
  • V$CELL_REQUEST_TOTALS – shows statistics for cells according to snapshot information. Note that this snapshot information does not seem to be related to AWR snapshots but rather CELLSRV snapshot time period on the cell.
  • V$CELL
  • V$CELL_CONFIG

Script: cellwait.sql

cellwait.sql shows sessions actively waiting on cell-related activities.

set linesize 120
set pages 55
col sid format 99999
col name format a36
col p1 format a10 Head 'P1'
col program format a25
col p2 format a10 Head 'P2'
col p3 format a10 Head 'P3'
col pgm format a15 head 'What'
col state format a15
col wt format 9999999 head 'Wait|Time'
col WaitEvent format a30 head 'Wait Event'
col lc format 99999999999.99 head 'last
call'
select A.sid,
A.event WaitEvent,
decode(A.event,'slave wait','N/A',
'PX Deq: Execution Msg','N/A',
'PX Deq Credit: send blk','N/A',
'latch free','N/A',
'enqueue',
chr(bitand(A.p1,-16777216)/16777215)||chr(bitand(A.p1,16711680)/65535),
'file open','-1',to_char(A.p1)) p1,
decode(A.event,'enqueue',decode(mod(A.p1,16),'6','ROW-LOCK','4','ITL','3',
'FK?','OTHER'),
'file open',
-1,
A.p2) p2,
decode(A.event,'latch
free','N/A','enqueue',null,'PX qref latch','-1',
'buffer busy waits',to_char(A.p3), A.p3) p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm
from v$session_wait A,
v$session B
where 1=1
and A.event like 'cell%'
and A.sid=B.sid
and B.status='ACTIVE'
order by 8
/

Script: flashwait.sql

flashwait.sql shows sessions actively waiting on
cell-related activities.
set linesize 120
set pages 55
col sid format 99999
col name format a36
col p1 format a10 Head 'P1'
col program format a25
col p2 format a10 Head 'P2'
col p3 format a10 Head 'P3'
col pgm format a15 head 'What'
col state format a15
col wt format 9999999 head 'Wait|Time'
col WaitEvent format a30 head 'Wait Event'
col lc format 99999999999.99 head 'last
call'
select A.sid,
A.event WaitEvent,
decode(A.event,'slave wait','N/A',
'PX Deq: Execution Msg','N/A',
'PX Deq Credit: send blk','N/A',
'latch free','N/A',
'enqueue',
chr(bitand(A.p1,-16777216)/16777215)||chr(bitand(A.p1,16711680)/65535),
'file open','-1',to_char(A.p1)) p1,
decode(A.event,'enqueue',decode(mod(A.p1,16),'6','ROW-LOCK','4','ITL','3',
'FK?','OTHER'),
'file open',
-1,
A.p2) p2,
decode(A.event,'latch
free','N/A','enqueue',null,'PX qref latch','-1',
'buffer busy waits',to_char(A.p3), A.p3) p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm
from v$session_wait A,
v$session B
where 1=1
and A.event like '%flash cache%'
and A.sid=B.sid
and B.status='ACTIVE'
order by 8
/

Script: cellwaitdetail.sql

cellwaitdetail.sql shows wait activity and corresponding ASM disk and cell-related information. Note that this script has been pulled from the Exadata Administrator class and I think it’s not quite linking things correctly – it seems to Cartesian-product all the existing ASM disk path names regardless of whether the wait is actively on disk/cell. We’ve explored v$session_wait.p2.values for the “cell smart table scan” wait and since it’s “0” and all v$asm_disk.hash_value values are zero (at least for this wait event occurrence, it will show all ASM/cell details.

set lines 200
col event format a35 head 'Event'
col cell_path format a40
col name format a30
col p3 format 99999999999999999
SELECT w.event, c.cell_path, d.name, w.p3
FROM V$SESSION_WAIT w,
V$EVENT_NAME e,
V$ASM_DISK d,
V$CELL c
WHERE e.name LIKE 'cell%'
AND e.wait_class_id = w.wait_class_id
AND w.p1 = c.cell_hashval
AND w.p2 = d.hash_value
and w.p2 <> 0
/

Script: exa_sysevents.sql

set lines 120
col instance_name format a6
col event format a35
col total_waits format 999,999,999
col total_timeouts format 999,999,999
col time_waited format 999,999,999
col average_wait format 9,999.99
col wait_class format a20
select inst.instance_name,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
wc.wait_class
from gv$system_wait_class wc,
gv$system_event se,
gv$instance inst
where inst.inst_id = se.inst_id
and inst.inst_id=wc.inst_id
and se.wait_class_id=wc.wait_class_id
and (se.event like '%cell%' or se.event like
'%flash cac%')
order by 5 desc
/

Script: exa_sessevents.sql

set lines 120
col instance_name format a6
col event format a35
col total_waits format 999,999,999
col total_timeouts format 999,999,999
col time_waited format 999,999,999
col average_wait format 9,999.99
col wait_class format a20
select inst.instance_name,
se.event,
se.total_waits,
se.total_timeouts,
se.time_waited,
se.average_wait,
wc.wait_class
from gv$system_wait_class wc,
gv$session_event se,
gv$instance inst
where inst.inst_id = se.inst_id
and inst.inst_id=wc.inst_id
and se.wait_class_id=wc.wait_class_id
and se.sid=&sid
and (se.event like '%cell%' or se.event like
'%flash cac%')
order by 5 desc
/

 

Summary

Measuring Exadata with Oracle wait events is vital to understanding how well your Exadata Database Machine is working in relation to non-Exadata solutions.