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
initialization parameter.

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.
SELECT /*+ dynamic_sampling(emp 10) */
       empno, ename, job, sal
FROM   emp
WHERE  deptno = 30;
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

Missing 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.

Stale 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.

Insufficient Statistics

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
estimate cardinalities.

For some complex predicates, operations or joins, so dynamic sampling may still be

Parallel Execution
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
dynamic statistics.

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 
statement level.

   –System level. Don’t do this!
   –Session level.
   — Statement level
   SELECT /*+ dynamic_sampling(emp 11) */
   empno, ename, job, sal FROM emp WHERE  deptno = 30;
You should not need to change the default value of “2”.   

If the optimizer identifies misestimates, it can create SQL plan directives to force
dynamic sampling.

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.
  • January 18, 2019 | 81 views
  • Comments