SQL tuning advisory (DBMS SQLTUNE package) in Oracle

SQL tuning advisory (DBMS SQLTUNE Package) for the Oracle SQL Query

SQL tuning advisory is the package available in the oracle for tune the SQL Query. It will provide the suggestion regarding the SQL Query to improve its performance by gathering stats, creating index and change execution plan.

1. Create the SQL tuning advisory Task
Put the SQL text in the below package for which you want to run the SQL advisory:

set serveroutput on
l_sql_tune_task_id varchar2(100);
my_sqltext   CLOB;
my_sqltext := 'SELECT TIMESERIEDATA.TIMESERIE_ID, max(TIMESERIEDATA.TOTIME) from EREM.TIMESERIEDATA where timeseriedata.timeserie_id in (554083833,554083832,554083831,554083830,548292904,480213784) group by TIMESERIEDATA.TIMESERIE_ID';
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
sql_text    => my_sqltext,
scope => dbms_sqltune.scope_comprehensive,
time_limit => 60,
task_name => 'ADVISORY_FRDZ',
description => 'tuning task for statement your_sql_id.');
dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

Note: If you have SQL id of the SQL then you can use the following script to create the SQL Advisory Task. You get the sql_id from V$sqlarea view.

l_sql_tune_task_id VARCHAR2(100);
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
sql_id => '9babjv8yq8ru3',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 60,
task_name => 'ADVISORY_FRDZ',
description => 'Tuning task for statement 19v5guvsgcd1v.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

2. Execute the SQL tuning Advisory task

exec dbms_sqltune.execute_tuning_task(task_name => 'ADVISORY_FRDZ');

Get the Report of SQL tuning Advisory task

set longchunksize 1000
set pagesize 10000
set linesize 100
select dbms_sqltune.report_tuning_task('ADVISORY_FRDZ') as recommendations from dual;

3. Check the SQL tuning Advisory

SELECT task_name, status FROM dba_advisor_log WHERe task_name = 'ADVISORY_FRDZ';

4. Drop the SQL tuning advisory task

EXECUTE dbms_sqltune.drop_tuning_task('ADVISORY_FRDZ');

As a result of SQL tuning Advisory you got following suggestions:
1. More appropriate execution plan for SQL Query.
2. Create Index on the tables.
3. Create stats on the table.

Example: For execution plan in Accept State
Note: If advisory is suggest for creating the different execution plan in accept state which will increase the performance as example given below:

1. Created the accept SQL Profile as mentioned:

Recommendation (estimated benefit: 96.53%)
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SQL_TUNING_TASK1',
task_owner => 'SYS', replace => TRUE);

If you created the plan as you got in advisory reports, its work for same parameters as mentioned in SQL query tuning advisory. It is possible that it will not work with all other different parameters. It depends upon you application is using bind variable so that it use same plan for all parameters.

2. If it not help for all parameters, still having performance problem:
Note: Name parameter available at end of the execution plan for the query when you fetch it again.

exec DBMS_SQLTUNE.drop_sql_profile(name=>'SYS019237763467408',ignore => TRUE);


1 thought on “SQL tuning advisory (DBMS SQLTUNE package) in Oracle

  1. Pingback: Manage the SQL Profile in the Oracle Database | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.