Oracle Tuning-Analyze SQL with SQL Tuning Advisor

SQL TUNING ADVISOR :-

• The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.
• The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
• You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
• Find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

sqlplus / as sysdba
GRANT ADVISOR TO HARI;
CONN HARI/hari;

Steps to tune the problematic SQL_ID using SQL TUNING ADVISOR :-

Create Tuning Task :

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '43x11xxhxy1j7',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning task :

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

Monitor the task executing using below query:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ='my_sql_tuning_task_1';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task_1           COMPLETED

Check the status is completed for the task and we can get recommendations of the advisor.

Report Tuning task :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1')

-----------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------

Tuning Task Name                  : my_sql_tuning_task_1

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 11/10/2018 19:47:27

Completed at                      : 11/10/2018 19:47:54
--------------------------------------------------------------------
SQL_ID : 43x11xxhxy1j7
SQL_staement : SELECT * FROM HARI.EMP

Number of SQL Profile Findings    : 1
--------------------------------------------------------------------
FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)

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

  - Consider accepting the recommended SQL profile.

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

To get detailed information :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

Drop SQL Tuning task :

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_sql_tuning_task_1');
END;
/

Another method for adding new task using SQL TUNING ADVISOR :-

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')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and executions_delta > 0
order by 1, 2, 3
/

Enter value for sql_id: 483wz173punyb

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------
15694 1 10-NOV-18 01.00.04.047 AM 483wz173punyb 2391860790 1 4,586.818 33,924,912.0
15695 1 10-NOV-18 02.00.18.928 AM 483wz173punyb 2 1,488.867 0,064,449.0
15696 1 10-NOV-18 03.00.03.192 AM 483wz173punyb 2 1,053.459 8,780,977.0

Create a tuning task for the specific statement from AWR snapshots:-

Create Task,

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1868,
end_snap => 1894,
sql_id => '483wz173punyb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => '483wz173punyb_tuning_task',
description => 'Tuning task for statement 483wz173punyb in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute Task,

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '483wz173punyb_tuning_task');

Report task,

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('483wz173punyb_tuning_task') AS recommendations FROM dual;

EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '483wz173punyb_tuning_task');

EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '483wz173punyb_tuning_task');

EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '483wz173punyb_tuning_task');

EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '483wz173punyb_tuning_task');

Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba