Oracle: To Tune a script

  

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 6996,

                          end_snap    => 6997,

                          sql_id      => '6zcykn22gxuhp',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => '6zcykn22gxuhp_tuning_task',

                          description => 'Tuning task for statement 6zcykn22gxuhp');

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

END;

/

 

 

And then execute this task:

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '6zcykn22gxuhp_tuning_task');

 

And get the tuning tsk recommendation report:

SET LONG 10000000;

SET PAGESIZE 10000

 

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('6zcykn22gxuhp_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24