Performance Tuning
SQL Tuning Advisor
User Rating: / 1
- Details
-
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: 5447
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