Monitoring Exadata with Wait Events

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.