Oracle AWR Exception Reporting Oracle Tips by Burleson Consulting

Exception Reporting with the AWR

At the highest level, exception reporting involved adding a WHERE clause to a data dictionary query to eliminate values that fall beneath a pre-defined threshold.  For a simple example, this can be done quite easily with a generic script to read dba_hist_sysstat.

AWR uses a "Top N" method which defaults to collect the Top-30 SQL statements for each SQL category (statistics_level=typical).  If you set statistics_level = all, AWR will collect the top 100 SQL statements.

The following simple script called rpt_sysstat_10g.sql displays a time-series exception report for any statistic in dba_hist_sysstat.  The script accepts the statistics number and the value threshold for the exception report.

Notice that this simple script will prompt you for the statistic name and threshold value; allowing ad-hoc AWR queries:

SQL> @rpt_sysatst

This will query the dba_hist_sysstat view to display all values

that exceed the value specified in

the "where" clause of the query.

Enter Statistic Name:  physical writes

Enter Statistics Threshold value:  200000

SNAP_TIME                  VALUE

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

2004-02-21 08:00         200,395

2004-02-27 08:00         342,231

2004-02-29 08:00         476,386

2004-03-01 08:00         277,282

2004-03-02 08:00         252,396

2004-03-04 09:00         203,407

The listing above indicates a repeating trend where physical writes seem to be high at 8:00 AM on certain days.  This powerful script will allow the DBA to quickly extract exception conditions from any instance-wide Oracle metric and see its behavior over time.

The next section provides a more powerful exception report that compares system-wide values to individual snapshots.

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts: