Oracle Database introduced Dynamic Sampling to allow the optimizer to gather
additional information at parse time.
If database statistics were missing, stale or insufficient to produce a good execution
Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING
They can be initialized at instance or session level, or for individual queries using the
with available values ranging between “0” (off) to “10” (aggressive sampling) with
default value of “2”.
Dynamic Sampling in 12C
Dynamic sampling has been renamed to Dynamic Statistics in Oracle Database 12c.
Much of the functionality is the same, but a new sample level of 11 has been added.
The new setting allows the optimizer to decide if dynamic statistics should be sampled
and if so, what sample level to use.
Why Use Dynamic Sampling
Dynamic sampling enables the server to improve performance by :
Estimating single-table predicate selectivities where available statistics are missing or
may lead to bad estimations.
Estimating statistics for tables and indexes with missing statistics.
Estimating statistics for tables and indexes with out of date statistics.
At compile-time Oracle determines if dynamic sampling would improve query
If so it issues recursive statements to estimate the necessary statistics.
Dynamic sampling benefits:
Dynamic sampling can be beneficial in the following ways
The sample time is small compared to the overall query execution time.
T he current database statistics alone would not create an optimal plan, so dynamic
sampling results in a better performing query.
The query may be executed multiple times.
In addition to the OPTIMIZER_DYNAMIC_SAMPLING system parameter the dynamic
sampling level can be set.
We use DYNAMIC_SAMPLING optimizer hint for specific queries like the following.
The results of dynamic sampling are repeatable provided no rows are inserted,
updated or deleted from the sampled table.
When to Sample
The following situations will trigger automatic sampling to gather dynamic statistics
Dynamic statistics are sampled if there are missing database statistics.
They may be missing because they are newly created objects, or had their statistics
locked before any statistics were gathered.
Although the dynamic statistics should help the optimizer, these statistics are
considered low quality compared to conventional database statistics.
Statistics are considered stale when 10% or more of the rows in the table have
changed since the statistics were last gathered.
Stale statistic can affect cardinality estimates.
Because of changes to the number of rows in the table and inaccuracies in column
statistics, such as number of distinct values, high and low column values.
Existing database statistics may not be sufficient to generate an optimal execution
Dynamic statistics can make up for the absence of extended statistics for column
groups and expressions, as well as missing histograms that would identify data skew.
Even when all the necessary statistics are present it may not be possible to correctly
For some complex predicates, operations or joins, so dynamic sampling may still be
Parallel execution is typically used to speed up long running processes.
For a long running process, the time associated with sampling dynamic statistics is
compared to the query execution time.
So it may be worth spending a little more time to make sure the execution plan is
SQL Plan Directives
The presence of one or more usable SQL plan directives will trigger the sampling of
SQL plan directives are created when the optimizer identifies misestimates.
The cardinality of operations or degree of parallelism (DOP) from previous executions
of the statement, or other statements using similar query expressions.
Controlling Dynamic Statistics
Dynamic statistics can be controlled by the
OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and the
The following examples show how to control dynamic statistics at system, session and
You should not need to change the default value of “2”.
If the optimizer identifies misestimates, it can create SQL plan directives to force
The presence of SQL plan directives influence the way DBMS_STATS gathers
statistics, which potentially fixes the root cause of the problems.
Reuse of Dynamic Statistics
Dynamic statistics can be stored in the SGA as OPT_ESTIMATE hints in the V$SQL_REOPTIMIZATION_HINTS view.