Database Reference
In-Depth Information
This means that to gather additional information, some (sampling) queries are executed against the referenced
objects. Unfortunately, the statistics gathered by dynamic sampling are neither stored in the data dictionary nor
stored elsewhere. The only way to virtually reuse them is to reuse the shared cursor itself. Also note that the statistics
gathered by dynamic sampling aren't necessarly used. In fact, the query optimizer performs several sanity checks to
decide whether they should be used or not.
■
as of version 12.1, the term
dynamic statistics
is used instead of dynamic sampling. In this topic I always use
the old name.
Note
The value (also called
level
) of the
optimizer_dynamic_sampling
initialization parameter specifies how and
when dynamic sampling is used. Table
9-1
summarizes the accepted values and their meanings. Note that the default
value depends on the
optimizer_features_enable
initialization parameter:
•
optimizer_features_enable
is set to 10.0.0 or higher, the default is level 2.
If
•
optimizer_features_enable
is set to 9.2.0, the default is level 1.
If
•
optimizer_features_enable
is set to 9.0.1 or lower, dynamic sampling is disabled.
If
Table 9-1.
Dynamic Sampling Levels and Their Meaning
Level
When Is Dynamic Sampling Used?
Number of Blocks*
0
Dynamic sampling is disabled.
0
1
Dynamic sampling is used for tables without object statistics. However, this
occurs only if the following three conditions are met: the table has no index,
it's part of a join (also subquery or nonmergeable view), and it has more blocks
below the high watermark than the number of blocks used for the sampling.
32
2
Dynamic sampling is used for all tables without object statistics.
64
3
Dynamic sampling is used for all tables fulfilling the level-2 criterion and, in
addition, for which a guess is used to estimate the selectivity of a predicate.
32 or 64
4
Dynamic sampling is used for all tables fulfilling the level-3 criterion and, in
addition, having two or more columns referenced in a
WHERE
clause.
32 or 64
5
The same as level 4.
64
6
The same as level 4.
128
7
The same as level 4.
256
8
The same as level 4.
1024
9
The same as level 4.
4096
10
The same as level 4.
All
11
The query optimizer decides when and how to use dynamic sampling.
This level is available as of version 12.1 only.
Automatically
determined
* This is the number of blocks used for sampling when dynamic sampling is activated with the initialization parameter
or the hint using the statement-level syntax. For levels 3 and 4, if object statistics are available, then 32 blocks are
sampled; otherwise, 64 blocks are sampled. When a hint using the object-level syntax is used, and for levels from 1 to 9,
