Steps to Run SQL Tuning Advisor Against Slow Running Queries in Oracle

April 10, 2019

Tuning slow running query in Oracle is one of the most challenging tasks for the DBA. When you run SQL tuning advisor against sql_id or SQL query, it provides you recommendations to tune long running queries in Oracle. You can execute it against any of the select, insert, update and delete statements.

SQL Tuning Advisor

SQL Tuning Advisor evaluates resource consumption in terms of CPU, I/O, memory and temporary space. The advisor receives one or more SQL statements as input and provides recommendations on how to optimize their execution plans, gives the rationale for the advice, the estimated performance benefit, and the actual command to implement the advice.

Below are the steps to execute the Tuning Advisory

STEP 1: Create tuning task for the specific SQL_ID:

declare
 task_nm varchar2(100);
 begin
 task_nm := dbms_sqltune.create_tuning_task(SQL_ID=> '&SQL_ID',TASK_NAME => 'SQL_TUNNING_TASK_SQL_ID');
 end;
 /
NOTE: Replace sql_id in above statement
STEP 2: Check the status of newly created task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
 TASK_NAME                      STATUS
 
TASK_NAME            INITIAL
STEP 3: Execute the newly created task:

exec dbms_sqltune.execute_tuning_task (TASK_NAME => '&TASK_NAME');
Note: Please replace the task name as mentioned in step 1
STEP 4: Check the status after executing the task:

SQL> SELECT task_name, status FROM dba_advisor_log WHERE task_name = '&TASK_NAME';
 TASK_NAME                      STATUS
 
TASK_NAME      COMPLETED
STEP 5: Execute the Below Query to get the Advisory Report:

SQL>SET LONG 10000;
 SQL>SET PAGESIZE 1000
 SQL>SET LINESIZE 200SQL> SQL>
 SQL> SELECT DBMS_SQLTUNE.report_tuning_task('&TASK_NAME') AS recommendations FROM dual;
 SQL>SET PAGESIZE 24

NOTE: Replace task name in above query
STEP 6: To Drop the Tuning Task:

execute dbms_sqltune.drop_tuning_task('&TASK_NAME');

To execute tuning advisory using AWR snap ID if sql_id is not present in cursor:

STEP 7: Find the snap ID using below query:
SQL> SELECT SQL_ID, PLAN_HASH_VALUE, TIMESTAMP FROM DBA_HIST_SQL_PLAN WHERE SQL_ID='&SQL_ID';
 SQL> select snap_id,sql_id,plan_hash_value from dba_hist_sqlstat where sql_id='&SQL_ID' and plan_hash_value='&plan_hash_value' order by snap_id desc;
STEP 8: Create Tuning Task:

DECLARE
   l_sql_tune_task_id  VARCHAR2(100);
 BEGIN
   l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                           begin_snap  => 345,
                           end_snap    => 349,
                           sql_id      => '43qef4f7g5h32',
                           scope       => DBMS_SQLTUNE.scope_comprehensive,
                           time_limit  => 60,
                           task_name   => '43qef4f7g5h32_AWR_tuning_task',
                           description => 'Tuning task for statement 43qef4f7g5h32  in AWR');
   DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
 END;
 /

--NOTE: Replace the above highlighted values:
STEP 9: After creation of tuning task you can follow the above steps from 2-9.

After getting the sql tuning advisory output, please follow the recommendations to tune the slow running queries. Advisory may recommend you to gather object statistics, accept profiles for better plan, create indexes etc. You can also see the performance benefits in the advisory output. Manually you can also compare the best advisory plan and tune the query.

Must Read: