|
|
|
|
|
|
|
|
SQL Tuning made easy in Oracle 10g |
|
|
|
|
Back
Next
“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.
Back
Next
|
|
|
|
|
|
|
|
Home