-- tune.sql
-- William Robertson 2007, 2021 www.williamrobertson.net
-- Inspired by Adrian Billington's "A brief introduction to Oracle's SQL Tuning Advisor"
-- http://www.oracle-developer.net/display.php?id=317
--
-- Usage: @tune
-- No command-line parameters. Works on current SQL*Plus buffer. e.g:
--
-- SQL> SELECT blah FROM blah WHERE blah
-- SQL>
-- SQL> @tune
-- 
-- DBMS_SQLTUNE tuning task procedures require ADVISOR system privilege.
-- http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_sqltun.htm#ARPLS68383

set term off
store set sqlplus_settings.sql replace
set serverout on size 200000
set autotrace off
set autoprint off
set long 50000
set linesize 170
set longchunksize 170
col recommendations format a120 word

var sqltext clob
var recommendations clob

exec dbms_lob.createtemporary(:sqltext, true)
exec dbms_lob.createtemporary(:recommendations, true)

save tune.buf repl
0 begin :sqltext := trim(chr(10) from q'[
l
i ]');;
i end;;
r

declare
    k_taskname constant varchar2(50) := user || to_char(sysdate,'YYYYMMDDHH24MI');
    v_task varchar2(30);
    v_priv_check integer := 0;
begin
    select count(*) into v_priv_check
    from    user_sys_privs p
    where  p.privilege in ('ADVISOR','OEM_ADVISOR');

    if v_priv_check > 0 then
        -- Drop the task in case we are re-running:
        declare
            no_advisor_privs      exception; pragma exception_init(no_advisor_privs, -13616);
            no_such_tuning_task exception; pragma exception_init(no_such_tuning_task, -13605);
        begin
            dbms_sqltune.drop_tuning_task(k_taskname);
        exception
            when no_advisor_privs then dbms_output.put_line('The current user has not been granted the ADVISOR privilege.');
            when no_such_tuning_task then null;
        end;

        select count(*) into v_priv_check
        from    user_sys_privs p
        where  p.privilege in ('ADVISOR','OEM_ADVISOR');

        if v_priv_check > 0 then
            -- create a sql tuning task for our sql...
            v_task := dbms_sqltune.create_tuning_task
            ( sql_text => :sqltext
            , time_limit => 30
            , scope => 'COMPREHENSIVE'
            , task_name => k_taskname
            , description => 'Demo of DBMS_SQLTUNE' );

            dbms_sqltune.execute_tuning_task(k_taskname);

            :recommendations := dbms_sqltune.report_tuning_task(k_taskname);
        else
            :recommendations := 'ADVISOR or OEM_ADVISOR system privilege is required to use the DBMS_SQLTUNE tuning task procedures.';
        end if;
    end if;
end;
/

set term on
set feed off

select :recommendations "Recommendations:" from dual;

begin
      dbms_lob.freetemporary(:sqltext);
      dbms_lob.freetemporary(:recommendations);
end;
/

@sqlplus_settings.sql
get tune.buf nolist
set term on