Making the Default
Optimizer Mode Permanent
Oracle Tips by
To make sure that your SQL baseline remains
permanent, it is a good idea to add the appropriate hint or stored
outline to all of the SQL that uses the default optimizer mode. This
can save hundreds of hours of work when you migrate to a later release
of Oracle and you do not want the improved CBO to change SQL that you
have already tuned.
For example, let’s say that your evaluation
showed that your best overall SQL performance is with
optimizer_mode=first_rows. After seeing the initialization
parameter, you should take the time to firm up all queries that
benefit from the system-wide default.
This is done by adding the first_rows
hint to the SQL source code, or by creating stored outlines for the
SQL to ensure that they will always use the first_rows method.
This will ensure that the default baseline remains, and it prevents
changes to CBO statistics and upgrades from changing the execution
Instance-Wide Parameters to Improve the Speed of Full-Table Scans
Once we have verified that a full-table scan is
legitimate, based on the number of rows requested, we can look at
improving the performance of the full-table scan. While I will be
discussing this in detail in Chapter 10, we should review the
initialization parameters that relate to the speed of full-table
As a quick summary, we need to verify the
following initialization parameters:
To enable parallel query, the DBA will locate
those SQL statements that require full-table scans and then ensure
that these queries utilize parallel query by adding the full
and parallel hints to the SQL and making the change persistent
with optimizer plan stability or by changing the SQL source code to
include the hints.
WARNING: It is very dangerous to enable
parallel query for a table with the alter table command. Once a
table is marked for parallel query, the CBO may change the execution
plan for existing queries to use parallel full-table scans instead of
index scans. This well-intentioned mistake has crippled many
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.