Insights into Statistics, Query Optimization and the Oracle Optimizer
- November 27, 2017
How are dynamic sampling results managed for SQL plan directives?
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:
- Optimizer statistics for any of the base tables in the dynamic sampling query have been re-gathered since the dynamic sampling result was created or modified.
- The number of rows of the tables in the directive is changed above a threshold since the directive was created or modified. The threshold we use is the same we use in DBMS_STATS, which is based on the STALE_PERCENT preference.
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.
Join the discussion
-
Good post!