Starting in Oracle Database 12c, the optimizer automatically decides whether dynamic statistics are useful and which sample size to use for all SQL statements.
In earlier releases, dynamic statistics were called dynamic sampling.
The primary factor in the decision to use dynamic statistics is whether available statistics are sufficient to generate an optimal plan. If statistics are insufficient, then the optimizer uses dynamic statistics.
Automatic dynamic statistics are enabled when the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter is not set to 0. By default, the dynamic statistics level is set to 2.
In general, the optimizer uses default statistics rather than dynamic statistics to compute statistics needed during optimizations on tables, indexes, and columns.The optimizer decides whether to use dynamic statistics based on several factors, including the following:
• The SQL statement uses parallel execution.
• A SQL plan directive exists.
The following diagram illustrates the process of gathering dynamic statistics.
As shown in Figure 10-2, the optimizer automatically gathers dynamic statistics in the following cases:
• Missing statistics
When tables in a query have no statistics, the optimizer gathers basic statistics on these tables before optimization. Statistics can be missing because the application creates new objects without a follow-up call to DBMS_STATS to gather statistics, or because statistics were locked on an object before statistics were gathered.
In this case, the statistics are not as high-quality or as complete as the statistics gathered using the DBMS_STATS package. This trade-off is made to limit the impact on the compile time of the statement.
•Insufficient statistics
Statistics can be insufficient whenever the optimizer estimates the selectivity of
predicates (filter or join) or the GROUP BY clause without taking into account correlation between columns, skew in the column data distribution, statistics on expressions, and so on.
Extended statistics help the optimizer obtain accurate quality cardinality estimates for complex predicate expressions. The optimizer can use dynamic statistics to compensate for the lack of extended statistics or when it cannot use extended statistics, for example, for non-equality predicates.
The database does not use dynamic statistics for queries that contain the AS OF clause.
