SQL Tuning made easy in Oracle 10g

“Find out how to use DBMS_SQLTUNE package to tune SQL Statements in Oracle 10g”

Have you ever been frustrated with SQL Tuning?
Did you get lost in the maze of /+ HINTS +/ and analysis paralysis?
Pre-10g you have to be either a SQL tuning expert or should have a license to expensive tuning tools available in the market to effectively tune SQL Statements. Not the case anymore.

In 10g you can use DBMS_SQLTUNE package to get tuning recommendations for SQL Statements.

Overview

Tuning of SQL Statements using DBMS_SQLTUNE involves following 4 steps:
1) Creating Tuning Task
2) Executing Tuning Task
3) Displaying results of tuning task
4) Implementing Recommendations

Privileges:

ADVISOR privilege should be granted to user to use DBMS_SQLTUNE package.

Step 1: Creating Tuning Task

Tuning task can be created by calling DBMS_SQLTUNE.CREATE_TUNING_TASK function by passing in SQL statement to be tuned along with required arguments. Following example creates a tuning task by the name “vega_tuning_task”

DECLARE
my_task_name   VARCHAR2 (30);
my_sqltext     CLOB;
BEGIN
my_sqltext :=  'SELECT e.last_name, d.department_name, d.department_id
            FROM employees e, departments d
           WHERE e.department_id = d.department_id
             AND d.department_id = :bnd';   
my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
    bind_list     => sql_binds (anydata.convertnumber (9)),
       user_name     => 'HR',
       scope         => 'COMPREHENSIVE',
       time_limit    => 60,
       task_name     => 'vega_tuning_task',         
       description   => 'Tuning Task'
    );
END;
/

Step 2: Executing SQL Tuning Task

Tuning task can be executed by calling DBMS_SQLTUNE.EXECUTE_TUNING_TASK procedure. Run the following PL/SQL block to execute vega_tuning_task created in step 1

BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'vega_tuning_task');
END;
/

Step 3: Checking Status of SQL Tuning Task

Task execution status can be obtained by querying user_advisor_tasks. Use the following query to find out status of vega_tuning_task

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'vega_tuning_task';

Step 4: Retrieving results of SQL tuning task

After task is executed results can be obtained by calling REPORT_TUNING_TASK function

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

Results contain all the finding and recommendations of Automatic SQL Tuning.

Conclusion

Once a tedious task of SQL tuning was made easy in Oracle 10g