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 







Oracle Advisory Statistics

Oracle Database Tips by Donald Burleson

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

When Oracle 10g was released, Oracle introduced its common manageability infrastructure, also known as the advisory framework. This framework includes server-based advisors, chief among them being the Automatic Database Diagnostic Monitor (ADDM). Embedded within ADDM, since it can call other lower-level advisors, are advisors related to SQL, memory and space management.


The named advisors include:

  • SQL Tuning Advisor

  • SQL Access Advisor

  • PGA Advisor

  • SGA Advisor

  • Segment Advisor

  • Undo Advisor

In release 11g, there are advisors and other tools introduced in 10g, and there are also two new utilities: the Automatic Diagnostic Repository (ADR) and the SQL Performance Advisor (SPA).


To further clarify, the following question should be addressed: Is Feature X an advisor, a utility, or both? Depending on the feature, the answer in many cases is both. Access to several advisors outside of a GUI, such as Enterprise Manager, can be accomplished by executing the underlying SQL scripts. A classic example of that is the Automatic Workload Repository. An AWR report contains several advisory-like sections. Deep inside an AWR report, there is a section on Advisory Statistics.


Figure 8.1:  Advisory Statistics in AWR


The PGA and SGA statistics captured in the report can also be obtained via canned SQL scripts.


As a caution, it is up to the DBA to determine access to use extended licensed features. If one is not licensed to use the Tuning or Diagnostics packs, for example, not only does that mean these utilities cannot be used within Enterprise Manager, or Database Control and GUI variations thereof, it also means one cannot use any script - the DBA's or Oracle's - to query the contents of any of the workload repository tables and views. If there are any doubts about what one is licensed for, contact the Oracle sales representative. The Licensing Information guide published with each release can also be reviewed. 


With that caution out of the way, start by looking at two new features in 11g and then cover some historical utilities. The first two sections cover ADRCI and SQL Performance Analyzer (SPA). Both sections are somewhat lengthy because of their nature: they are quite powerful and useful. One can only hope that they will be back-ported to older versions of Oracle as that is how good they are.

ADRCI: ADR Command Interpreter

The ADR of interest here is the Automatic Diagnostic Repository, not the ADR (Automatic Data Repair) associated with LogMiner. The Automatic Diagnostic Repository maintains diagnostic data that can be uploaded to Oracle using the Incident Packaging Service (IPS).  As in the old days, part of what Oracle still asks for is a zipped or compressed set of files created by having downloaded and run the RDA tool . The remote diagnostics script(s) collected information about the system. In one sense, ADR can be thought of as a prepositioned hRDA tool within the database.


If support analysts can look at diagnostic data, then so can the DBA. From Oracle's perspective, it is easy to see where they are coming from, as in "Let's make it easier for customers to not only diagnose failure, but also to package it up and send us information in a standardized format". The side benefit of this perspective is that now the data can be seen in the same format or package. This could be done before to a large degree with RDA, but this is even better.

Changes in Oracle 11g

In addition to this idea , the architecture of how the RDBMS is installed on a computer has also undergone some significant changes . To be honest, use of Oracle 11g is going to be somewhat of a culture shock to people accustomed to older versions of Oracle. The number one shock will be saying goodbye to the Windows SQL*Plus interface for what is there  now is a command prompt window. Number two is the location of dump and trace files in relation to the old admin directory. And the number three most significant shock, though there are more than just three, is how the contents of the alert log are produced. The standard alert log in 11g is an XML file. Not to worry since Oracle still provides the old version of the alert log in another directory.


Part of the new architecture includes a "diag" directory in what is analogous to ORACLE_BASE/diag/rdbms/<SID>. The arrangement of certain files is referred to as a unified directory structure and this structure can apply to more than one instance. Each instance has its own ADR Home, and just like ORACLE_HOME, there is an ADR Base.


The ADR Home turns out to be a file-based repository of diagnostic related data such as the alert log, trace files, and dumps. The idea of an ADR Home even extends to RAC and ASM. Crashes, faults, problems - whatever the errant condition is - can be viewed as incidents, and that leads back to being able to package up files relevant to an incident, hence the Incident Packaging Service. All of this is part of the fault diagnosibility infrastructure.


The command interpreter interface into the ADR is the ADRCI command-line tool ( or adrci.exe). ADRCI enables viewing diagnostic data, viewing Health Monitor reports, and as mentioned, performing incident packaging operations.


There are two ways to access the Automatic Diagnostic Repository. One is via the Enterprise Manager Support Workbench. Since it is "known" that GUI tools are for the weak and lazy, it is best to concentrate on the command-line interface. In all seriousness, the ability to invoke the command line utility should hover around 100%, even if the database is not open or the console is not running, or host credentials are not set, or Web interface is not enabled or blocked in the environment. The idea that relying on Enterprise Manager factors in many points of failure should be coming clearer. Therefore, knowing how to use ADRCI is evidently useful.

NOTE: Rampant author Laurent Schneider has some additional insight into creating an Oracle Automatic Diagnostic Repository (ADR)

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by top Oracle database experts (Bert Scalzo,  Donald Burleson, and Steve Callan). 

Buy direct from the publisher and save 30%!



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.