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.

DECLARE
my_sqltext CLOB;
task_name VARCHAR2(30);
BEGIN
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',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task1');
END;
/


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
SET PAGESIZE 200
SET LINESIZE 200
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';