I got a chance to discuss some of the issues we have been facing in this area with the CBO team at Oracle OpenWorld last week, and learned about some of the changes they have planned for the future. Please don’t take this as fact, this is only based on my recollection of from an informal chat.
The parameter OPTIMIZER_ADAPTIVE_FEATURES will be split into two parts, OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS. Franck Pachot has a blog post which goes into more details.
Worth noting is the fact that OPTIMIZER_ADAPTIVE_STATISTICS will be set to false by default. This change will be implemented in 12.2, but also be made available as a patch for 12.1 (I believe again with the same defaults).
With OPTIMIZER_ADAPTIVE_STATISTICS set to false, SQL Plan Directives will still be created when assumptions made at parse time are discovered to be wrong at execution time, however they will not automatically be used to trigger dynamic sampling or creation of column groups. I imagine that the advantage for this is that these directives could be manually monitored, and root causes could be fixed by the DBA.
Specific to 12.2, if dynamic sampling is triggered, it will be a completely new mechanism for caching results instead of the current mechanism reliant on the result cache. Results from dynamic sampling queries will be stored persistently (not lost after instance restart and available to all nodes in a RAC cluster). There will be a mechanism to ensure that this data can be refreshed when it gets stale (somewhat similar to the existing mechanism for incremental statistics refresh where the database stores number of changed rows in base tables).
I am a little surprised at what appears like a partial back-track in this area from Oracle, however based on the number of issues we have found in this area I think this is the right thing to do. My guess is that many customers have just been disabling ALL adaptive features when problems have been encountered during upgrade. Separating the features out and providing more sensible default settings to encourage more customers to use at least some of these features should be beneficial to all.
2 thoughts on “Upcoming Adaptive Query Optimization Changes”
Thanks for the info.
Oracle have now released information of the back-porting of the parameter change to 12.1 as MOS note 2187449.1 (https://support.oracle.com/epmos/faces/DocContentDisplay?id=2187449.1)
Thanks for that