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

Figure 14-13. Average Active Sessions from Enterprise Manager
The scripts listed in this recipe provide a means to summarize database time and AAS performance information
from SQL scripts, and they can be used to plot key performance information over a user-defined time range, similar to
what you can achieve with Enterprise Manager.
There are a number of resources available to learn more about the Oracle's database performance methodology
and AAS. We suggest consulting Oracle's documentation first and then expanding to a Google search, as there are
many white papers and blogs available to the public.
14-6. Monitoring Smart Scans by Database Time and AAS
Problem
You wish to monitor recent and historical performance information specific to Exadata smart scans in order to
measure the frequency and time consumed by Smart Scans relative to your overall database workload.
Solution
In this recipe, you will learn how to query Oracle's ASH views to report detailed smart scan behavior based on the
database time and AAS performance method, as initially introduced in Recipe 14-5.
Monitoring Recent and Current Smart Scan Performance Metrics from ASH
To measure recent and current smart-scan-related AAS metrics from ASH, run the script in Listing 14-7. In this script,
we will report on summarized AAS data related to Smart Scan and cell offload queries and group the output on the
SQL ID and a user-entered sample interval.
Listing 14-7. lst14-07-ss-ashcurr-details.sql
SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';
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')
> (sysdate-&&mins_ago/1440);
SQL> WITH xtimes (xdate) AS
(SELECT to_date('&_bt') xdate FROM dual