Oracle SQL Tuning
Oracle Tips by Burleson Consulting
Before Oracle10g, it was extremely difficult to track index usage
and see how SQL statements behaved except when they were in the
library cache. With Oracle10g, it is now possible to track SQL
behavior over time and ensure that all SQL is using an optimal
execution plan, and Oracle10g provides the ability to track SQL
execution metrics with new dba_hist tables, most notably
dba_hist_sqlstat and dba_hist_sql_plan.
It is important to track the relationship between database objects,
such as tables, indexes, and the SQL that accesses the objects.
Oracle SQL execution plans for any given statement may change if the
system statistics change, dynamic sampling is used, materialized
views are created, or indexes are created or dropped.
The SQL Tuning Advisor
The SQL Tuning Advisor (STA) works with the Automatic Tuning
Optimizer (ATO) to analyze historical SQL workload using data from
the AWR, and it generates recommendations for new indexes and
materialized views that will reduce the disk I/O associated with
troublesome SQL statements.
The STA is primarily designed to replace the manual tuning of SQL
statements and speed up the overall SQL tuning process. The SQL
Tuning Advisor studies poorly executing SQL statements and evaluates
resource consumption in terms of CPU, I/O, and temporary space.
The advisor receives one or more SQL statements as input and
provides advice on how to optimize their execution plans, gives the
rationale for the advice, the estimated performance benefit, and the
actual command to implement the advice.
The STA can be thought of as 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 a SQL Profile.
The 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, so it does not require any application code changes.
- Performs the access path analysis. The Optimizer
recommends new indexes that produce a significantly faster execution
- 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
Ion tool is
also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.