Skip to Main Content
  • Questions
  • What is risk factor and performance issue to change "OPTIMIZER_ADAPTIVE_FEATURES" parameter in DB

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: March 31, 2021 - 5:23 am UTC

Last updated: March 31, 2021 - 6:35 am UTC

Version: 12.1

Viewed 100+ times

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 we 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.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.