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 








The Oracle Statspack Analyzer

Oracle Database Tips by Donald Burleson

Click here for a free AWR report analysis

Since the dawn of Oracle (circa 1989), Oracle DBA's have struggled to interpret the plethora of statistics that are provided by the Oracle time-series performance reports.  These reports have taken several forms over the years, but the core content has remained identical:

  • bstat-estat reports - Oracle6 through Oracle8
  • STATSPACK reports - Oracle8i to present
  • Automated Workload Repository reports - Oracle 10g

Originally, Oracle's elapsed-time reports were intended solely for Oracle technical supports and they remain full of undocumented performance metrics, page after page of undocumented gobbledygook:

Statistic                             Total     per Second    per Trans
--------------------------------- --------- -------------- ------------
DFO trees parallelized                    1            0.0          0.0
calls to get snapshot scn: kcmgss    55,918           15.5        860.3
calls to kcmgas                       9,929            2.8        152.8
calls to kcmgcs                          59            0.0          0.9
cleanout - number of ktugct calls    11,057            3.1        170.1

The tuning challenge for the Oracle DBA

Every Oracle tuning expert has their own method for reading and interpreting STATSPACK reports, sifting through thousands of lines of details to arrive at a set of valid tuning recommendations.

With the incorporation of the Automated Workload Repository into the database kernel in Oracle 10g, STATSPACK (AWR) reports have become the de-facto standard for Oracle performance reports.  The challenge for the Oracle DBA is to separate the wheat from the chaff and focus on those metrics that are the most important for understanding the nature of the performance problem. 

To see the manual report analysis steps, I've devoted an entire chapter to reading a STATSPACK report in my book "Oracle Tuning: The Definitive Reference".

Interpreting an Oracle STATSPACK/AWR report will always be complex and it's doubtful that anyone will invest the thousands of hours of work required to replicate the analysis of a human expert.

On the other hand, it is possible to create basic decision rules (heuristics) that can be generalized and applied to all STATSPACK and AWR reports.  For a simple example, it's easy to see when an Oracle database has slow disks (latency > 50 ms) and it's always worth noting whenever a tablespace has disk I/O latency that is more than 30% slower than the average I/O speed.

Towards an expert system for automated tuning analysis

There are many applications of expert systems and applied artificial Intelligence with Oracle, but the Oracle community has always desired a software tool that could apply basic decision rules to generic STATSPACK and AWR reports.

Burleson Consulting in conjunction with Texas Memory Systems has taken-on this challenge to build an expert system for intelligent analysis of STATSPACK reported.  The web site (AWR Analyzer), is the result of hundreds of hours of work by a consortium of Oracle performance tuning experts, all working to create valid decision rules that might be applied to any time-series Oracle report.

A work-in-progress, the AWR Analyzer tool is already the best AWR and STATSPACK analyzer in the world, and we hope to continue to expand and refine the decision rules as the site sponsors get feedback from actual users and DBA's.

The AWR Analyzer already does an excellent job of locating obvious bottlenecks for server resources (disk, RAM, CPU, Network) and it has many sophisticated decision rules for analysis of Oracle internal performance. 

Of course, locating Oracle internal resource bottlenecks is more challenging.  Analyzing a thousand-line report is not trivial, and many dozens of observation points may be required to formulate a decision rule.  Further, the generalization of these complex rules may make them prone to "false positives", the reporting of a non-existent bottleneck.

As the the AWR Analyzer effort gains momentum, the team of experts will continuously refine and expand the decision rules, working towards a software solution that will mimic the analysis of a human expert.

Obviously, Statspackanalyzer will never be able to model the intuition of an expert because it's impossible to quantify those "I have a feeling" hunches that distinguish the real experts, but this tool shows great promise as a research tool.




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.