How to run SQL Tuning Advisor?

 

1)   Identify the problematic SQL_ID

 

Ex. 57vf9jgszq0yp –In our case, this is problematic SQL_ID

 

  

2)   CREATE_TUNING_TASK Functions creates a tuning task

 

set serveroutput on

declare

  l_sql_tune_task_id  varchar2(100);

begin

  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

                          sql_id      => '57vf9jgszq0yp',

                          scope       => dbms_sqltune.scope_comprehensive,

                          time_limit  => 7200,

                          task_name   => 'Tuning_task_57vf9jgszq0yp',

                          description => 'tuning task for statement sql_id_57vf9jgszq0yp');

  dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/

 

3)   Execute tuning task

exec dbms_sqltune.execute_tuning_task(task_name => 'Tuning_task_57vf9jgszq0yp');

 

 

4)   Take report

spool Tuning_task_57vf9jgszq0yp_log1.txt

set long 100000;

set longchunksize 1000

set pagesize 10000

set linesize 400

select dbms_sqltune.report_tuning_task('Tuning_task_57vf9jgszq0yp') as recommendations from dual;

spool off;