Adpative Features Patches with Oracle Peoplesoft

I’ve met David Kurtz, an Oracle ACE Director working for Accenture’s Enkitec division in the UK earlier this year on the OUGN Conference‘s boat trip from Oslo to Kiel and return. We set across the table at dinner. And of course we started discussing “Oracle”. David went to my talk about “How to ensure Performance Stability” before. He did ask me questions about the adaptive features patches I was recommending in my talk:

The Adaptive Feature Patches

I explained to him that he will have to request these specific two fixes unfortunately for every Proactive Bundle Patch over and over again. But based on our experience these two patches fix most of the nasty issues with and around “adaptive” in Oracle 12.1.0.2.

• • The patch# 22652097 introduces the two parameters OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS. In addition removes the parameter OPTIMIZER_ADAPTIVE_FEATURES.
  • The patch# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.

Experience with Oracle Peoplesoft

Now a while later David has mailed me with his first-hand experience. And he shared his blog post with me.

In short (I did ask for David’s permission to quote him):

“We have had the patches in place on our supercluster for a few weeks, and I have been working on tuning the GL reporting.
It has been positive.  It hasn’t fixed all our problem, but it certainly hasn’t added to them.  I have seen plans switching mostly from NL to Hash, but I have not found a case where I think that was a bad decision. I just wanted to say thank you for your assistance with this. 
I shall certainly be recommending this as standard practice for PSFT on 12cR1. “

But you better should read the entire blog post:

http://blog.psftdba.com/2017/06/peoplesoft-adaptive-query-optimization.html

Further Information

Please find Required Interim Patches and PSFT recommended Oracle 12c init.ora parameters for the Oracle Database with PeopleSoft in MOS Note:1100831.1 – it got updated for Oracle 12.1.0.2:

“Upon upgrades to Oracle Database 12c, PeopleSoft customers have found various performance regressions in what appears related to the new Oracle 12c Optimizer Adaptive Feature (OAF). OAF is enabled by default in 12c via the init.ora parameter: optimizer_adaptive_features = TRUE. Through Oracle Support suggestions for bugs and service requests filed or happenstance, many customers have worked around these performance issues by setting the following init.ora parameter to false.

More details can be found at Bug 21293606 OPTIMIZER_ADAPTIVE_FEATURES CAUSING PERFORMANCE REGRESSIONS

We recommend overriding the default from optimizer_adaptive_features = TRUE to:
optimizer_adaptive_features = FALSE

New information for 12.1 and Optimizer Adaptive Features (as of May 2017)

Oracle has provided new patches for Oracle 12.1.0.2.0 that backport the 12.2 OAF granular settings to Oracle 12.1.

Adopting OAF 12.2 defaults in Oracle 12.1.0.2.0 may be done by applying the following patches for your version and platform:

Patch 22652097 splits the parameter optimizer_adaptive_features into two, as was done in Oracle 12.2, and disables adaptive statistics.

Patch 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STAT_EXTENSIONS is set to ON.

For PeopleSoft customers on Oracle 12.1, we now recommend applying these two patches. Please see Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics and 12c SQL Performance) (Doc ID 2312911.1) for details.”

–Mike