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

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 (sql_id='&&sql_id' or '&&sql_id' is null)
and (tc='&&event_descr' or '&&event_descr' is null)
... Code from LIsting 14-5
Enter value for sql_id: brd3nyszkb4v3
old 33: where (sql_id='&&sql_id' or '&&sql_id' is null)
new 33: where (sql_id='brd3nyszkb4v3' or 'brd3nyszkb4v3' is null)
Enter value for event_descr: cell smart table scan
old 34: and (tc='&&event_descr' or '&&event_descr' is null)
new 34: and (tc='cell smart table scan' or 'cell smart table scan' is null)
Begin Time SQL ID DBT AAS Component DBTPerMetric AASPerMetric
------------------ ------------- -------- ----- --------------------- ------------ ------------
07-NOv-12 18:05:21 brd3nyszkb4v3 4660.00 15.53 cell smart table scan 3110.00 10.37
07-NOv-12 18:10:21 brd3nyszkb4v3 27450.00 91.50 cell smart table scan 12250.00 40.83
This query displays two intervals in which cursor brd3nyszkb4v3 performed Exadata smart scans and waited on
the cell smart table scan wait event.