Top 60 Oracle Blogs

Recent comments

Adaptive Query Optimization: Backport of 12.2 Configuration in

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” (from now on, PSU). The second was installed on top of “Patch 24448103 – Database Proactive Patch” ( 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 PSU. In fact, it is not compatible with the 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 PBP. It is the one called “Oracle Database 12c Release”. 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.