Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

OPTIMIZER_FEATURES_ENABLE change caused massive hard parse and slowness

User_4ZSEXJan 25 2018 — edited Jan 26 2018

DB version : 11.2.0.4

Platform : Oracle Linux 6.8

I have a very busy OLTP production database which is a 4-node RAC DB serving the Siebel application.

This DB was upgraded 2 months back from 11.2.0.3 to 11.2.0.4. But, I kept the OPTIMIZER_FEATURES_ENABLE to 11.2.0.3 as a precautionary measure.

So, 2 days back I modified OPTIMIZER_FEATURES_ENABLE parameter from 11.2.0.3 to 11.2.0.4 across all the instances. Its a dynamic parameter BTW.

Suddenly the application became slow and impacted our business. So, around 50 minutes after i changed OPTIMIZER_FEATURES_ENABLE , I had to revert it back to 11.2.0.3. We were seeing lot of hard parses when OPTIMIZER_FEATURES_ENABLE was changed. I have few questions on this.

Question1.

When I change OPTIMIZER_FEATURES_ENABLE parameter, will oracle flush all cursors from Library Cache ? Is this why there were so many hard parses ?

Oracle documentation page on  OPTIMIZER_FEATURES_ENABLE doesn't mention any such thing.

Another weird thing I noticed is that when I reverted OPTIMIZER_FEATURES_ENABLE to 11.2.0.3 , there weren't many hard parses.

ie. If changing this parameter to 11.2.0.4 caused lot of hard parses, why weren't there so much hard parses when I reverted the parameter to 11.2.0.3 ?

Question2.

Unfortunately, I modified OPTIMIZER_FEATURES_ENABLE parameter from 11.2.0.3 to 11.2.0.4 during business hours (10:35 AM) .

Maybe if I did it during off-peak hours , hard parsing would have still happened for the most common SQLs but with less load Oracle will have parsed it without much hassle . Right ?

Question3. Was Oracle generating multiple plans as part of Adaptive cursor sharing during this time of reqression?

I am attaching AWR reports from Instance 1 and Instance 2 between 10AM to 11:30 AM . At 10:35AM , I changed the parameter to 11.2.0.4 and by 11:25 , I reverted it to 11.2.0.3 due to DB slowness.

All suggestions/findings welcome.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 23 2018
Added on Jan 25 2018
7 comments
5,061 views