Run SQL Query as parameter in SQL Tuning Advisory DBMS_SQLTUNE package in Oracle

SQL Tuning Advisory with direct SQL Query in Oracle

We have Query which we want to run the SQL Advisory directly on Query.

Following are the steps to run query directly in SQL Advisory

1. Create a SQL tuning task defining SQL which need to be tuned
You need to enter the query and username of objects present.

my_sqltext CLOB;
task_name VARCHAR2(30);
my_sqltext := 'SELECT name,job_title FROM EMP';
my_sqltext := my_sqltext || ' where emp_no=100';
task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sql_text => my_sqltext,
bind_list => sql_binds(anydata.ConvertNumber(100)),
user_name => 'HR',
time_limit => 60,
task_name => 'sql_tuning_task1');

2. Execute the SQL tuning task

exec dbms_sqltune.execute_tuning_task ( 'sql_tuning_task1');

3. Fetch the Advisory report of SQL tuning

SET LONG 100000
SELECT DBMS_SQLTUNE.report_tuning_task('SQL_TUNING_TASK1') AS recommendations FROM dual;

4. Drop the task name if everything done or not needed more

exec DBMS_SQLTUNE.drop_tuning_task (task_name => 'SQL_TUNING_TASK1');

5. Check if any tuning task already present

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

Leave a Reply

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

You are commenting using your 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.