Another great feature of Oracle 10G that allow you to tune
SQL. Now you don't need to tune SQL statement manually. This new feature
does it for you.
SQL Tuning Advisor using DBMS_SQLTUNE package and very simple to use.
The example below shows how to use SQL advisor.
1. Grant following access to the user that is going to run this new
tool. In the example below SCOTT is the owner of the schema.
GRANT ADVISOR TO SCOTT;
GRANT SELECT_CATALOG_ROLE TO SCOTT;
GRANT EXECUTE ON DBMS_SQLTUNE TO SCOTT; |
2. Create the tuning task
DECLARE
task_name_var VARCHAR2(30);
sqltext_var CLOB;
BEGIN
sqltext_var := 'SELECT * from EMP where empno = 1200';
task_name_var := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text => sqltext_var,
user_name => 'SCOTT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'sql_tuning_task_test1',
description => 'This is a test tuning task on EMP table');
END;
/ |
Some time you may have queries that might take longer than the time that
you have specified in the "time_limit" parameter. If this is the case then
remove this parameter.
NOTE: You can not create more than one task with the same name. If this is
the case then drop the existing task or use a different name.
2.1 To view the existing task for the user run the following
statement.
| select task_name from dba_advisor_log where owner =
'SCOTT'; |
3. Execute the tuning task
| Execute dbms_sqltune.Execute_tuning_task (task_name =>
'sql_tuning_task_test1'); |
3.1 You can check the status of the task using following query.
select status from dba_advisor_log where task_name='sql_tuning_task_test1';
4. Now view the Recommendation
set linesize 100
set long 1000
set longchunksize 1000
SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task_test1')
from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TASK_TEST1')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task_test1
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 06/22/2006 15:33:13
Completed at : 06/22/2006 15:33:14
-------------------------------------------------------------------------------
SQL ID : ad1437c24nqpn
SQL Text: SELECT * from EMP where empno = 1200
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SCOTT"."EMP" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, |
Based on this information, you can decide what actions are necessary to tune
the SQL.
|
