Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Extending Oracle10g AWR

Oracle Tips by Burleson Consulting

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 'Oracle Tuning: The Definitive Reference' 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:

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

natural join

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
------------------------- ---------------------------------------- ------
                          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


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.

The Ion tool is the easiest way to analyze AWR disk I/O in Oracle and Ion allows you to spot hidden I/O trends.

An IBM disk I/O monitor

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.