ORACLEdbms_sqltune

As usually, we do some sql tuning, and we also can do it with the help of oracle dbms_tunne which is the oracle sql tuning advisor.

After oracle 10g, oracle provide one function for sql tuning automatically named dbms_sqltune.

Where can it be used

We often use it with oracle enterprise manager, grid control, addmrpt or manually do it.

For manually as:

DECLARE
  SQLTUNING_TASK VARCHAR2(30);
  SQL_TEXT CLOB;
BEGIN
  SQL_TEXT := 'select * from t1 where object_id = 2';
  SQLTUNING_TASK := DBMS_SQLTUNE.CREATE_TUNING_TASK(

    SQL_TEXT => SQL_TEXT,
    USER_NAME => 'WINKEY',
    SCOPE => 'COMPREHENSIVE',
    TIME_LIMIT => 15,
    TASK_NAME => 'my_task',
    DESCRIPTION => 'winkey'
    );
END;
/

SELECT TASK_NAME FROM DBA_ADVISOR_LOG;   --sqladvisor log

--execute sql tuing task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( TASK_NAME => 'my_task');
END;
/

--checking for sql tuing task
SELECT status
FROM   USER_ADVISOR_TASKS
WHERE  TASK_NAME = 'my_task';

-- Checking the Progress of SQL Tuning Advisor
SELECT SOFAR, TOTALWORK
FROM   V$ADVISOR_PROGRESS
WHERE  USER_NAME = 'WINKEY' AND TASK_NAME = 'my_task';

SET LONG 2000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_task')
FROM   DUAL;