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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

VARIABLE task_id NUMBER;
EXECUTE DBMS_ADVISOR.CREATE_TASK ('SQL Access Advisor',
:task_id, ?my_first_task?);

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

EXECUTE DBMS_ADVISOR.CREATE_SQLWKLD(?my_first_workload?,'This
is my first workload');

Then, link your task and workload objects:

EXECUTE DBMS_ADVISOR.ADD_SQLWKLD_REF('my_first_task',
'my_first_workload');

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

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

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:

EXECUTE DBMS_ADVISOR.EXECUTE_TASK('my_first_task');

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:

EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT('my_first_task'), - 
               '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 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.