Host and Database Performance Monitoring - Oracle Exadata Recipes: A Problem-Solution Approach - page 425

with the &&days_ago parameter, a sample duration with the &&duration_min parameter, and a sampling window as
specified by the &&interval_mins parameter:
Listing 14-8. lst14-08-ss-ashpast-details.sql
... Column formatting and min/max dates identical to Listing 14-7
SQL> WITH xtimes (xdate) AS
(SELECT to_date('&_bt') xdate FROM dual
UNION ALL
SELECT xdate+(&&interval_mins/1440) FROM xtimes
WHERE xdate+(&&interval_mins/1440) <= to_date('&_et')
)
select * from (
select to_char(xdate,'DD-MON-RR HH24:MI:SS') samplestart,
nvl(sql_id,'NULL') sql_id,
(sum(decode(event,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS'))) smpcnt,
(sum(decode(event,null,0,dbtw)) over
(partition by to_char(xdate,'DD-MON-RR HH24:MI:SS')))/60/&&interval_mins aas,
nvl(event,'*** IDLE *** ') tc,
decode(event,null,0,dbtw) dbtw,
decode(event,null,0,dbtw)/60/&&interval_mins aas_comp
from (
select s1.xdate,event,10*count(*) dbtw,sql_id,count(ash.smpcnt) smpcnt
from (
select sample_id,
sample_time,
session_state,sql_id,
decode(session_state,'ON CPU','CPU + CPU Wait',event) event,
10*(count(sample_id) over (partition by sample_id)) smpcnt
from dba_hist_active_sess_history
where to_date(to_char(sample_time,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR
HH24:MI:SS')
between to_date('&_bt') and to_date('&_et')) ash,
(select xdate from xtimes ) s1
where ash.sample_time(+) between s1.xdate and s1.xdate+(&&interval_mins/1440)
group by s1.xdate,event,sql_id)
order by 1,dbtw desc)
where tc like 'cell smart%'
/
How It Works
Oracle database performance method and the AAS metrics, as introduced in Recipe 14-5, are important for the
Exadata DMA to be familiar with. Specific to measuring time-based smart scan behavior, the key is to capture
session-history-related information from ASH using the cell smart table scan and cell smart index scan
Oracle wait events.
Oracle has introduced additional Exadata-related wait events in 11gR2 to capture Exadata-related instrumentation
values. When sessions enter a state in which they are waiting for I/O as a result of a smart scan request, the session will
post a wait on the cell smart table scan event for table scans and cell smart index scan event for offloadable
index scans. You may also witness sessions waiting on the cell single block physical read or cell multiblock physical