Running the SQL Tuning Advisor – DBMS_SQLTUNE

I will first provide a conceptual explanation of SQL Tuning Advisor , which is part of the Tuning Pack (CONTROL_MANAGEMENT_PACK_ACCESS=DIAGNOSTIC+TUNING), and then we will perform an example using the resources of its API, the DBMS_SQLTUNE package .

? ? ? ?

SQL Tuning Advisor replaces the manual SQL Tuning process, which can be costly for a DBA or developer. It performs a complete analysis of the SQL statement, consisting of:

– Identify outdated statistics or even their non-existence.

– Determine best execution plans. (SQL Profile)

– Identify better paths and objects that satisfy the possibility of using them (indexes, materialized views).

– Restructuring of instruction.

? ? ? ?

The Advisor can be executed via EM in “Advisor Central” or using the procedures in the DBMS_SQLTUNE package, which will be discussed now. In fact, the “Automatic SQL Tuning” feature, which was introduced in version 10g and which I will discuss in another post, uses the Advisor to perform the tuning.

? ? ? ?

Running the Advisor using DBMS_SQLTUNE has two steps: creating the SQL Tuning task and executing it.

? ? ? ?

After execution, it reports several recommendations for improving the statement, based on the analysis points mentioned above. One of the recommendations is to apply SQL Profile to generate improved execution plans. SQL Profile is associated with the SQL statement signature created through a hash function, which normalizes the statement, capitalizing it and removing extra whitespace before generating the signature.

? ? ? ?

Now let's put DBMS_SQLTUNE into the field!

? ? ? ?

Necessary privileges

? ?

To use the API, the user must be given the ADVISOR privilege.

sqlplus /AS SYSDBA
SQL> GRANT ADVISOR TO TDADBA;        

Creating the SQL Tuning Task

? ?

To create the SQL Tuning task, I will use the information found in the AWR, so I will inform the initial and final snap_id to identify the period in which the SQL was executed.

DECLARE
  l_sql_tune_task_id VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap => 43996,
                          end_snap => 43997,
                          sql_id => '2sk15bdfc6gaf',
                          scope => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit => 1200,
                          task_name => '2sk15bdfc6gaf_AWR_tuning_task',
                          description => 'Tuning task for statement 2sk15bdfc6gaf in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/        

? ?

Note: The scope parameter receives the value SCOPE_COMPREHENSIVE so that SQL Profiles are also generated, if possible.

? ?

Running the SQL Tuning Task

? ?

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2sk15bdfc6gaf_AWR_tuning_task');        

?

Other common commands:

?

-- Interrupting and resuming execution
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task');
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '2sk15bdfc6gaf_AWR_tuning_task');

-- Canceling the SQL tuning task.
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => ' 2sk15bdfc6gaf_AWR_tuning_task'); 

-- Restarting the SQL Tuning Task, allowing it to run again.
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => ' 2sk15bdfc6gaf_AWR_tuning_task ')        

??

Checking SQL Tuning Task Status

? ?

SELECT task_name, status
  FROM dba_advisor_log
 WHERE owner = 'TDADBA';


TASK_NAME STATUS
------------------------------ -----------
2sk15bdfc6gaf_AWR_tuning_task ? ?COMPLETED        

? ?

Viewing recommendations generated by the SQL Tuning Task

? ?

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('2sk15bdfc6gaf_AWR_tuning_task') AS recommendations
  FROM dual;        

? ?

In this case, one of the recommendations is to create the following SQL Profile application: ??

? ?

6- SQL Profile Finding (see explain plans section below)
-------------------------------------------------- ------
 A potentially better execution plan was found for this
 instruction.

Recommendation (estimated benefit: 98.92%)
----------------------------------------------------------------------- 
- Consider accepting the recommended SQL profile.
 run dbms_sqltune.accept_sql_profile(task_name =>
 '5h3s41pv9hxuk_AWR_tuning_task', task_owner => 'SYS', replace =>
 TRUE);        

? ?

Therefore, to apply the generated SQL Profile, the recommended command must be used. And we finish executing the SQL Tuning Advisor using DBMS_SQLTUNE.

? ?

References:

? ?

MOS notes:

Using the DBMS_SQLTUNE package to Run the Sql Tuning Advisor [ID 262687.1]

? ?