Dec 17 2008
?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.
Home 
Webmaster Said:
Thank you.