Database Reference
In-Depth Information
To choose the value of the
optimizer_mode
initialization parameter, you have to ask yourself whether it's more
important for the query optimizer to produce execution plans for the fast delivery of the first row or the fast delivery of
the last row:
all_rows
value. This is the most
•
If fast delivery of the last row is important, you should use the
commonly used configuration.
first_rows_
n
value (where
n
is 1, 10, 100, or 1,000 rows). This configuration should be used only when the application
partially fetches result sets that are larger than the number of rows specified with the
parameter. For existing applications, you can check this by comparing the
executions
and
end_of_fetch_count
columns in the
v$sqlarea
view. Notice that the older implementation of
the first row optimizer (that is, configured through the value
first_rows
) should no longer be
used. In fact, it's provided for backward compatibility only.
•
If fast delivery of the first row(s) is important, you should use the
The default value is
all_rows
. Also note that
INSERT
,
DELETE
,
MERGE
and
UPDATE
statements are always optimized
with
all_rows
. It makes sense to do so because those SQL statements must process all rows before they return control
to the caller.
■
The key idea of the first row optimization is to avoid blocking operations (that is, operations that don't produce
rows until they have run to completion). for this purpose, nested loop joins are generally preferred over hash joins (which
block until a hash table has been built) and merge joins (which block until both inputs have been sorted). In addition, in
some situations,
ORDER BY
operations (which block until the rows have been sorted) are replaced by index range scans.
for large result sets, it's unlikely that the first row optimization leads to optimal performance. So it's of paramount impor-
tance to use first row optimization only when large result sets are only partially fetched by the calling application.
Caution
The
optimizer_mode
initialization parameter is dynamic and can be changed at the instance and session levels.
In a 12.1 multitenant environment, it can also be set at the PDB level. In addition, with one of the following hints, it's
possible to set it at the statement level:
•
all_rows
•
first_rows(
n
)
where
n
is any natural number greater than 0
optimizer_features_enable
In every database version, Oracle introduces or enables new features in the query optimizer. If you're upgrading
to a new database version and want to keep the old behavior of the query optimizer, it's possible to set the
optimizer_features_enable
initialization parameter to the database version from which you're upgrading.
Unfortunately, not all new features are disabled by this initialization parameter. For example, if you set it to 10.2.0.4
in version 11.2, you won't get exactly the 10.2.0.4 query optimizer. For this reason, I usually advise using the default
value, which is the same as the version number used for the database binary files. Also Oracle Support, through
the
Use Caution if Changing the OPTIMIZER_FEATURES_ENABLE Parameter After an Upgrade
note (1362332.1),
provides similar advice.
■
Tip
Changing the default value of the
optimizer_features_enable
initialization parameter is only a short-term
workaround. Sooner or later the application should be adapted (optimized) for the new database version.
