SQL Tuning Advisor.

Different Type of Advisors are:-

SQL> SELECT * FROM DBA_ADVISOR_dEFINITIONS;

ADVISOR_ID ADVISOR_NAME PROPERTY
---------- ------------------------------ ----------
1 ADDM 1
2 SQL Access Advisor 15
3 Undo Advisor 1
4 SQL Tuning Advisor 7
5 Segment Advisor 3
6 SQL Workload Manager 0
7 Tune MView 31

Sql Tuning Advisor:-

The automatic sql tuning feature replaces the manual tuning. The sql tuning advisor has 2 modes, Normal and Tuning Mode.

Normal Mode:-

The sql statement is complied and an execution plan generated, usually generating a good plan for most sql statement gives the very narrow time constraints in normal mode, usually a fraction of a second.

Tuning Mode:-

This mode is used only for the previously identified high load sql, since its analysis are measured in minutes rather than fractions of second. The output from tuning mode goes beyond an execution plan to a series of recommendation actions and rationales that should produce a better execution plan for the future execution of sql statements. In tuning mode, the oracle query optimizer is called automatic tuning optimizer (ATO).

Before tuning a high load sql statement, you must be able to identify which sql statement are causing the high load on system. In 10g the ADDM automates the process of identifying the high load sql from the sql workload.

The sql tuning advisor receives one or more input as sql stmt's and considering no. of factors, including cpu, i/o and temporary space usage , provides inputs on how to improve the execution plan along with expected benefits. Th sql tuning advisor perform four specific type of analysis.

* Statistics analysis.
* Sql profiling.
* Access path analysis.
* Sql structure analysis.

Statistics analysis:-

The optimizer recommend the gathering of missing / stale Statistics.

Sql profiling:-

The optimizer may be able to improve the performance by gathering additional statistics and altering session specific parameter such as optimizer_mode. If such improvements are possible the information is stored in an sql profile.

Acces Path analysis:-

The optimizer investigates the effect of new or modified indexes on the access path. Its index recommendation relates to a specific statement so where necessary it will also suggest the use of sql access advisor to check the impact of these indexes on a representative sql workload.

Sql structure analysis:-

The optimizer suggest alternative for sql stmt that contains structure that may impact on performance.

Working with Sql Tuning Advisor:-

Package Name :- dbms_sqltune.

Steps
1. Create Tuning Task
2. Execute Tuning Task
3. Get the Tuning Task report.


Create Tuning Task

SQL> declare
task_nm varchar2(100);
sql_stmt varchar2(100);
begin
task_nm := dbms_sqltune.create_tuning_task
(
SQL_TEXT => 'select * from employ where empid = 8765',
TASK_NAME => 'SQL_TUNNING_TASK'
);
end;
/

Execute Tuning Task

SQL> exec dbms_sqltune.execute_tuning_task (TASK_NAME => 'SQL_TUNNING_TASK');


Get Tuning Task Report

SQL> set long 10000;
SQL> set pagesize 10000;
SQL> select dbms_sqltune.report_tuning_task ('SQL_TUNNING_TASK') FROM DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNNING_TASK
Tuning Task Owner : MANZOOR
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 12/03/2010 11:32:04
Completed at : 12/03/2010 11:32:04
Number of Statistic Findings : 1
Number of Index Findings : 1

-------------------------------------------------------------------------------
Schema Name: MANZOOR
SQL ID : a1ry0qy36vqvm
SQL Text : select * from employ where empid = 8765

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- Statistics Finding
---------------------
Table "MANZOOR"."EMPLOY" was not analyzed.

Recommendation
--------------
- Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'MANZOOR', tabname =>
'EMPLOY', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO');

Rationale
---------
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.

2- Index Finding (see explain plans section below)
--------------------------------------------------
The execution plan of this statement can be improved by creating one or more
indices.

Recommendation (estimated benefit: 100%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design
or creating the recommended index.
create index MANZOOR.IDX$$_00FA0001 on MANZOOR.EMPLOY('EMPID');

Rationale
---------
Creating the recommended indices significantly improves the execution plan
of this statement. However, it might be preferable to run "Access Advisor"
using a representative SQL workload as opposed to a single statement. This
will allow to get comprehensive index recommendations which takes into
account index maintenance overhead and additional space consumption.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 1720151680

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOY | 1 | 30 | 14 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMPID"=8765)

2- Using New Indices
--------------------
Plan hash value: 383576207

--------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
--------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (
0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOY | 1 | 30 | 2 (
0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX$$_00FA0001 | 1 | | 1 (
0)| 00:00:01 |
--------------------------------------------------------------------------------
--------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("EMPID"=8765)

-------------------------------------------------------------------------------


Execute the below to drop the Task.

SQL> exec dbms_sqltune.drop_tuning_task('SQL_TUNNING_TASK');

PL/SQL procedure successfully completed.


We can also create a tuning task based using below methods.

a) AWR Topsp ( Get it from AWR Report)

Include parameter (Eg)
BEGIN_SNAP => 12
END_SNAP => 14
SQL_ID => '19rsgrtsc1'

b)Direct SQLID

Include Parameter
SQL_ID => '19rsgrtsc1'

c) Using Sql Sets

Include Parameters
SQLSET_NAME => 'TEST_SQL_TUNING_SET'

if the scope parameter is set as limited then sql profiling analysis is omitted. The time_limit parameter simply restricts the time the optimizer can spend compiling the recommendations. During the execution phase you may wish to pause and restart the task, cancel it or reset the task to allow it to be executed as below.

--Interept and resume a tuning task

> exec dbms_sqltune.interrupt_tuning_Task('SQL_TUNNING_TASK');
> exec dbms_sqltune.resume_tuning_task('SQL_TUNNING_TASK');

--Cancel Tuning Task

> exec dbms_sqltune.cancel_tuning_task ('SQL_TUNNING_TASK'):

--Reset a tuning Task allow it to re execute.

> exec dbms_sqltune.reset_tuning_task ('SQL_TUNNING_TASK'):

The status of the tuning task can be monitored using the dba_advisor_log;