Introduction
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 plan.
The OPTIMIZER_DYNAMIC_SAMPLING controlled dynamic Sampling
initialization parameter.
They can be initialized at instance or session level, or for individual queries using the
DYNAMIC_SAMPLING hint.
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 sensitivities where available statistics are missing or may lead to wrong estimations.
We are estimating statistics for tables and indexes with missing statistics.
I am estimating statistics for tables and indexes with out of date statistics.
At compile-time Oracle determines if dynamic Sampling would improve query performance.
If so, it issues recursive statements to estimate the necessary statistics.
Dynamic sampling benefits:
Dynamic Sampling can be beneficial in the following ways overall query execution time.
The 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 current statistics
Database.
Statistics are considered when 10% or more of the rows in the table have changed since the statistics were last gathered.
Stale statistics can affect carnality 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 plan.
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 necessary.
Parallel Execution
Parallel execution is typically used to speed up long-running processes.
For a long-running process, the time associated with dynamic sampling 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 optimal.
SQL Plan Directives
The presence of one or more usable SQL plan directives will trigger the Sampling of dynamic statistics.
SQL plan directives 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 DYNAMIC_SAMPLING hint.
The following examples show how to control dynamic statistics at system, session and statement level.
–System-level. Don’t do this!
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=11;
–Session level.
ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING=11;
— 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.
Summary
This Post described the Dynamic Sampling to allow the optimizer to gather in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions
