Understanding SQL Tuning
Before relational databases were introduced, database queries
required knowledge of the internal structures and developers
needed to build in the tuning as a part of writing the database
query. However, the SQL standard imposed a declarative solution
to database queries where the database optimizer determines
important data access methods such as what indexes to use and
the optimal sequence to join multiple tables together.
Today, it is not enough for a developer to write an SQL
statement that provides the correct answer. SQL is declarative,
so there are many ways to formulate a query, each with identical
results but with far different execution times.
Oracle SQL tuning is a phenomenally complex subject, and entire
books have been devoted to the nuances of Oracle SQL tuning,
most notably the Kimberly Floss book
Oracle SQL & CBO Internals
by Rampant TechPress.
This chapter provides a review the following areas of SQL
tuning:
§
The goals of SQL tuning
§
Simplifying complex SQL
§
SQL Optimization instance parameters
§
Statistics and SQL optimization
§
Oracle10g and CBO statistics
§
Oracle tuning with hints
§
Oracle10g SQL profiles
§
AWR and SQL tuning
§
ADDM and SQL tuning
The first three sections will be an overview of general
Oracle10g tuning concepts, so that the basic tools and
techniques for tuning SQL optimization are clearly introduced.
The focus will then shift to an exploration of the new Oracle10g
SQL Profiles, and will eventually delve into
the internals of AWR and explore how the SQLTuning and SQLAccess advisor use time-series
metadata.
SEE CODE DEPOT FOR FULL SCRIPTS
