OPTIMIZER_DYNAMIC_SAMPLING


Property Description

Parameter type

Integer

Default value

If OPTIMIZER_FEATURES_ENABLE is set to 10.0.0 or higher, then 2

If OPTIMIZER_FEATURES_ENABLE is set to 9.2.0, then 1

If OPTIMIZER_FEATURES_ENABLE is set to 9.0.1 or lower, then 0

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

0 to 11

Basic

No


OPTIMIZER_DYNAMIC_SAMPLING controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics.

Note:

Dynamic statistics were called dynamic sampling in releases before Oracle Database 12c.

When this parameter is set to 11, the optimizer will use dynamic statistics to verify cardinality estimates for all SQL operators, and it will determine an internal time limit to spend verifying the estimates.

There are cases where the optimizer will automatically decide to use 11, for example:

  • The query will run in parallel.

  • The query was executed before and its history is available (from the cursor cache, Automatic Workload Repository, or the SQL Management Base).

If the value of OPTIMIZER_DYNAMIC_SAMPLING is set to 11, the OPTIMIZER_FEATURES_ENABLE setting has no effect on the OPTIMIZER_DYNAMIC_SAMPLING setting.

See Also:

Oracle Database SQL Tuning Guide for more information on setting this parameter