You Asked
We are going to introduce new access control system by integrating fingerprint sensors to our production database. As per vendor (Access control system) suggestion for smooth functionality of Access Control System, we need to change the parameter value "OPTIMIZER_ADAPTIVE_FEATURES" from TRUE to FALSE.
Has it any adverse effect on database ? What is risk factors on database due to changed it .
4 node ORACLE 12.1 RAC database on Linux platform.
and Connor said...
Rather than doing that, I would look at applying patch 22652097
This converts the 12.1 option (one parameter to control all adaptive features) to two parameters (which is how it is from 12.2 onwards)
optimizer_adaptive_plans
- change a plan *during* execution (eg from nested loop to hash join)
optimizer_adaptive_statistics
- remember an execution and potentially change the plan *next* execution
The first adaptive optimization has generally been seen as a good one because it avoids a current plan spiralling out of control. The second one is perhaps less popular because it tends to reduce the amount plan stability over time.
And from 12.2, the defaults reflect that, ie
opptimizer_adaptive_plans=TRUE
optimizer_adaptive_statistics=FALSE
So I would look at heading down that path, which also then gets you closer to what you'll see in 12.2, 18, 19 and beyond.
Is this answer out of date? If it is, please let us know via a Comment