Using Oracle SQL Tuning Advisor

Oracle 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.

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:

Also See:

Automatic SQL Tuning Advisor

ORAchk