Control: Inside the Oracle Optimizer
By Donald K. Burleson
OTN Member since 2001
new applications for the Oracle Cost-Based Optimizer?
Here's the latest information about how it works.
This article has the following sections:
PART 3 - Proper Development
Many infrastructure issues must be addressed
in order to avoid surprises with SQL optimization. Shops that do not
create this infrastructure are plagued by constantly changing SQL
execution plans and poor database performance.
The key to success with the CBO is stability
and ensuring your success with the CBO involves several important
- Reanalyze statistics only when
necessary. One of the most common mistakes made by Oracle DBAs
is to frequently re-analyze the schema. Remember, the sole purpose
of doing that is to change the execution plans for your SQL, and if
it ain't broke, don't fix it. If you are satisfied with your 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.
- Force developers to tune their
SQL. Many developers falsely assume that their sole goal is to
write SQL statements that deliver the correct data from Oracle. In
reality, formulating the SQL is only half their job. Successful
Oracle shops always require that developers ensure that their SQL
accesses the database in an optimal fashion and require migration
forms that include the execution plan for all new SQL.
- Carefully manage CBO statistics.
Successful Oracle shops carefully manage the CBO statistics to ensure that the CBO works the same in their test and production
environments. A savvy DBA will 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.
- Rarely change CBO parameters.
The CBO parameters are very dangerous because a single parameter
change could adversely affect the performance of an entire
enterprise. Changes to critical CBO parameters such as
should only be made after careful system testing.
- Ensure static execution plans.
Nobody like surprises, and successful CBO shops lock down SQL execution plans by carefully controlling CBO statistics, using
stored outlines optimizer plan stability, or adding detailed hints
to their SQL.
Let's take a closer look
at these issues.
Re-analyze statistics only when
necessary. It is very rare for the
fundamental nature of a schema to change; large tables remain large,
and index columns rarely change distribution, cardinality, and skew.
You should only consider periodically re-analyzing your total schema
statistics if your database matches these criteria:
- Data-analysis databases.
Many scientific systems load experimental data, analyze the data,
produce reports, and then truncate and reload a new set of
experiments. For these types of systems it may be necessary to
re-analyze the schema each time the database is reloaded.
- Highly volatile databases.
In these rare cases, the size of table and the characteristics of
index column data changes radically. For example, if you have a
table that has 100 rows one week and 10,000 rows the next week, then
you may want to consider a periodic reanalysis of statistics.
Force developers to tune their SQL.
It is amazing how many Oracle shops do not consider their SQL execution plans. They assume that because the CBO is intelligent and
sophisticated it will always provide the best execution plan, no
Because SQL is a declarative language, a
query can be written in many different ways, each with a different execution plan. For example, all of the following SQL queries give the
correct answer, but with widely varying execution plans:
-- Form one using non-correlated subquery)
book_key not in (select book_key from sales);
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=64)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=64)
3 1 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=25)
-- Form two using outer join
b.book_key = s.book_key(+)
quantity is null;
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=8200)
1 0 FILTER
2 1 FILTER
3 2 HASH JOIN (OUTER)
4 3 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=20 Bytes=1280)
5 3 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=100 Bytes=1800)
-- Form three using correlated subquery
book_title not in (
book.book_key = sales.book_key
quantity > 0);
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=59)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'BOOK' (Cost=1 Card=1 Bytes=59)
3 1 FILTER
4 3 NESTED LOOPS (Cost=6 Card=1 Bytes=82)
5 4 TABLE ACCESS (FULL) OF 'SALES' (Cost=1 Card=5 Bytes=90)
6 4 TABLE ACCESS (BY INDEX ROWID) OF 'BOOK' (Cost=1 Card=1)
7 6 INDEX (UNIQUE SCAN) OF 'PK_BOOK' (UNIQUE)
As we can see, the proper formulation of the
query has a dramatic impact on the execution plan for the SQL. Savvy
Oracle developers know the most efficient way to code Oracle SQL for
optimal execution plans, and savvy Oracle shops train their developers
to formulate efficient SQL.
Some techniques for assisting developers in
tuning their SQL include:
- Training them to use the
TKPROF utilities and to interpret
SQL execution results. Oracle University has several excellent
classes on CBO optimization techniques.
- Forcing all SQL that is migrating
into production to have verification that the SQL has been tuned.
- Making performance an evaluation
criterion. Instead of noting that the best developer is the
developer who writes SQL the fastest, add the mandate that a good
developer also writes SQL that performs efficiently.
Carefully manage CBO statistics.
Because the CBO relies on information about database objects, it is imperative that the CBO has the best possible statistics and that the
same excellent statistics be used in the production, test, QA, and
It is an important job of the Oracle DBA to
properly gather and distribute statistics for the CBO. The goal of the DBA is 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
Exporting CBO statistics is done with the
in the dbms_stats package. In
this example we export the current CBO statistics into a table called
When captured, we can move the table to
other instances and use the import_system_stats
procedure in dbms_stats to
overlay the CBO statistics when processing modes change:
Change CBO parameters only rarely.
Many Oracle shops change the fundamental characteristics of their CBO
by changing the global CBO parameters. Especially dangerous are
changes to optimizer_mode, optimizer_index_cost_adj,
and these changes should only be made when a sound reason exists.
Other CBO parameters such as hash_area_size
and sort_area_size are less
dangerous and can be set at the individual session level to change the CBO evaluates a query.
Ensure static execution plans.
Remember, re-analyzing a schema could cause thousands of SQL
statements to change execution plans. Many Oracle shops have
implemented standards that require that all SQL, when tested and
approved in their test environment, function identically in
The only way to achieve this mandate is to
migrate the statistics that were used in SQL testing into the production environment when the SQL is migrated. However, the DBA must
ensure that a migration of statistics from test into production does
not adversely affect the execution plans of other SQL that touch the target table. Hence, the DBA will carefully manage the CBO statistics,
ensuring that no SQL changes execution plans after it is migrated into
Our tuning tools:
Our Ion tool is
the easiest way to analyze Oracle performance and Ion
allows you to spot hidden performance trends.
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.