At DOAG this year, the question of how Oracle Database 12c Release 2 manages dynamic statistics for SQL plan directives came up a number of times. SQL plan directives tell the Oracle Optimizer when to use dynamic sampling (DS) to improve cardinality estimates. When queries are parsed and relevant SQL plan directives exist, DS queries sample data to obtain better cardinality estimates (assuming that optimizer_adaptive_statsitics is TRUE).
In Oracle Database 12c Release 1, DS queries included a RESULT_CACHE hint to store the results in the server result cache to reduce the overhead of continuously executing them against application data. This didn’t always work well for systems with very high hard-parse rates and significant data-churn, so in Oracle Database 12c Release 2 the result cache is no longer used for this purpose. Instead, DS query results are stored in the data dictionary. They can be seen as follows:
SELECT * FROM DBA_SQL_PLAN_DIRECTIVES WHERE TYPE = 'DYNAMIC_SAMPLING_RESULT';
In Oracle Database 12c Release 2, the optimizer checks to see whether the result of a particular DS query is available in the data dictionary before it attempts to execute it and fetch the results. Executing DS queries consumes system resources, even though the database uses sampling to reduce the overhead. 'Caching' reduces the overhead further.
The optimizer needs up-to-date DS query results to derive good cardinality estimates, but since the DS queries sample application data that's subject to change, the obvious question is: “how do we make sure that the results held in the data dictionary are up-to-date?”
Prior to Oracle Database 12c Release 2, the result cache made things easy for the optimizer because staleness is managed by the database. The optimizer itself didn’t need to do anything special to keep the DS results up-to-date. Now that the results are cached in the data dictionary, the optimizer has to take care of things itself.
How does it do it?
It’s pretty intuitive. The optimizer updates (at query parse time) the dynamic sampling result that's stored in the data dictionary if:
When a dynamic sampling result is stored in DBA_SQL_PLAN_DIRECTIVES, the CREATED column will record the creation time and, initially, LAST_MODIFIED will be NULL. When a new dynamic sampling result is obtained by a DS query, the LAST_MODIFIED column is updated accordingly.