Performance Tuning

SQL Tuning Advisor

User Rating:  / 1
PoorBest 
Parent Category: Articles
Created on Thursday, 18 March 2010 22:12
Last Updated on Monday, 12 March 2012 13:54
Published on Thursday, 18 March 2010 22:12
Written by Guy Lambregts
Hits: 5266

SQL Tuning Advisor

 

ARTICLE TO BE REVIEWED AND TO BE IMPROVED (  Feb 2010 )

 

Note you need an additional licence in order to use the SQL Tuning Advisor


You can use the SQL Tuning Advisor through the GUI Oracle Enterprise Manager or using the command line with dbms_sqltune . Using the command line is less user friendly.

The SQL Tuning Advisor is an 10G utility - a built in engine - to analyse the execution performance, access path and structure of sql statements

Input

sources for the SQL Tuning Advisor are


1) ADDM reporting ( ADDM can recommend to use the SQL Tuning Advisor )

2) AWR reporting  ( High Load SQL )

3) the actual cursor cache



4) SQL Tuning Set ( STS ). A SQL Tuning Set is a set of SQL statements ( sounds logic ) . Also for the STS possible input sources are AWR baselines or AWR preserved snapshots or the actual cursor cache. A STS can be transferred from one database to another. ( export an import to and from a staging table )

5) a single SQL statement ( see also SQL Tuning Advisor using the command line )

EXECUTION

Execution of a SQL Tuning tasks can be done with 2 options :


1) limited  ( no sql profiling options used )

2) comprehensive ( with sql profiling )


The execution of a SQL Tuning task can be scheduled with the DBMS_SCHEDULER. this can be interesting to postpone a thorough analysis. 

OUTPUT

Output of the SQL Tuning advisor is

1) possible recommendation to adjust schema object statistics  ( if they are stale )

2) possible recommendation to create a SQL profile. A SQL profile - once accepted - is stored in the data dictionary. A SQL profile gives the optimizer additional information in order to parse an optimal / better execution plan without the need the application code is changed. The old fashionned stored outlines freeze an execution plan, SQL profiles do not. SQL profiles can be grouped toghether in a SQLTUNE Category. The instance initialization parameter SQLTUNE_CATEGORY can be used - possible at the session level - to decide which profiles are to be taken into account by a user session.

3) possible recommendation to create additional indexes

4) possible recommendation to rewrite the SQL in another / better way.

 

As said this time the GUI EM DB Console is the preferred way. Some dynamic performance views are

DBA_SQL_PROFILE 

DBA_SQLTUNE_STATISTICS

DBA_SQLTUNE_BINDS

DBA_SQLTUNE_PLANS

DBA_ADVISOR_FINDINGS

DBA_ADVISOR_RECOMMENDATIONS

DBA_ADVISOR_RATIONALE