Script to execute SQL Tuning Advisor manually

 

The below script can be used to run SQL tuning advisor manually.

CREATE TUNING TASK

set time on ;
set timing on ;

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;
/

EXECUTE TUNING TASK

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

REPORT TUNING TASK

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;