ADAPTIVE STATISTICS and DYNAMIC STATISTICS

ADAPTIVE STATISTICS

The quality of the execution plans determined by the optimizer depends on the quality of the statistics available. However, some query predicates become too complex to rely on base table statistics alone and the optimizer can now augment these statistics with adaptive statistics.

The use of adaptive statistics can extend SQL statement parse times, which can become an overhead in systems that have high hard parse rates. In addition, SQL execution plans are likely to be more dynamic and less consistent. This feature is disabled by default from Oracle Database 12c Release 2 onwards. Systems that have low hard parse rates and complex queries (particularly ad-hoc queries) may benefit from this feature by setting the database initialization parameter optimizer_adaptive_statistics to TRUE.

DYNAMIC STATISTICS

During the compilation of a SQL statement, the optimizer decides if the available statistics are sufficient to generate a good execution plan or if it should consider using dynamic sampling. Dynamic sampling is used to compensate for missing or insufficient statistics that would otherwise lead to a very bad plan. For the case where one or more of the tables in the query does not have statistics, dynamic sampling is used by the optimizer to gather basic statistics on these tables before optimizing the statement. The statistics gathered in this case are not as high a quality (due to sampling) or as complete as the statistics gathered using the DBMS_STATS package.

Beginning with Oracle Database 12c Release 1, dynamic sampling was enhanced to become dynamic statistics. 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. Also, from Oracle Database 12c Release 1, a new level 11 has been introduced for the initialization parameter OPTIMIZER_DYNAMIC_SAMPLING. Level 11 enables the optimizer to automatically decide to use dynamic statistics for any SQL statement, even if all basic table statistics exist. The optimizer bases its decision to use dynamic statistics on the complexity of the predicates used, the existing base statistics, and the total execution time expected for the SQL statement. For example, dynamic statistics will kick in for situations where the optimizer previously would have used a guess, such as queries with LIKE predicates and wildcards.

The default dynamic sampling level is 2, so it’s likely that when set to level 11, dynamic sampling will kick-in much more often and will increase hard parse times.

AUTOMATIC RE-OPTIMIZATION

During the first execution of a SQL statement, an execution plan is generated as usual. During optimization, certain types of estimates that are known to be of low quality (for example, estimates for tables which lack statistics or tables with complex predicates) are noted, and monitoring is enabled for the cursor that is produced. If feedback monitoring is enabled for a cursor by the system, cardinality estimates in the plan are compared to the actual cardinalities seen during execution. If estimates are found to differ significantly from the actual cardinalities, then the optimizer looks for a replacement plan on the next execution. The optimizer will use the information gathered during the previous execution to help determine an alternative plan. The optimizer can re-optimize a query several times, each time learning more and further improving the plan. Oracle Database 19c supports multiple forms of re-optimization.