Tuesday, April 26, 2016

Optimizer Dynamic Sampling / Automatic Dynamic Statistics

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