How to create a SQL Tuning Task to optimize a specified SQL statement text

Creating a SQL Tuning Task

You can use SQL Tuning Advisor to optimize a specified SQL statement text, for that way you need to create a tuning task with the SQL statement passed as a CLOB argument. For the following PL/SQL code, the user USERADV has been granted the ADVISOR privilege and the function is run as user USERADV on the employees table in the USERADV schema.

DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sql_text := 'SELECT /*+ ORDERED */ * '                      ||
                'FROM employees e, locations l, departments d ' ||
                'WHERE e.department_id = d.department_id AND '  ||
                     'l.location_id = d.location_id AND '      ||
                     'e.employee_id < :bnd';
 
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sql_text,
         bind_list   => sql_binds(anydata.ConvertNumber(100)),
         user_name   => 'USERADV',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'my_sql_tuning_task01',
         description => 'Task to use for tuning a query');
END;

In this example, 100 is the value for bind variable :bnd passed as function argument of type SQL_BINDS, USERADV is the user under which the CREATE_TUNING_TASK function analyzes the SQL statement, the scope is set to COMPREHENSIVE which means that the advisor also performs SQL Profiling analysis, and 60 is the maximum time in seconds that the function can run. In addition, values for task name and description are provided.
The CREATE_TUNING_TASK function returns the task name that you have provided or generates a unique task name. To view the task names associated with a specific owner, you can run the following:

SELECT task_name FROM DBA_ADVISOR_LOG WHERE owner = 'USERADV';

Executing a SQL Tuning Task

After you have created a tuning task, you need to execute the task and start the tuning process. For example:

BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task01' );
END;

Checking the Status of a SQL Tuning Task

You can check the status of the task by reviewing the information in the USER_ADVISOR_TASKS view or check execution progress of the task in the V$SESSION_LONGOPS view. For example:

SELECT STATUS FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task01';

Checking the Progress of the SQL Tuning Advisor

You can check the execution progress of the SQL Tuning Advisor in the V$ADVISOR_PROGRESS view. For example:

SELECT sofar, totalwork FROM V$ADVISOR_PROGRESS WHERE user_name = 'USERADV' AND task_name = 'my_sql_tuning_task01';

Displaying the Results of a SQL Tuning Task

After a task has been executed, you display a report of the results with the REPORT_TUNING_TASK function. For example:

SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task01')
  FROM DUAL;

The report contains all the findings and recommendations of Automatic SQL Tuning. For each proposed recommendation, the rationale and benefit is provided along with the SQL commands needed to implement the recommendation.

Additional Operations on a SQL Tuning Task

You can use the following APIs for managing SQL tuning tasks:

  • INTERRUPT_TUNING_TASK to interrupt a task while executing, causing a normal exit with intermediate results
  • RESUME_TUNING_TASK to resume a previously interrupted task
  • CANCEL_TUNING_TASK to cancel a task while executing, removing all results from the task
  • RESET_TUNING_TASK to reset a task while executing, removing all results from the task and returning the task to its initial state
  • DROP_TUNING_TASK to drop a task, removing all results associated with the task