{"id":1668,"date":"2018-02-20T18:26:45","date_gmt":"2018-02-20T16:26:45","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=1668"},"modified":"2020-08-18T16:12:36","modified_gmt":"2020-08-18T14:12:36","slug":"bp-and-patch-22652097","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/bp-and-patch-22652097\/","title":{"rendered":"BP and Patch 22652097: set optimizer_adaptive_statistics to FALSE explicitly or it might not work!"},"content":{"rendered":"

Update 14.03.2018: After some exchanges with Nigel Bayliss, the behaviour described here has been filed as unpublished bug 27626925: OPTIMIZER ADAPTIVE STATS DEFAULT FALSE NOT HONORED WHEN ENABLED IN OCT OR JAN BP. It will be fixed starting with April’s bundle patch.<\/p><\/blockquote>\n

 <\/p>\n

According to Nigel’s blog post:<\/p>\n

The Oracle 12.1.0.2 October 2017 BP and the Adaptive Optimizer <\/a><\/p>\n

if you installled the patch 22652097 prior to apply the Bundle Patch 171018, the BP apply in the database should recognize that the patch was already in place and keep it activated. This is done through the fix control 26664361.<\/p>\n

When fix_control 26664361:0<\/strong> -> Patch 22652097 is not enabled: the parameter optimizer_adaptive_features (OAF) works<\/p>\n

When fix_control 26664361:1<\/strong> -> Patch 22652097 is enabled; optimizer_adaptive_features is discarded and the two new parameters have the priority: optimizer_adaptive_plans (OAP) and optimizer_adaptive_statistics (OAS).<\/p>\n

But at my customer, I had another behavior.<\/p>\n

My patching story might be very similar to yours!<\/strong><\/p>\n

When I started upgrading my customer’s database to 12c in early 2015, I experienced very soon the infamous problems with SQL Plan Directives (SPD) and Adaptive Dynamic Sampling (ADS) that I described in my paper:\u00a0ADAPTIVE FEATURES OR: <\/a>HOW I LEARNED TO STOP WORRYING AND TROUBLESHOOT THE BOMB<\/a> .<\/p>\n

Early fixes<\/strong><\/p>\n

When I was new to the problem, the quick fix for the problematic applications was to set OAF to FALSE.<\/p>\n

Later, I discovered some more details and decided to opt for setting:<\/p>\n

_optimizer_dsdir_usage_control=0<\/pre>\n
In other cases, I disabled the specific directives that were causing problems.<\/p>\n
But many databases did not have so many problems, and I left the defaults.<\/p>\n
Patch 22652097 on top of BP170718\u00a0<\/strong><\/p>\n
At some point, me and my customer decided to apply the fix 22652097, on top of BP170718 that was our current patch level at that time.<\/p>\n
The patch installation on a test database was complaining about the optimizer_adaptive_feature set: this parameter was not used anymore. This issue is nicely explained by Flora in her post Patch 22652097 in 12.1 makes optimizer_adaptive_features parameter obsolete.<\/a><\/p>\n
In order to apply that patch on the remaining databases, we did:<\/p>\n