Previously I wrote about performance issues with 12.1 Adaptive features, specifically the large number of dynamic statistics checks, usually triggered by the existence of SQL Plan Directives. See previous articles: here (performance impact to the result cache and the fix described were slightly different than the typical experience, but the root cause was the dynamic stats queries being run) and here (turning off adaptive features results in drastic performance increases). After that post I also was able to verify in repeatable testing that only turning off the hidden parameter “_optimizer_dsdir_usage_control”, which disables the optimizer usage of dynamic statistics (sampling), had the same impact as turning off all Adaptive features with OPTIMIZER_ADAPTIVE_FEATURES.
Oracle more recently came out and said that the way Adaptive features are enabled has changed in 12.2, and it’s clear that this is an admission that dynamic stats (triggered by SPD) were being overused. Christian Antognini explains the changes well here. In short, OPTIMIZER_ADAPTIVE_FEATURES goes away and is replaced by are two new parameters, OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS. The latter controls the problematic adaptive use of dynamic stats and it is DISABLED by default. <cue choirs of happy music and the like>
Just a few days ago, Mike Dietrich posted about this change and posted links to the back-ported patches for 12.1. Oracle’s recommended approach is now to install these patches on any 12.1 upgrade or install, rather than waiting for 12.2. There’s a MOS Note on the subject, the link in his blog doesn’t seem to work, but you can find it here: Recommendations for Adaptive Features in Oracle Database 12c Release 1 (12.1) (Doc ID 2187449.1). Use the MOS note for definitive patch links since things could change, and I’m direct linking to the patches I’d need, so YMMV. However, for convenience the patches are 22652097 and 21171382 (which has a confusing name and is older, but links to one of the bugs that we want to see resolved). The one we really want that changes the parameters and disables excessive dynamic stats is 22652097 and it requires the July patch set (220.127.116.11.160719).
We just finished a patching round … guess we’ll be starting the next one earlier than usual.
Edit Fall 2017: Captured a nice graph that shows a fairly typical Peoplesoft job graphed by job runtime before and after disabling the the hidden parameter “_optimizer_dsdir_usage_control”. (From Peoplesoft job runtime data, not database metrics.) Adding it here. These are queries with literals that can’t be changed … for reasons. Looking at database AWR metrics also shows queries with the same force_matching_signature using the same plans with roughly the same data sizes running faster and with less logical IO.