-----------------------------------------------------------
--        name  sqltunad
--     version  0.0
-- description  This script contains the steps for 
--              running the SQL Tuning Advisor.
--    synopsis  Takes one argument, the SQL ID.
--       usage  
--    comments  You are supposed to know the SQL ID
--              before you run this. You can get that
--              from V$SQL, V$SQLAREA, and such. 
--              The first line of this script displays
--              the SQL text for the SQL ID passed in
--              as an argument.
--   copyright  Copyright David Clement 2006. 
--              All rights reserved.
--    creation  25 May 2006
--      author  David Clement
--     changes
----------------------------------------------------------
set long 10000
set pages 1000
set lines 256
set serveroutput on size 1000000
/

select sql_text from v$sql where sql_id = '&&1'
/

declare 
    sql_tuning_task_id varchar2 (100);
begin
    sql_tuning_task_id := dbms_sqltune.create_tuning_task (
        sql_id      => '&&1',
        scope       => dbms_sqltune.scope_comprehensive,
        time_limit  => 60,
        task_name   => '&&1._tuning',
        description => 'Tuning &&1');
    dbms_output.put_line ('sql_tuning_task_id: ' 
        || sql_tuning_task_id);
end;
/

exec dbms_sqltune.execute_tuning_task(task_name => '&&1._tuning');
/

spool &&1._tuning

select dbms_sqltune.report_tuning_task ('&&1._tuning')
as recommendations
from dual
/

spool off