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:
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
/
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
/
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
/
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
/
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.
1050 Wilshire Drive,
Suite 170,
Troy, MI 48084
Phone: (248) 465-9533
Toll free: 1-877-868-1753
Email: [email protected]
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.
© Centroid, Inc. All rights reserved. Contact Privacy Policy Terms of Use CCPA Policy