-- tune.sql
-- William Robertson 2007, 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_ADVISOR requires ADVISOR system privilege.

var sqltext CLOB
var recommendations CLOB

set term off
store set sqlplus_settings.sql replace
set autoprint off
set long 50000
set longchunksize 111
col recommendations format a111 word

save tune.buf repl
0 BEGIN :sqltext := q'^
l
i ^';;
i END;;
r

DECLARE
    k_taskname CONSTANT VARCHAR2(20) := USER || '_tuning_task';
    v_task VARCHAR2(30);
    v_priv_check INTEGER := 0;
BEGIN
    -- Drop the task in case we are re-running:
    DECLARE
        no_such_tuning_task EXCEPTION; PRAGMA EXCEPTION_INIT(no_such_tuning_task, -13605);
    BEGIN
        DBMS_SQLTUNE.DROP_TUNING_TASK(k_taskname);
    EXCEPTION
        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 => 10
        , 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;
/

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