How to execute sql tuning advisor for SQL ID in 19c

 

Description: - SQL Tuning Advisor is a program provided by sql that comes along with oracle installation. This advisor can be executed against a sql id and it generates a report with recommendations which generally includes

·       Collection of object statistics

·       Creation of indexes

·       Rewriting SQL statements

·       Creation of SQL profiles

·       Creation of SQL plan baselines

 

The objective to use this program is to avoid tedious manual tuning which DBA’s generally do to fix a bad query.

Note:-As mentioned earlier, this advisor comes along with default  oracle installation but It requires Diagnostic and Tuning packs to use the SQL Tuning Advisor.  

 

Let me demonstrate how to execute SQL tuning advisor against a SQL id

 

Step 1.

To run the advisor we need to create tuning task ,

But before that , we need to have the sql id against which we would run the advisor.

For e.g. let us assume the sql id is – 8d5cry63fwerg65

SET serveroutput ON DECLARE   l_sql_tune_task_id  VARCHAR2(100); BEGIN   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (                           sql_id      => '&&my_sql_id',                           scope       => DBMS_SQLTUNE.scope_comprehensive,                           time_limit  => 120000,                           task_name   => 'sql_tuning_task_&&my_sql_id',                           description => 'Tuning task for statement &&my_sql_id.');   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); END; /

 

Enter value for my_sql_id: 8d5cry63fwerg65

 

Step 2.

Check the task status.

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';   TASK_NAME                                      STATUS ------------------------------             ----------- sql_tuning_task_8d5cry63fwerg65    INITIAL

 

 

Step 3.

Execute the sql tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'sql_tuning_task_&&my_sql_id');

 

Step 4.

Check the task status again

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';   TASK_NAME                                                 STATUS ------------------------------                              ----------- sql_tuning_task_8d5cry63fwerg65     COMPLETED

 

Step 5.

Review the recommendations by SQL Tuning Advisor

SET LINES 222 SET pages 30000 SET long 999999 SET longc 99999999   SELECT DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS recommendations FROM dual;

 

 

Step 5.

Implement the Recommendations only if you find the recommendations are satisfactory.

execute dbms_sqltune.accept_sql_profile(task_name => ‘sql_tuning_task_8d5cry63fwerg65', task_owner => 'SYS', replace => TRUE);

 

 

Stpe 6.

To drop the tuning task

BEGIN   DBMS_SQLTUNE.drop_tuning_task (task_name => 'sql_tuning_task_&&my_sql_id'); END; /

 

Step 7.

Verify the task status

SELECT task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE 'sql_tuning_task_&&my_sql_id';