The SQLAccess Advisor

DBMS_ADVISOR :-

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');