DB version : 184.108.40.206
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 220.127.116.11 to 18.104.22.168. But, I kept the OPTIMIZER_FEATURES_ENABLE to 22.214.171.124 as a precautionary measure.
So, 2 days back I modified OPTIMIZER_FEATURES_ENABLE parameter from 126.96.36.199 to 188.8.131.52 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 184.108.40.206. We were seeing lot of hard parses when OPTIMIZER_FEATURES_ENABLE was changed. I have few questions on this.
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 220.127.116.11 , there weren't many hard parses.
ie. If changing this parameter to 18.104.22.168 caused lot of hard parses, why weren't there so much hard parses when I reverted the parameter to 22.214.171.124 ?
Unfortunately, I modified OPTIMIZER_FEATURES_ENABLE parameter from 126.96.36.199 to 188.8.131.52 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 184.108.40.206 and by 11:25 , I reverted it to 220.127.116.11 due to DB slowness.
All suggestions/findings welcome.