SQLTuning Advisor (Manually)

Generate addm/ash/awr see report.

Step 0) In order to execute Sql Tuning Adviser API, one must be granted with “ADVISER” role.

grant adviser to <user>;

Step 1) The first step using SQL Tuning Adviser is to create a tuning task using DBMS_SQLTUNE.CREATE_TUNING_TASK.

— for a specific statement from AWR

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 764,

                          end_snap    => 938,

                          sql_id      => '19v5guvsgcd1v',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => '19v5guvsgcd1v_AWR_tuning_task',

                          description => 'Tuning task for statement 19v5guvsgcd1v in AWR.');

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

END;

Step 2) — or for a specific statement from Shared Library Cache

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          sql_id      => 'fhahkc71k304u',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'fhahkc71k304u_tuning_task',

                          description => 'Tuning task for statement fhahkc71k304u.');

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

END;

/

Step 3) — or for a specific statement given manually

During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be re-executed:

Interrupt and resume a tuning task.

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');

EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');

-- Cancel a tuning task.

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');

-- Reset a tuning task allowing it to be re-executed.

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '19v5guvsgcd1v_AWR_tuning_task');

——————————————————————————————
Step 4 create a tuning task from AWR
——————————————————————————————
get snap ids of today

sys@goldprod> select SNAP_ID, BEGIN_INTERVAL_TIME from dba_hist_snapshot where BEGIN_INTERVAL_TIME > trunc(sysdate) order by snap_id;

   SNAP_ID BEGIN_INTERVAL_TIME

---------- ---------------------------------------------------------------------------

      4042 28-APR-11 12.00.29.410 AM

      4043 28-APR-11 01.01.01.094 AM

      4044 28-APR-11 02.00.32.909 AM

      4045 28-APR-11 03.00.07.558 AM

      4046 28-APR-11 04.00.40.121 AM

      4047 28-APR-11 05.00.14.894 AM

      4048 28-APR-11 06.00.59.123 AM

      4049 28-APR-11 07.00.23.056 AM

      4050 28-APR-11 08.00.51.205 AM

      4051 28-APR-11 09.00.19.892 AM

      4052 28-APR-11 10.00.35.227 AM

      4053 28-APR-11 11.00.02.168 AM

      4054 28-APR-11 12.00.37.690 PM

      4055 28-APR-11 01.00.09.106 PM

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 4042,

                          end_snap    => 4055,

                          sql_id      => 'fhahkc71k304u',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => 'fhahkc71k304u_AWR_tuning_task',

                          description => 'Tuning task for statement fhahkc71k304u in AWR.');

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

END;

/

PL/SQL procedure successfully completed.

Step 5) execute the tuning task

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'fhahkc71k304u_AWR_tuning_task');

Step 6) report tuning task findings

SET LONG 100000;

SET PAGESIZE 1000

SET LINESIZE 300

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

SET PAGESIZE 24

Step 7) accept recommendations

execute dbms_sqltune.accept_sql_profile(task_name =>'fhahkc71k304u_AWR_tuning_task', replace => TRUE);

Step 8) verify if the sql profile is used.