Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle SQL Tuning Advisor
Oracle Tips by Burleson Consulting

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

Through the SQL Tuning Advisor, information ADDM has analyzed from the AWR is used to identify high load or poorly performing SQL statements. ADDM also knows about the top SQL and its impact on the system. Once a statement is identified, then ADDM can pass the statement into another analyzer, so to speak, and come up with recommendations. The SQL Tuning Advisor is the engine behind the scenes and it develops finer tuned plans along with some advice.


The recommendations fall into four categories:

1.      Statistics Analysis – The advisor checks the status of statistics (stale or missing) and if necessary, gathers new or updated statistics.

2.      SQL Profiling – What was the past execution history like? Profiling can use the history of a statement to generate a well-tuned plan. This is especially useful for applications where the DBA has no control over the code. What can be done is, via the advisor, build a better plan around the code.

3.      Access Path Analysis – This is a lot like what performance tuning in SQL Server does. If it is determined that an index will help a statement, the index will be automatically created. In Oracle, a recommendation will be received to build the index for it is not built for the DBA.

4.      SQL Structure Analysis – This component of the advisor identifies bad plans and offers suggestions as to restructuring them. Restructuring suggestions include both syntactic and semantic changes to code.

The APIs for this advisor are discussed after the next section.

SQL Access Advisor

Right behind and alongside the SQL Tuning Advisor is the SQL Access Advisor. The former helps fix or tune statements. The second offers analysis based on recommending indexes, partitions, and materialized views. The Access Advisor works not only on single statements, but also on complete business workloads. One of the features of SQL Access Advisor is that what is gathered on a production system can be transferred to another system. 


The SQL Tuning Advisor and SQL Access Advisor command-line APIs are accessed via the DBMS_SQLTUNE and DBMS_ADVISOR PL/SQL built-ins. The sqltrpt.sql script in the rdbms/admin directory is also part of the licensed Tuning Pack. Access to V$SQL_MONITOR and V$SQL_PLAN_MONITOR are as well.


In Oracle 11g, a new initialization parameter helps to control access to Tuning Pack and Diagnostics Pack features. The parameter CONTROL_MANAGEMENT_PACK_ACCESS can be set to:

  • DIAGNOSTIC+TUNING – the features from both packs are enabled

  • DIAGNOSTIC – only the features from the Diagnostic Pack are enabled

  • NONE – features from both packs are disabled

The settings can also be controlled or accessed via Enterprise Manager (see the Setup link). 

WARNING: The default setting is for DIAGNOSTIC+TUNING. If during an audit Oracle License Management determines that licensed features have been used above and beyond reasonable use, such as for education, research or demonstration, be prepared to pay the consequences!

Having a hard time reading this sentence.  Are we saying that transferring gathered queries/stats from production to a test or tuning environment is a critical feature of the SQL Access Advisor?

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.


Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.