Chapter Download

SQL Tuning with SQL Access Advisor

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:

  1. Create a task.
  2. Define the workload.
  3. Generate the recommendations.
  4. 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:

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK (task_name => 'dba_tuning_task_v1' );

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:


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:


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.

This was first published in June 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: