SQL TUNING ADVISOR :-
o 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.
o 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.
o You can also run the SQL Tuning Advisor selectively on a single or a set of
SQL statements that have been identified as problematic.
o 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 :-
Check the PLAN_HASH_VALUE got changed for the specific statement and
get SNAP_ID to create a tuning task.
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,Execute and Report the task from given AWR snapshot IDs.
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;
Interrupt Tuning task :
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name =>
'483wz173punyb_tuning_task');
Resume Tuning task :
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name =>
'483wz173punyb_tuning_task');
Cancel Tuning task :
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name =>
'483wz173punyb_tuning_task');
Reset Tuning task :
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name =>
'483wz173punyb_tuning_task');
