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.

Customized AWR Tuning Reports:

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)

Creating a custom AWR Report for file I/O

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

select 

   begin_interval_time, 

   filename,

   phyrds

from

   dba_hist_filestatxs 

natural join

   dba_hist_snapshot

;

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.

SQL> @phys_reads

BEGIN_INTERVAL_TIME       FILENAME                                       PHYRDS

------------------------- ---------------------------------------- ------------

24-FEB-04 11.00.32.000 PM E:\ORACLE\ORA92\FSDEV10G\SYSTEM01.DBF         164,700

                          E:\ORACLE\ORA92\FSDEV10G\UNDOTBS01.DBF         26,082

                          E:\ORACLE\ORA92\FSDEV10G\SYSAUX01.DBF         472,008

                          E:\ORACLE\ORA92\FSDEV10G\USERS01.DBF            1,794

                          E:\ORACLE\ORA92\FSDEV10G\T_FS_LSQ.ORA           2,123

Conclusion

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.