Optimizer Dynamic Sampling / Automatic Dynamic Statistics
Dynamic sampling (DS) was introduced in Oracle database
9i R2. This is to improve the optimizer’s ability to generate good execution
plan.
How Dynamic
Sampling Works?
In very simple meaning, Optimizer takes a sample of rows
from a table to calculate missing statistics.
During the compilation of a SQL statement, the optimizer
decides whether to use Dynamic Sampling or not by considering whether the
available statistic are sufficient or not.
Typically, Dynamic Sampling used to compensate for
missing or insufficient statistics to generate a good execution plan.
Note that,
The statistics gathered dynamically are not as high a quality or as complete as
the statistics gathered using the DBMS_STATS package but it is very useful to
generate better plan.
Oracle Database 12c Enhancements
Note that
from Oracle Database 12c, dynamic sampling has been enhanced to become dynamic
statistics. Now Term “Dynamic
Sampling” refers as “Dynamic Statistics” in Oracle Database 12c.
Dynamic statistics allow the optimizer to augment
existing statistics to get more accurate cardinality estimates for not only
single table accesses but also joins and group-by predicates.
Different levels of parameter optimizer_dynamic_sampling.
Level
|
When the Optimizer Uses Dynamic Statistics
|
Sample Size (Blocks)
|
0
|
Do not use dynamic statistics
|
n/a
|
1
|
Use dynamic statistics for all tables that do not have
statistics, but only if the following criteria are met:
There is at least 1 nonpartitioned table in the query
that does not have statistics.
This table has no indexes.
This table has more blocks than the number of blocks
that would be used for dynamic statistics of this table.
|
32
|
2
|
Use dynamic statistics if at
least one table in the statement has no statistics. This is the default setting.
|
64
|
3
|
Use dynamic statistics if any of the following
conditions is true:
The statement meets level 2 criteria.
The statement has one or more expressions used in the WHERE
clause predicates, for example, WHERE SUBSTR(CUSTLASTNAME,1,3).
|
64
|
4
|
Use dynamic statistics if any
of the following conditions is true:
The statement meets level 3
criteria.
The statement uses complex
predicates (an OR or AND operator
between multiple predicates on the same table).
|
64
|
5
|
Use dynamic statistics if the statement meets level 4
criteria.
|
128
|
6
|
Use dynamic statistics if the
statement meets level 4 criteria.
|
256
|
7
|
Use dynamic statistics if the statement meets level 4
criteria.
|
512
|
8
|
Use dynamic statistics if the
statement meets level 4 criteria.
|
1024
|
9
|
Use dynamic statistics if the statement meets level 4
criteria.
|
4086
|
10
|
Use dynamic statistics if the
statement meets level 4 criteria.
|
All blocks
|
11
|
Use dynamic statistics automatically when the optimizer
deems it necessary. The resulting statistics are persistent in the statistics
repository, making them available to other queries.
(11gR2
11.2.0.4 and above)
|
Automatically determined
|
Enabling Automatic Dynamic Statistics
Automatic Dynamic Statistics are enabled by default in
Oracle Database 12c with OPTIMIZER_DYNAMIC_SAMPLING defaulting to level 2.
Automatic Dynamic Statistics are enabled when any of the following conditions
are true:
- The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to the default valueOr The OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is set to 11 (i.e. any value other than the default or 11 disables ADS).
- Dynamic statistics is invoked through a SQL hint
- 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).
The optimizer decides whether to use dynamic statistics
based on several factors using Adaptive Query Optimization techniques like
Adaptive Plans, Statistics Feedback, SQL Plan directives etc .
Enabling
Dynamic Statistics
Automatic Dynamic Statistics (ADS) can be enabled by
setting optimizer_dynamic_sampling to non-zero value either with a
parameter or using a hint.
Example
execute immediate 'alter session set
optimizer_dynamic_sampling=4';
select
/*+ dynamic_sampling(4) */ ...
Disabling
Dynamic Statistics
Automatic Dynamic Statistics (ADS) can be disabled but
setting optimizer_dynamic_sampling to 0 either with a parameter or using a
hint.
Example
execute immediate 'alter session set
optimizer_dynamic_sampling=0';
select
/*+ dynamic_sampling(4) */ ...
Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
