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

Specific to Exadata, one of the SQL execution characteristics commonly monitored is the SQL statement's
offload efficiency. The offload efficiency is reported from the Monitored SQL Execution Details window in both
the I/O statistics section of the Overview pane as well as at a per-operation level in the SQL execution plan, as shown
in Figure 14-12 .
Figure 14-12. Cell offload efficiency from SQL Monitoring
14-5. Monitoring Performance by Database Time
Problem
You wish to collect recent and historical performance statistics for your Exadata databases, focusing on database time
and the Average Active Sessions (AAS) metric.
Solution
In this recipe, you will learn how to extract database time-based and AAS performance information using Active
Session History (ASH) views.
Monitoring Historical Summarized Database Time and AAS Metrics from ASH
To report on summarized database time and AAS metrics from ASH views for an Oracle 11gR2 database running on
Exadata, log in to SQL*Plus and execute the script in Listing 14-3.
Listing 14-3. lst14-03-dbt-ashpast-bytype.sql
... Formatting options omitted for brevity
SQL> col bt NEW_vALUE _bt NOPRINT
SQL> col et NEW_vALUE _et NOPRINT
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';
SQL> select min(cast(sample_time as date)) bt,
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')