The SQLAccess Advisor

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.