SQL Advisor

1.Creating Task Name:
---------------------

 SQL> SET SERVEROUTPUT ON
declare
stmt_task VARCHAR2(100);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'dfaw5z40nt5ww',time_limit  => 3600);
DBMS_OUTPUT.put_line('task_id: ' || stmt_task );
end;
/SQL>   2    3    4    5    6    7
task_id: TASK_7019

PL/SQL procedure successfully completed.


2.Tuning the Task:
------------------

SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'TASK_174021');

PL/SQL procedure successfully completed.


3.Generating the sql tuned report:
----------------------------------

SET linesize 200
SET LONG 999999999
SET pages 1000
SET longchunksize 20000
SELECT dbms_sqltune.report_tuning_task('TASK_174021', 'TEXT', 'ALL') FROM dual;