How to run sql tuning advisor for a sql _id

Many times a sql may be running long and we may need to run sql tuning advisor to get the recommendation to resolve the long running issue.In this post, we would be looking at the steps to achieve it

Steps for running  sql tuning  advisor on the particular sql -id in the Cursor cache

Create sql Tuning Task

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK
(sql_id => '<SQL_ID>',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => '<task Name>',
description => 'Tuning Task for <SQL-ID>');
end;
/

Example
For SQL_ID=454nj462ha3n
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '454nj462ha3n',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => '454nj462ha3n_task_1',
description => 'Tune query using sql_id 454nj462ha3n');
end;
/

The scope parameter above is one of the most important for this function. You can set this parameter to the following values:
LIMITED
SQL Tuning Advisor produces recommendations based on statistical checks, access path analysis, and SQL structure analysis. SQL profile recommendations are not generated.
COMPREHENSIVE
SQL Tuning Advisor carries out all the analysis it performs under limited scope plus SQL profiling.

The below query list all the tuning task in the database

COL TASK_ID FORMAT 999999
COL TASK_NAME FORMAT a25
COL STATUS_MESSAGE FORMAT a33
SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM DBA_ADVISOR_LOG;

Execute sql Tuning Task

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '<Task Name>');
end;
/
Example
BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '454nj462ha3n_task_1');
end;
/

You can monitor the processing of the tuning task with the statement

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME = ‘454nj462ha3n_task_1’;

Once the tuning task is completed, we can generate the report

Get the summary of recommendation of sql tuning task

SET LONG 10000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '<TASK NAME>') from DUAL;

Example
SET LONG 10000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '454nj462ha3n_task_1') from DUAL;

Get the detailed recommendation of sql tuning task

SET LONG 10000000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
set pagesize 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( '454nj462ha3n_task_1','TEXT','ALL','ALL') from DUAL;

How to accept the profile recommended by the sql tuning expert

execute dbms_sqltune.accept_sql_profile(task_name =>'454nj462ha3n_task_1', task_owner => 'SYS', replace => TRUE);

This will provide system generated name for the SQL Profile. If you want to give some name, then you can use below sql

DECLARE
my_sqlprofile_id VARCHAR2(30);
BEGIN
my_sqlprofile_id := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
task_name => '454nj462ha3n_task_1',
name => '454nj462ha3n_profile');
END;
/

How to drop the sql tuning task.

begin
DBMS_SQLTUNE.DROP_TUNING_TASK('454nj462ha3n_task_1');
end;
/

Steps for running  sql tuning  advisor on the particular sql -id not in the Cursor cache

If the sql_id is not in the Cursor Cache, we can locate into the AWR tables and then run the sql tuning tasks

set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id','<SQL_ID>')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

Once we get to know the Snap_id , we can run the sql tuning advisor as below

SET SERVEROUTPUT ON
DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => <begin_Snap_id>,
end_snap => <end_Snap_id>,
sql_id => '<SQL_ID>',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 1800,
task_name => '<SQL_ID>_AWR_task',
description => 'Tuning task for statement <SQL_ID> in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Then normal steps can be executed as same as above

Related Articles

Oracle Explain Plan : This is about Explain Plan in Oracle, How to read oracle explain plan for performance related issue, how to find the explain plan for query in cursor
Oracle Performance Tuning : Listing of Oracle performance tuning articles which will help you in solving the problems quickly
Sql Trace in Oracle: SQL trace or 10046 event is being used to trace the session activity in Oracle. The output generated by SQL trace is formatted using trcsess, tkprof utility
Oracle joins with examples : What are Oracle Joins with examples (Sql joins), Different type of joins , Inner Join,Outer join,left outer join,right outer join,cross join with examples
Hash join in Oracle :Check out this post for the detailed description of Hash join in Oracle, How it is different from Nested Loop join in oracle
v$active_session_history :Check out about Active Session History ,how it is configured,how to find performance bottleneck using ASH, ASH report generation,ASH queries
how to check sql profile in oracle :Check out this post on how to check sql profile in oracle, how to find the content of sql profile, how to drop the sql profile
https://docs.oracle.com/database/121/TGSQL/tgsql_sqltune.htm