Tips by Burleson
Oracle10g SQL Tuning Advisor
SQL Tuning Advisor is like a container for conducting and
analyzing many tuning tasks. It calls the optimizer internally and
performs the analysis as follows:
- Executes the Stale or Missing statistics analysis and makes
a recommendation to collect, if necessary.
- Plans the tuning analysis and creates SQL profile. SQL
Profile is a collection of the historical information of prior
runs of the SQL statement, comparison details of the actual and
estimated cardinality, and predicate selectivity etc. SQL
Profile is stored persistently in the data dictionary, and hence
does not require any application code changes.
- Performs the access path analysis. The Optimizer recommends
new indexes that produce a significantly faster execution path.
- Restructure the SQL statement. Optimizer identifies SQL
statements that have bad plans and makes relevant suggestions to
The plan analysis mode, which creates the SQL Profiles, is a
significant stage where additional information for the query is
collected by the optimizer. This analysis is not possible in the
normal mode. Such a SQL profile helps generate a better execution
plan than the normal optimization. Additional tasks like checking
for advanced predicate selectivity, correlation between columns,
join skews, and complex predicates such as functions, help in
profiling the SQL statement. Once a statement is profiled and
stored, it can be used at will.