Forum Stats

  • 3,728,698 Users
  • 2,245,675 Discussions
  • 7,853,706 Comments

Discussions

What is risk factor and performance issue to change "OPTIMIZER_ADAPTIVE_FEATURES" parameter in DB

MOJIBUL HOQUE
MOJIBUL HOQUE Member Posts: 159 Blue Ribbon

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.

Tagged:

Best Answer

  • JohnWatson2
    JohnWatson2 Member Posts: 4,228 Bronze Crown
    Accepted Answer

    Discussing failings in the optimizer can be an invitation for flaming, I hope my comments do not get people all excited.

    In release 12.1, a significant minority of sites (0.1%? 49%? No idea) experienced performance problems that appeared to be related to use of SQL Directives, which is part of the Adaptive Features (my own experience of this was one system where the parsing workload appeared to become excessive). This has led some people to say "The Adaptive Features are RUBBISH and must ALWAYS be disabled". This is perhaps an unthinking reaction based on being unwilling to investigate why the problem has occurred in that particular situation. This springs to mind, https://en.wikipedia.org/wiki/Don%27t_throw_the_baby_out_with_the_bathwater

    There is a fuller description and rather better solution here

    Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics & 12c SQL Performance) (Doc ID 2187449.1)

    or you could discuss with your vendor why they are making such a crude recommendation. At the very least, you could implement it at the session level for their product rather than for the whole RAC.

Answers

  • JohnWatson2
    JohnWatson2 Member Posts: 4,228 Bronze Crown
    Accepted Answer

    Discussing failings in the optimizer can be an invitation for flaming, I hope my comments do not get people all excited.

    In release 12.1, a significant minority of sites (0.1%? 49%? No idea) experienced performance problems that appeared to be related to use of SQL Directives, which is part of the Adaptive Features (my own experience of this was one system where the parsing workload appeared to become excessive). This has led some people to say "The Adaptive Features are RUBBISH and must ALWAYS be disabled". This is perhaps an unthinking reaction based on being unwilling to investigate why the problem has occurred in that particular situation. This springs to mind, https://en.wikipedia.org/wiki/Don%27t_throw_the_baby_out_with_the_bathwater

    There is a fuller description and rather better solution here

    Recommendations for Adaptive Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive Statistics & 12c SQL Performance) (Doc ID 2187449.1)

    or you could discuss with your vendor why they are making such a crude recommendation. At the very least, you could implement it at the session level for their product rather than for the whole RAC.

  • EdStevens
    EdStevens Member Posts: 28,052 Gold Crown

    Admitting I know nothing about your database, but if the fingerprint sensor is just for access control (validating logon credentials) I can't see how optimizer directives even come into play.

    I agree with JohnWatson2 - take it up with the vendor. I've had a mixed bag with that sort of thing. At the extreme, I had a vendor say that while they support their app on oracle, they don't recommend it "because oracle can't handle more than three concurrent connections". Yes, they really said/believed that. I've had other vendors tell me things that were less rediculous, but equally wrong and mis-informed. Sometimes you have to know more than the vendor. Sometimes that's not a high bar to meet.

    MOJIBUL HOQUE
Sign In or Register to comment.