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

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
/
Begin Time SQL ID DBT AAS Component DBTPerMetric AASPerMetric
------------------ ------------- --------- ----- --------------------- ------------ ------------
08-NOv-12 01:41:44 brd3nyszkb4v3 40980.00 68.30 cell smart table scan 15860.00 26.43
b6jzrwqggzt1u cell smart table scan 14740.00 24.57
g26s1uz7hmadb cell smart table scan 2850.00 4.75
brd3nyszkb4v3 CPU + CPU Wait 2680.00 4.47
cp7daugwu0s3q cell smart table scan 1340.00 2.23
g26s1uz7hmadb CPU + CPU Wait 760.00 1.27
b6jzrwqggzt1u CPU + CPU Wait 480.00 .80
... Output omitted for brevity
In this output, during the sampled time interval, SQL ID brd3nyszkb4v3 had 26.43 AAS waiting on the cell
smart table scan wait event. On Exadata, you can use the “ cell smart %” component details to understand the cell
offload AAS metrics.
Monitoring Historical Database Time and AAS metrics for Specific SQL
Statements or Wait Events
Limit your report output to a specific SQL statement and/or component by placing additional logic in the script.
Run the script in Listing 14-5 for a specific SQL ID in your environment, searching for database time and AAS metrics
representing Exadata smart scans.
Listing 14-5. lst14-05-dbt-ashpast-bydtl.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 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 (sysdate-&&days_ago)
and ((sysdate-&&days_ago)+(&&duration_min/1440));
SQL> WITH xtimes (xdate) AS
(SELECT to_date('&_bt') xdate FROM dual
UNION ALL