Finally … the two most important fixes for Adaptive Features are included in Oracle 220.127.116.11 October 2017 BP (Bundle Patch). I’m absolutely happy about this. Almost all of my customer and many folks outside I’m dealing with on a regular basis had to request and wait for merge patches over and over again. And we all knew – also based on the experience of the Real World Performance Team – that these two patches are so important to stabilize Oracle 18.104.22.168 production systems.
You may find this ALERT on MyOracle Support:
Which fixes are included?
Besides some other fixes these important fixes are included:
- The patch for bug# 22652097 introduces the two parameters
OPTIMIZER_ADAPTIVE_STATISTICS, and in addition removes the parameter
- The patch for bug# 21171382 disables the automatic creation of extended statistics unless the optimizer preference AUTO_STATS_EXTENSIONS is set to ON.
- And in addition the patch for bug# 20243268 – Suboptimal execution plan for SQL that has cardinality mis-estimate directives auto-added which interacts with the fix for bug# 21171382
Please see this blog post for details and further links about the patches, and why you need to apply them – or better use the October 2017 BP: Enabling Oracle 12.2 Adaptive Features in Oracle 22.214.171.124.
Where are these fixes included?
The fixes are included in the Oracle Database 126.96.36.199 Proactive Bundle Patch (BP). They are NOT included in the PSUs. And they are not included in Oracle 12.2 RUs as there’s no need to include them. This is another reason why you should switch from PSUs to BPs whenever possible.
Interestingly enough the fixes were included already in the Windows Bundle Patches since the August 2017 BP.
Please note that these fixes are disabled by default. But with the new Automatic Fix Control Persistence Framework once you enable them they’ll stay enabled when you apply subsequent Bundle Patches.
This patch is supported by 12.2 adaptive features behavior in 188.8.131.52, but these changes are kept by default off. See My Oracle Support Document 2312911.1 Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics & 12c SQL Performance) (Doc ID 2312911.1) for more information about how to use 12.2 adaptive features behavior in 184.108.40.206 provided by the Database Proactive Bundle Patch.
Further information is provided in MOS Note: 2312911.1 – Recommendations for Adaptive Features in Oracle Database 12.1.
See also David Kurtz‘ (Accenture – Enkitec) experiences with Oracle PeopleSoft installations using the Adaptive Features fixes: https://mikedietrichde.com/2017/07/06/adpative-features-patches-oracle-peoplesoft/
What is your task now?
It’s very “simple”:
- Apply Database Proactive Bundle Patch 220.127.116.11.171017 Patch 26635880.
- Remove a potentially existing parameter
optimizer_adaptive_featuresfrom your SPFILE.
- Switch on the patches. Please see MOS Note: 2312911.1 for detailed options.
Either add to your init.ora/spfile:
alter system set "_fix_control"='26664361:7','16732417:1','20243268:1'scope=both;
Be VERY careful not delete other fix control entries if any exist. This command will overwrite existing _fix_control settings.
- In addition OPTIONALLY you may enable also the other optimizer fixes included.
Please see DBMS_OPTIM_BUNDLE – only available in Oracle 18.104.22.168 on how to display and enable the other fixes in addition.
- Restart the database. This is required in order to enable the fixes correctly.
- Finally run the script
connect / as sysdba
You should be good by using the defaults for
Be aware if you had
optimizer_adaptive_features=FALSE before applying this BP you may encounter now plan changes due to
optimizer_adaptive_plans=TRUE by default. I’d recommend to test and keep an eye on it but giving it a try. Actually two of my Oracle 12c reference customers have exceptionally good experiences with the “plan” part of the adaptive features.
And one last thing:
If you have an Oracle Multitenant deployment with Oracle 22.214.171.124 and plan to enable the highly recommended “adaptive” fixes AND the fixes under control of DBMS_OPTIM_BUNDLE you must edit your init.ora/SPFILE as otherwise the two calls overwrite each other vice versa. I’ll file a bug these days.