I finally managed to install and test patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES). Actually, I installed and tested two patches. The first was installed on top of “Oracle Database Patch 23054246 – Database Patch Set Update 12.1.0.2.160719” (from now on, 12.1.0.2.160719 PSU). The second was installed on top of “Patch 24448103 – Database Proactive Patch 12.1.0.2.161018” (12.1.0.2.161018 PBP).
From a functional point of view, both patches provides the 12.2 configuration in 12.1 as I describe it in this post. In fact, the behavior of setting the (un)documented initialization parameters as well as all the default values are the same. I was able to find only two differences between 12.2 and a patched 12.1.
- In a patched 12.1 it is not possible to successfully executed an ALTER SESSION statement that modifies the OPTIMIZER_ADAPTIVE_FEATURES initialization parameter (it is in 12.2). If you try to do it, the database engine raises an ORA-02248 (invalid option for ALTER SESSION).
- In a patched 12.1 the AUTO_STAT_EXTENSIONS preference is not available (it is in 12.2). To have it, you have to install patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY). However, note that I was only able to install and test it with the 12.1.0.2.160719 PSU. In fact, it is not compatible with the 12.1.0.2.161018 PBP. In my opinion, this is a major issue! I really do not understand why it is not part of patch 22652097. UPDATE 2016-12-22: as you can read in the comments, Stefan pointed out that there is a version of patch 21171382 that is compatible with the 12.1.0.2.161018 PBP. It is the one called “Oracle Database 12c Release 12.1.0.2.13DBEngSysandDBIM”. I was able to successfully test it.
My expectation is that, in the future, Oracle immediately provides patch 22652097 for all PSU and PBP. Actually, I even hope that Oracle includes it in future PBP. If this expectation will be fulfilled, I see no reason for not applying it. Otherwise, I would advise to stick with the undocumented initialization parameters.
Update 2017-10-18: The October 2017 PBP includes the patches 22652097 and 21171382. By default they are disabled. To enable them you have to set fix control 26664361. For detailed information refer to Oracle Support note 2312911.1.
Hey Chris,
patch #21171382 for version 12.1.0.2.13DBBP is also valid for 12.1.0.2.161018 (PBP), because of 12.1.0.2.13 is a sub-set of 12.1.0.2.161018. The Oracle patch site itself does not tell it, but it works – i just did it last week :-)
By the way – is the weird cardinality / statistics feedback also back-ported? I could not test it until yet, but with 12.2 cardinality feedback is not used on the 2nd execution – it is used on 3rd execution. This looks like a bug in 12.2 (to me) as it is described differently in the 12.2 optimizer white paper.
Regards
Stefan
Hi Stefan
First of all, thank you very much for the feedback. I updated the post.
About the statistics feedback issue, do you have a test case? With the one I use for my tests it works as expected in both 12.2 and the patched 12.1.
Best,
Chris