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