Database Reference
In-Depth Information
The
optimizer_dynamic_sampling
initialization parameter is dynamic and can be changed at the instance level
and at session level. In a 12.1 multitenant environment, it can also be set at the PDB level. In addition, it's possible to
specify a value at the statement level with the
dynamic_sampling
hint. The hint supports two syntaxes:
optimizer_dynamic_sampling
•
The statement-level syntax overrides the value of the
initialization parameter:
dynamic_sampling(
level
)
•
The object-level syntax activates dynamic sampling for a specific table only:
dynamic_sampling(table_alias
level
)
■
When dynamic sampling is activated through the hint using the object-level syntax, the sampling always
takes place. In other words, the query optimizer doesn't check whether the rules mentioned in Table
9-1
are fulfilled.
however, depending on whether object statistics are already available, the sampled statistics might be discarded. all this
can be an unnecessary overhead, so I don't recommend using the object-level syntax.
Caution
As of version 11.2, if the
optimizer_dynamic_sampling
initialization parameter is set to the default, then the
query optimizer automatically decides how and when dynamic sampling is used for SQL statements executed in
parallel. This is done because parallel SQL statements are expected to consume a lot of resources, and it's therefore
critical to have the best possible execution plan.
The query optimizer can use dynamic sampling to gather two types of statistics. The first type includes the following:
•
The number of blocks below the high watermark of a segment
•
The number of rows in a table
•
The number of distinct values in a column
NULL
values in a column
•
The number of
As you can see, the statistics of the first type are equivalent to corresponding object statistics that should already
be available in the data dictionary. As a result, the statistics gathered by dynamic sampling would only be useful if
the object statistics are either missing or inaccurate (stale). But be aware: by default, the statistics of the first type
are gathered only for objects that are missing object statistics. However, you can force the matter by specifying the
dynamic_sampling_est_cdn(table_alias)
hint. You might do so if the statistics are present but inaccurate. The hint
forces them to be gathered when they would otherwise not be.
The second type of statistics gathered by dynamic sampling includes the following:
•
The selectivity of a predicate
•
The cardinality of a join (from version 12.1 onward only)
•
The cardinality of an aggregation (from version 12.1 onward only)
Because these statistics go beyond the information provided through object statistics (despite the fact that in
some cases the selectivity of a predicate can be obtained through extended statistics), they're intended to increase the
information that object statistics can provide. With them, the query optimizer might be able to perform better estimations.
Here are some examples (excerpts of the output generated by the
dynamic_sampling_levels.sql
script run in
version 11.2.0.3) illustrating in which situations the values between 1 and 4 lead to dynamic sampling. The tables used
for the tests are created with the following SQL statements. Initially, they have no object statistics. Note that the only
difference between the
t_noidx
table and the
t_idx
table is that the latter has a primary key (and therefore an index):
CREATE TABLE t_noidx (id, n1, n2, pad) AS
SELECT rownum,
