8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Dynamic Statistics in Oracle Database 12c Release 1 (12.1)

Oracle Database 10g 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. Dynamic sampling was controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter 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 a default value of "2".

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. Both the name change in the documentation and the additional sampling level have been back-ported to Oracle 11gR2 (11.2.0.4) subsequently, so with the exception of the references to SQL plan directives, the functionality described here is common to 11gR2 (11.2.0.4) also.

Control of the adaptive optimizations has altered in 12.2 and the change is available in 12.1 with the correct patches. See here.

Related articles.

Why Use Dynamic Statistics

Dynamic statistics can be beneficial in the following cases.

When to Sample

The optimizer will attempt to use default database statistics in preference to dynamic statistics, but the following situations will trigger automatic sampling to gather dynamic statistics.

In these cases, recursive SQL is used to sample the data and generate dynamic statistics, which are persisted and sharable between SQL statements having similar patterns.

Controlling Dynamic Statistics

As mentioned previously, dynamic statistics can be controlled by the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter and the DYNAMIC_SAMPLING hint. The functionality associated with the individual settings is described here. 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;

In the majority of cases you should not need to change the default value of "2". This is possibly more true in Oracle 12c because of the introduction of SQL plan directives and how they work. If the optimizer identifies misestimates in the cardinality of operations or degree of parallelism (DOP), it can create SQL plan directives to force dynamic sampling in the short term. The presence of SQL plan directives influence the way DBMS_STATS gathers statistics, which potentially fixes the root cause of the problems in the database statistics, making the SQL plan directives and therefore dynamic sampling no longer necessary.

Reuse of Dynamic Statistics

As pointed out by Jonathan Lewis in his Re-optimization blog post, dynamic statistics can be stored in the SGA as OPT_ESTIMATE hints in the V$SQL_REOPTIMIZATION_HINTS view. Both the view and the OPT_ESTIMATE hint are undocumented. The storage of dynamic statistics mean resampling of the statistics is not necessary if the same statement is parsed again.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.