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

Monitoring Recent, Detailed Database Time and AAS Metrics
To report detailed database time and AAS metrics from the in-memory Oracle ASH buffer, which displays granular,
un-summarized ASH data, run the script in Listing 14-6. This script is similar to the script in Listing 14-4 but extracts
its data from v$ACTIvE_SESSION_HISTORY instead of DBA_HIST_ACTIvE_SESS_HISTORY .
Listing 14-6. lst14-06-dbt-ashcurr-details.sql
... Column formatting omitted
SQL> col bt NEW_vALUE _bt NOPRINT
SQL> col et NEW_vALUE _et NOPRINT
SQL> select min(cast(sample_time as date)) bt,
max(cast(sample_time as date)) et
from v$active_session_history
where to_date(to_char(sample_time,'DD-MON-RR HH24:MI:SS'),'DD-MON-RR HH24:MI:SS')
between (sysdate-&&days_ago)
and ((sysdate-&&days_ago)+(&&duration_min/1440));
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 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,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,
(count(sample_id) over (partition by sample_id)) smpcnt
from v$active_session_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;