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

Begin Time SQL ID DBT AAS Component DBTPerMetric AASPerMetric
------------------ -------------- ----- ---- --------------------- ------------ ------------
08-NOv-12 13:40:54 NULL 3.00 .02 CPU + CPU Wait 2.00 .01
c3rvcbu8r3zx8 CPU + CPU Wait 1.00 .01
08-NOv-12 13:43:54 NULL 2.00 .01 CPU + CPU Wait 2.00 .01
08-NOv-12 13:46:54 NULL 4.00 .02 CPU + CPU Wait 4.00 .02
08-NOv-12 13:49:54 NULL 3.00 .02 CPU + CPU Wait 1.00 .01
NULL ASM file metadata operation 1.00 .01
NULL control file sequential read 1.00 .01
... Output omitted for brevity
How It Works
Over the years, Oracle DBAs, performance engineers, and developers have used a number of techniques to identify
and optimize Oracle performance. In recent years, the Oracle Database Performance Method, described in
docs.oracle.com/cd/E11882_01/server.112/e10822/tdppt_method.htm has become increasingly adopted and
utilized to focus performance monitoring and optimization because it is based on the most important metric from
an end user's or application's perspective: time. While statistics, counters, ratios, wait events, and a number of other
types of information are valuable inputs to an overall performance assessment analysis, the most important aspect of
performance is time, or time per interval.
In this recipe, we demonstrated using Oracle's ASH framework as the basis for time-based performance monitoring.
At a high level, Oracle's ASH buffer is externalized in the v$ACTIvE_SESSION_HISTORY view and, at one-hour intervals,
Oracle summarizes this information and publishes to the WRH$_ tables. The DBA_HIST_ACTIvE_SESS_HISTORY view can
then be used to query AWR data from the WRH$_ tables. When Oracle writes ASH data from the ASH buffer to the
WRH$_ tables, one of every ten in-memory samples is captured, which is why the historical ASH queries in the solution of
this recipe multiply the sample counts by ten.
The AAS metrics is a reflection of the number of sessions actively consuming CPU resources or actively waiting
on a named Oracle wait event over a time interval; in other words, the AAS metric is a reflection of the average Oracle
database load for a time range. AAS can be formulated as follows:
AAS = D (Database Time) / Elapsed clock time
AAS = S (ASH samples, where D =1 second ) / Elapsed clock time
This metric is important for a number of reasons and is often used to size and/or validate whether your
underlying compute node infrastructure is capable of handling your workload. With Exadata, this is especially
important because most organizations make the Exadata investment for performance reasons. Oracle places such
a high value on the AAS metric and database time methodology that it uses the underlying ASH data for the main
chart on the Performance landing page in Enterprise Manager, as displayed in Figure 14-13 . In Figure 14-13 , the line
on the Y-axis represents AAS and the area under the curve represents database time, broken apart by the various
components of time that make up the workload. The red line at the top of this chart shows the total number of
CPU cores for your cluster; Oracle displays this because as AAS approaches your processor ceiling, it is typically an
indication that performance could be impacted.