SQL Tuning through SQL Profiling.

 Suppose the sql id is – 0hky681atzrfn

1. Create Tuning Task

DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '7ujfmgb1j83a3',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '7ujfmgb1j83a3_tuning_task_v1',

description => 'Tuning task1 for statement 7ujfmgb1j83a3');

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

END;

/

If SQL is NOT in Cursor Cache

What if the sql_id is not present in the cursor , but present in AWR snap?

SQL_ID =24pzs2d6a6b13

First we need to find the begin snap and end snap of the sql_id.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,

executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b

where sql_id='&sql_id' and a.snap_id=b.snap_id

and a.instance_number=b.instance_number

order by snap_id desc, a.instance_number;

From here we can get the begin snap and end snap of the sql_id.

begin_snap -> 235

end_snap -> 240

1. Create the tuning task:

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 235,

                          end_snap    => 240,

                          sql_id      => '24pzs2d6a6b13',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => '24pzs2d6a6b13_AWR_tuning_task',

                          description => 'Tuning task for statement 24pzs2d6a6b13  in AWR');

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

END;

/

To execute a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.EXECUTE_TUNING_TASK function.

For example, execute the following PL/SQL program:

BEGIN

  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'7ujfmgb1j83a3_tuning_task_v1');

END;

/

Optionally, query the status of the task.

The following example queries the status of all tasks owned by the current user, which in this example is hr:

COL TASK_ID FORMAT 999999

COL TASK_NAME FORMAT a25

COL STATUS_MESSAGE FORMAT a33

SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM   USER_ADVISOR_LOG ORDER BY 1;  -- DBA_ADVISOR_LOG;

Sample output appears below:

TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE

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

    884 7ujfmgb1j83a3_tuning_task_v1     COMPLETED

To view the report for a SQL tuning task:

Connect SQL*Plus to the database with the appropriate privileges, and then run the DBMS_SQLTUNE.REPORT_TUNING_TASK function.

For example, you run the following statements:

SET LONG 1000000000

SET LONGCHUNKSIZE 100000000

SET LINESIZE 100

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('7ujfmgb1j83a3_tuning_task_v1' )

FROM   DUAL;

To implement a SQL profile:

Connect SQL*Plus to the database with the appropriate privileges, and then execute the ACCEPT_SQL_PROFILE function.

For example, execute the following PL/SQL:

DECLARE

  my_sqlprofile_name VARCHAR2(30);

BEGIN

  my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 

    task_name    => '7ujfmgb1j83a3_tuning_task_v1'  -- Modify **

,   name         => 'my_sql_profile' -- Modify **

,   profile_type => DBMS_SQLTUNE.REGULAR_PROFILE    -- PX_PROFILE  Modify only if requried to enable PX query

,   force_match  => true 

);

END;

/