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.
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
ADVISOR privilege should be granted to user to use DBMS_SQLTUNE package.
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.
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; /
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';
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.
Once a tedious task of SQL tuning was made easy in Oracle 10g