Description: - SQL Tuning Advisor is a program provided by sql that comes along with oracle installation. This advisor can be executed against a sql id and it generates a report with recommendations which generally includes
·
Collection of object statistics
·
Creation of indexes
·
Rewriting SQL statements
·
Creation of SQL profiles
·
Creation of SQL plan baselines
The objective to use this program is to avoid tedious manual
tuning which DBA’s generally do to fix a bad query.
Note:-As mentioned earlier, this advisor comes along with
default oracle installation but It
requires Diagnostic and Tuning packs to use the SQL Tuning Advisor.
Let me demonstrate how to execute SQL tuning advisor against
a SQL id
Step 1.
To run the advisor we need to create tuning task ,
But before that , we need to have the sql id against which
we would run the advisor.
For e.g. let us assume the sql id is – 8d5cry63fwerg65
|
SET
serveroutput ON DECLARE l_sql_tune_task_id VARCHAR2(100); BEGIN l_sql_tune_task_id :=
DBMS_SQLTUNE.create_tuning_task ( sql_id => '&&my_sql_id', scope =>
DBMS_SQLTUNE.scope_comprehensive, time_limit => 120000, task_name =>
'sql_tuning_task_&&my_sql_id', description =>
'Tuning task for statement &&my_sql_id.'); DBMS_OUTPUT.put_line('l_sql_tune_task_id: '
|| l_sql_tune_task_id); END; / |
Enter value for my_sql_id: 8d5cry63fwerg65
Step 2.
Check the task status.
|
SELECT
task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE
'sql_tuning_task_&&my_sql_id'; TASK_NAME STATUS ------------------------------
----------- sql_tuning_task_8d5cry63fwerg65 INITIAL |
Step 3.
Execute the sql tuning task
|
EXEC
DBMS_SQLTUNE.execute_tuning_task(task_name =>
'sql_tuning_task_&&my_sql_id'); |
Step 4.
Check the task status again
|
SELECT
task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE
'sql_tuning_task_&&my_sql_id'; TASK_NAME STATUS ------------------------------
----------- sql_tuning_task_8d5cry63fwerg65
COMPLETED |
Step 5.
Review the recommendations by SQL Tuning Advisor
|
SET LINES 222 SET pages 30000 SET long 999999 SET longc 99999999 SELECT
DBMS_SQLTUNE.report_tuning_task('sql_tuning_task_&&my_sql_id') AS
recommendations FROM dual; |
Step 5.
Implement the Recommendations only
if you find the recommendations are satisfactory.
|
execute
dbms_sqltune.accept_sql_profile(task_name => ‘sql_tuning_task_8d5cry63fwerg65',
task_owner => 'SYS', replace => TRUE); |
Stpe 6.
To drop the tuning task
|
BEGIN DBMS_SQLTUNE.drop_tuning_task (task_name
=> 'sql_tuning_task_&&my_sql_id'); END; / |
Step 7.
Verify the task status
|
SELECT
task_name, STATUS FROM dba_advisor_log WHERE task_name LIKE
'sql_tuning_task_&&my_sql_id'; |
