This excerpt, taken from Easy Oracle Automation: Oracle10g Automatic Storage, Memory and Diagnostic Features by Dr. Arun Kumar R, describes the process of using SQL Access Advisor for tuning SQL. Click here to view the entire chapter.
SQL Tuning with SQL Access Advisor
To use the SQL Access Advisor for tuning, perform the following four steps:
- Create a task.
- Define the workload.
- Generate the recommendations.
- Implement the recommendations.
Task Operations
A task has all the information relating to the recommendation process and its results. To automatically create a task, use the wizard in OEM or dbms_advisor.quick_tune. After creating a task, the DBA has to execute the task in preparation for the tuning process:
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'dba_tuning_task_v1' );
END;
/
For the Senior DBA or those who prefer the hands-on approach, use the dbms_advisor.create_task procedure. To view the tasks associated with any specific user or application schema, use the following command:
SELECT TASK_NAME FROM DBA_ADVISOR_LOG WHERE OWNER = 'owner' ;
Similarly, the dba_advisor_log can be used to find the status of a tuning task:
SELECT TASK_NAME FROM DBA_ADVISOR_LOG WHERE task_name = 'dba_tuning_task_v1' ;
The execution progress can be checked with the v$session_longops view, which gives the status of various operations that run longer than six seconds (in absolute time).
To view the results of the task execution, use the report_tuning_task_function:
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'dba_tuning_task_v1') FROM DUAL;
This report contains all the results and recommendations of the automatic SQL tuning. For each proposed recommendation, the rationale and advantages can be found, along with the SQL commands needed to implement the changes.
There are other useful APIs to be used along with the dbms_sqltune package for managing SQL tuning tasks. They are as follows:
- drop_tuning_task – used to drop a task and remove all associated results.
- reset_tuning_task – used to reset a task during execution to its initial state.
- interrupt_tuning_task – used to interrupt a task and exit with intermediate results.
- cancel_tuning_task – used to cancel a task execution and remove all results.
Next, the use of workload with SQL Access Advisor will be examined.
Click here to view the entire chapter.
