SQL Tuning made easy in Oracle 10g

articles: 

“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;
/

Create_tuning_task functions returns name of the task created.

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

Comments

cbruhn2's picture

KhrishnaBoppana is very right about all the great faetures about the new packages in oracle 10g.
You just have to remember that even to use these packages you have to pay for the tuning advisor to Oracle.
Even if you don't use it from the database console and directly from the package included in oracle you have to pay.
Every time you start the tuning advisor it's logged in Oracle and if oracle support comes by you probably have to pay the feee.
But otherwise if you have at very serious problem it might be worth the cost of the package fee.

Best regards
Carl Bruhn

Good one.

Small change on Step 4: Retrieving results of SQL tuning task

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

Dear Khrishna,
Thank you for exploring oracle supplied packages for tuning.
I tried with some queries. But the report gives only error message stating 'ORA-16951: Too many bind variables supplied for this SQL statement'
I even tried simple select statement with out any where clause. Still I am getting the same error.
Can you please look into this clarify.
Thanks in Advance
Sreenadh

Hi Sreenath,

I had similar problem while running dbms_sqltune. Metalink article 813596.1 describes the issue and resolution, the resolution (worked for me) is:
Remove the parameter

bind_list => sql_binds(anydata.ConvertNumber(100))
If you have this in your code, you can remove and check...just in case if anyone else gets similar issue...

Thanks!