SQL Tuning Task using the Oracle DBMS_SQLTUNE package

 SQL Tuning Task using the Oracle DBMS_SQLTUNE package

-- Set environment formatting for SQL*Plus

SET PAGES 1000

SET LINES 1000

SET LONG 600000                -- Allow large output for tuning report

SET PAGESIZE 9999

SET LINESIZE 155

SET VERIFY OFF                 -- Suppress variable substitution display

-- Define column format for the output

COLUMN recommendations FOR a150

-- Accept input from user for tuning task name

ACCEPT task_name -

       PROMPT 'Task_Name: '

-- PL/SQL block to create and execute the SQL tuning task

DECLARE

    ret_val VARCHAR2(4000);

BEGIN

    -- Create a tuning task for the specified SQL_ID and time limit

    ret_val := DBMS_SQLTUNE.create_tuning_task(

                  task_name    => '&&Task_name',      -- Task name passed by user

                  sql_id       => '&sql_id',          -- SQL ID to be tuned

                  time_limit   => &time_limit         -- Time limit for tuning in seconds

               );

    -- Execute the tuning task

    DBMS_SQLTUNE.execute_tuning_task('&&Task_name');

END;

/

-- Display tuning recommendations

SELECT DBMS_SQLTUNE.report_tuning_task('&&task_name') AS recommendations 

FROM dual;

-- Clear substitution variable

UNDEF task_name