After having several issues with adaptive features on 12.1 databases, in some cases, the quickest way to solve problems was to simply set ‘optimizer_adaptive_features’ parameter to FALSE.
In 12.2, this parameter became obsolete and was replaced with two different parameters : ‘optimizer_adaptive_plans’ and ‘optimizer_adaptive_statistics’. Therefore, it is recommended to apply Patch 22652097 (PROVIDE SEPARATE CONTROLS FOR ADAPTIVE PLANS AND ADAPTIVE STATISTICS FEATURES) in order to have the same behaviour in 12.1.
I read thoroughly the README file of Patch 22652097 and applied it on my 12.1 homes. But when I tried to start the databases afterwards, some of them returned the following errors :
ORA-01078: failure in processing system parameters
ORA-32003: error occured processing parameter 'optimizer_adaptive_features'
LRM-00101: unknown parameter name 'optimizer_adaptive_features'
Of course, I forgot to remove the newly obsolete ‘optimizer_adaptive_features’ parameter in their spfiles. Thus, I quickly fixed the incriminated spfiles and setted, if needed, the two new parameters ‘optimizer_adaptive_plans’ and ‘optimizer_adaptive_statistics’. Finally my databases started correctly.
I now have to apply this patch on other servers, and I surely won’t forget to check if some 12.1 databases have this parameter specified in spfile :
select VALUE from V$SPPARAMETER where NAME='optimizer_adaptive_features' and ISSPECIFIED='TRUE' ;
The missing instruction in the README file of Patch 27162953 resulted in the issue that I ran into.
Thanks to the blog* written by Flora on 10/16/2017, I was able to resolve the issue.
*Patch 22652097 in 12.1 makes optimizer_adaptive_features parameter obsolete
https://floobar0.wordpress.com/2017/10/16/patch-22652097-in-12-1-makes-optimizer_adaptive_features-parameter-obsolete/
-Sean L
1/22/2018
LikeLike
Hi Sean ! Happy to help 🙂
LikeLike
Pingback: BP and Patch 22652097: set optimizer_adaptive_statistics to FALSE explicitly or it might not work! | DBA survival BLOG