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

Oracle Tips by Burleson Consulting
Mark Rittman

Oracle9i came with a feature known as the Summary Advisor that recommended a set of materialized views for a given query workload. Consisting of a number of procedures and functions within the DBMS_OLAP package, together with GUI functionality within Oracle Enterprise Manager, Summary Manager was a useful tool for recommending an aggregation strategy for your data warehouse.

Summaries are only part of the story, however, when looking to improve query response time within your Oracle data warehouse. The effective creation of indexes is just as important and with Oracle Database 10g, Oracle has acknowledged this by providing a new tool, the SQL Access Advisor, that now makes recommendations for both materialized views and indexes. Available through the DBMS_ADVISOR PL/SQL package, the SQL Access Advisor can be used to either tune existing materialized views and indexes, or to recommend them for new schemas that need tuning. An important point to note with the SQL Access Advisor is that whilst it is installed by default as part of the 10g database installation, it is actually part of the Enterprise Manager Tuning Pack, which needs to be licensed separately if using in a production environment.

A typical tuning exercise using the DBMS_ADVISOR package would be as follows:

1. Gather a number of SQL statements that will form the tuning workload.

2. Check that the user running DBMS_ADVISOR has the ADVISOR privilege, and has SELECT access to the tables and views referenced by the SQL statements.

3. Define a task using DBMS_ADVISOR.CREATE_TASK, to create a container for your tuning exercise:

:task_id, ?my_first_task?);

Then define a workload object, into which we will later load individual SQL statements:

is my first workload');

Then, link your task and workload objects:


4. Statements can then be manually added into the workload object:

   'my_first_workload', 'MONTHLY', 'ROLLUP', priority=>1, 
executions=>20, -
    username => 'DEMO',  sql_text => 'sELECT SUM(sales) FROM 

Alternatively, they can be loaded in from a table of SQL statements you create, an SQL Tuning Set, an SQL Cache workload, an Oracle9i Summary Advisor workload; otherwise, a hypothetical workload can be generated from a set of table and view definitions in a schema.

5. Generate recommendations for this task's workload:


Each recommendation generated by the SQL Access Advisor can be viewed using catalog views such as USER_ADVISOR_RECOMMENDATIONS. In addition, the procedure GET_TASK_SCRIPT generates an executable SQL script that contains the CREATE, DROP, and ALTER statements to implement the advisor recommendations:

               'ADVISOR_RESULTS', 'script.sql'); 

As an alternative to generating recommendations using the DBMS_ADVISOR package, the SQL Access Advisor functionality is also available as a wizard within the Enterprise Manager 10g Web site.

The SQL Access Advisor Wizard allows you to define tasks, gather workloads and implement recommendations using a graphical interface and is located in the Advisor Central page of the EM Web site.





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.