Extending Oracle10g AWR
Oracle10g Enterprise Manager (EM) has a fantastic interface for easily creating exception alerts and mailing them directly to the Oracle professional. However, the EM has limitations. Until EM evolves into a true Decision Support System (DSS) for the Oracle DBA, the DBA will still need to extract and use the workload information stored in the AWR (Advanced Workload Repository).
Information in the AWR can be extracted for the following purposes:
- Complex exception reporting
- Correlation analysis
- Data Mining
- Developing metric signatures
- Hypothesis testing
There are more sophisticated exception reports that cannot be provided by EM. The data inside the AWR dba_hist views can be used by the senior DBA to perform sophisticated exception and correlation analysis. For example:
- Signature Analysis - We can use the AWR data to plot values of many important performance metrics, averaged by hour-of-the-day and day-of-the-week. For example, plotting physical reads and writes signatures will give the DBA insights into the regular variations in database stress. Knowing this information is critical to scheduling just-in-time changes to SGA resources, which the foundation of creating a self-tuning database. For more information on signature analysis for Oracle, see the book 'Creating a Self-Tuning Oracle Database' by Rampant TechPress.
- Hypothesis testing - The DBA can easily run correlation analysis scripts to detect correlations between important performance metrics. Queries can be developed to show the correlation between buffer busy waits and DML per second for specific tables, all averaged over long periods of time.
- Comparing a single value to a system-wide value - We can easily write custom scripts to compare the relationship between performance values. For example, issue an alert when the physical writes for any data files exceeds 25% of total physical writes.
To understand custom AWR reports let's start with a simple example using a couple of the most popular views, the dba_hist view, dba_hist_sysstat. The dba_hist_sysstat view is one of the most valuable of the AWR history tables because it contains instance-wide summaries of many important performance metrics. The full list of all 320 system statistics is noted in Listing 1, but these are the most commonly used statistics for exception reporting:
STATISTIC_NAME ---------------------------------------------------------------- cluster wait time concurrency wait time application wait time user I/O wait time enqueue waits enqueue deadlocks db block gets consistent gets physical reads physical read IO requests db block changes physical writes DBWR buffers scanned DBWR checkpoints hot buffers moved to head of LRU shared hash latch upgrades - wait redo log space requests redo log space wait time table scans (short tables) table scans (long tables) table fetch continued row leaf node splits leaf node 90-10 splits index fast full scans (full) session cursor cache hits buffer is not pinned count workarea executions - multipass parse time cpu parse time elapsed parse count (total) SQL*Net roundtrips to/from client sorts (memory) sorts (disk) sorts (rows)
Let's see a sample custom AWR query by starting with a simple query to plot the 'user I/O wait time' statistic for each AWR snapshot. From phys_reads.sql script we can see that it is easy to extract the physical read counts from the AWR.
break on begin_interval_time skip 2
column phyrds format 999,999,999
column begin_interval_time format a25
Below we see a running total of Oracle physical reads from phys_reads.sql. Note that the snapshots are collected every half-hour in this example, and many DBAs will increase the default collection frequency of AWR snapshots. Starting from this script, we could easily add a where clause criteria and create a unique time-series exception report.
BEGIN_INTERVAL_TIME FILENAME PHYRDS
------------------------- ---------------------------------------- ------------
24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF 164,700
The AWR is one of the most important advanced in Oracle since the introduction of STATSPACK. Now that we get the basic idea behind custom AWR scripts we can see how it is easy to customize AWR reports for more sophisticated analysis.