This book excerpt will focus on real-world techniques for improving the speed of SQL queries with a focus on the new Oracle10g features. The topics will include the new Oracle parameters that affect SQL performance, the use of hints to change SQL execution plans, rewriting SQL queries in
more efficient forms and the use of advanced techniques such as Materialized Views, replacing SQL with PL/SQL, the new automated CBO statistics collection and using the new Oracle10g CPU costing approach.
This is an excerpt from the bestselling book Oracle Tuning: The Definitive Reference by Alexey Danchenkov and Donald Burleson, technical editor Mladen Gogala. Click here to download the full chapter.
Understanding Oracle 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 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.
Optimizing Oracle SQL execution
The key to success with the Oracle Cost-based Optimizer (CBO) is stability, and ensuring
success with the CBO involves the consideration of several important infrastructure
issues.
- Ensure static execution plans: Whenever an object is re-analyzed, the execution plan
for thousands of SQL statements may be changed. Most successful Oracle sites will
choose to lock down their SQL execution plans by carefully controlling CBO
statistics, using stored outlines (optimizer plan stability), adding detailed hints to their
SQL, or by using Oracle10g SQL Profiles. Again, there are exceptions to this rule
such as LIMS databases, and for these databases, the DBA will choose to use
dynamic sampling and allow the SQL execution plans to change as the data changes.
- Reanalyze statistics only when necessary: One of the most common mistakes made
by Oracle DBAs is to frequently re-analyze the schema. The sole purpose of doing
that is to change the execution plans for its SQL, and if it isn't broken, don't fix it. If
the DBA is satisfied with current SQL performance, re-analyzing a schema could
cause significant performance problems and undo the tuning efforts of the
development staff. In practice, very few shops are sufficiently dynamic to require
periodic schema re-analysis.
- Pre-tune the SQL before deploying: Many Oracle systems developers assume that
their sole goal is to write SQL statements that deliver the correct data from Oracle. In
reality, writing the SQL is only half their job and successful Oracle sites require all
developers to ensure that their SQL accesses Oracle in an optimal fashion. Many
DBAs will export their production CBO statistics into their test databases so that
their developers can see how their SQL will execute when it is placed into the
production system. DBAs and staff should be trained to use the AUTOTRACE and
TKPROF utilities and to interpret SQL execution results.
- Manage schema statistics: All Oracle DBAs should carefully manage the CBO
statistics to ensure that the CBO works the same in their test and production
environments. A savvy DBA knows how to collect high-quality statistics and migrate
their production statistics into their test environments. This approach ensures that all
SQL migrating into production has the same execution plan as it did in the test
database.
- Tune the overall system first: The CBO parameters are very powerful because a
single parameter change could improve the performance of thousands of SQL
statements. Changes to critical CBO parameters such as optimizer_mode,
optimizer_index_cost_adj, and optimizer_index_caching should be done before tuning
individual SQL statements. This reduces the number of suboptimal statements that
require manual tuning.
Prior to Oracle10g, it was an important job of the Oracle DBA to properly gather and
distribute statistics for the CBO. The goal of the DBA was to keep the most accurate
production statistics for the current processing. In some cases, there may be more than
one set of optimal statistics.
For example, the best statistics for OLTP processing may not be the best statistics for the
data warehouse processing that occurs each evening. In this case, the DBA will keep two
sets of statistics and import them into the schema when processing modes change.
The following section provides a quick, simple review of the goals of SQL tuning.
Goals of SQL tuning
There are many approaches to SQL tuning and this paper describes a fast, holistic
method of SQL tuning where we optimize the SGA, the all-important optimizer
parameters, and adjust CBO statistics, all based on current system load. Once the
"best" overall optimization is achieved, we drill-down into the specific cases of
sub-optimal SQL, and change their execution plans with SQL profiles, specialized
CBO stats or hints.
Despite the inherent complexity of tuning SQL, there are general guidelines that every
Oracle DBA follows in order to improve the overall performance of their Oracle systems.
The goals of SQL tuning are simple:
- Replace unnecessary large-table full-table scans with index scans.
- Cache small-table full table scans.
- Verify optimal index usage.
- Verify optimal JOIN techniques.
- Tune complex subqueries to remove redundant access.
These goals may seem deceptively simple, but these tasks comprise 90% of SQL
tuning. They do not require a thorough understanding of the internals of Oracle SQL.
This venture will begin with an overview of the Oracle SQL optimizers.
Of course, the SQL can be tuned to one's heart's content, but if the optimizer is not fed
with the correct statistics, the optimizer may not make the correct decisions. Before
tuning, it is important to ensure that statistics are available and that they are current.
The following section will provide a closer look at the goals listed above as well as how
they simplify SQL tuning.
Click here to read the rest of this chapter.
