Top 60 Oracle Blogs

Recent comments

JAN17 Proactive Bundle Patch + Adaptive Statistics control

If you have to create a new database now (I’m writing this in March 2017) for a migration of OLTP database with minimal risks of regression, I would recommend:

  • The latest patchset of Oracle Database 12cR1
  • The latest Proactive Bundle Patch
  • The two patches to get full control over Adaptive statistics

This post gives more detail about it and which patches to apply. It would be nice to have those patches merged into each bundle patch, but it is not.

dbi services 12cR2 free event

This Month, the 12cR2 was released and we immediately informed our customers about the new features that we think are important to know in order to plan when to go to 12.2 and for which projects. We started with the most important one, the Adaptive Statistics which helps to avoid all those performance issues encountered after migrating to 12cR1. We also mentioned that this new behavior has been backported to 12.2 with two patches, as explained here by Clemens Bleile:


This event was to inform about 12.2 but lot of customers waited for this to plan their 12.1 migration. Knowing the roadmap and the new features helps to plan what can wait for a stable 12cR2 (after few PSUs) and what must be migrated now to 12cR1. This is why we did everything to rollout this event as soon as possible once the main platform (Linux and Windows) were publicly available.

What to install for

Our recommendation for new installations of 12c for current migration with minimal risks of regression is

  • The latest patchset of Oracle Database 12cR1:
  • The latest Proactive Bundle Patch: JAN2017 BP
  • The two patches to get full control over Adaptive statistics: patches 22652097 and 21171382

Nothing is easy with patching, so I’ll detail how to find exactly what to install.

Latest patchset of Oracle Database 12cR1:

Now that the latest version is you cannot find anymore the on the download page.
You can download from the Patchset 21419221:

Latest Proactive Bundle Patch: JAN2017 BP

Finding the latest patch set update is easy if you follow the MOS Note Quick Reference to Patch Numbers for Database/GI PSU, SPU(CPU), Bundle Patches and Patchsets (Doc ID 1454618.1)

You will download for Linux or for Windows.

Adaptive statistics: patches 22652097 and 21171382

Then you have to find the two patches to apply them on top of the JAN17 ProactiveBP.

The first one is for separate parameters to enable adaptive plans and adaptive statistics separately: and you can find it for Oracle Proactive BP

The second one is for dbms_stats preference to control statistics extensions creation and you will have two suprises when following the link:
The name is AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY because the change has been merged with some other changes on concurrent statistics
It is available only for (listed in the non-chronological order of the platform list on MOS):

  • Oracle Proactive BP
  • Oracle
  • Oracle
  • Oracle Proactive BP
  • Oracle Proactive BP

If you want to go further, you have to open a SR, provide an opatch lsinventory (because it seems that Oracle Support Engineers are not able to get the lsinventory for the latest Proactive BP – the recommended one). And this is where the nightmare starts. The lastest we have here is for JAN16 Proactive Bundle Patch – Proactive BP.

I can’t wait for a relevant answer from MOS support engineers, so I got to look at the patch. It is very simple change actually.In DBMS_STATS it has to check whether AUTO_STAT_EXTENSIONS is ON before creating the column group. This is all in prvtstat.plb and if we are lucky there were no changes on it since the JAN16.

Before trying it, we can check conflicts in MOS. Here are the 3 patches I would like to apply, in their most recent release for Linux:


Yes, this is a nice feature of My Oracle Support: you can analyze the conflicts online.

The result of conflict analysis shows that we are lucky:

Ready now to download the files:

So the last patch we need,, can be downloaded in its latest Proactive BP version, even if it is 1 year old. And don’t worry about its name:

datapatch and verification

Don’t forget to run datapatch on your databases to be sure that the dictionary is patched.

SQL Patching tool version Production on Mon Mar 27 09:18:47 2017
Copyright (c) 2012, 2017, Oracle. All rights reserved.
Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done
Adding patches to installation queue and performing prereq checks...done
Installation queue:
Nothing to roll back
The following patches will be applied:
Installing patches...
Patch installation complete. Total patches installed: 3
Validating logfiles...done
SQL Patching tool complete on Mon Mar 27 09:21:33 2017

And then connect to check the new Adaptive Statistics behavior:

SQL> show parameter optimizer_adaptive
------------------------------------ ----------- ------------------------------
optimizer_adaptive_plans boolean TRUE
optimizer_adaptive_reporting_only boolean FALSE
optimizer_adaptive_statistics boolean FALSE
SQL> select dbms_stats.get_prefs('AUTO_STAT_EXTENSIONS') from dual;

Summary for new install

Don’t install a 12c database with only the software and documentation that was released 3 years ago. Lot of migration feedback has improved the stability of such migration, and this is implemented in patchset updates and MOS notes.
A good source of information is Mike Dietrich blog (the best source you can find to benefit from lot of migration projects experience):

Then, get the latest recommended software.

  1. The patchset: for your platform
  2. The patchset update for Unix/Linux
    or for Windows
  3. The Adaptive Statistics instance parameters: for Oracle Proactive BP
  4. The Adaptive Statistics stat preference for Oracle Proactive BP

Don’t forget to run datapatch on all databases, even the newly created ones.


Cet article JAN17 Proactive Bundle Patch + Adaptive Statistics control est apparu en premier sur Blog dbi services.