- 3,733,756 Users
- 2,246,818 Discussions
- 7,856,869 Comments
- 380.9K All Categories
- 2.1K Data
- 203 Big Data Appliance
- 1.9K Data Science
- 446.1K Databases
- 220.4K General Database Discussions
- 22 Multilingual Engine
- 506 MySQL Community Space
- 459 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 438 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.4K SQL & PL/SQL
- 20.8K SQL Developer
- 291.3K Development
- 6 Developer Projects
- 117 Programming Languages
- 288K Development Tools
- 96 DevOps
- 3K QA/Testing
- 645.2K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 148 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 138 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 195 Java User Groups
- 178 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
OPTIMIZER_FEATURES_ENABLE change caused massive hard parse and slowness
DB version : 18.104.22.168
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 22.214.171.124 to 126.96.36.199. But, I kept the OPTIMIZER_FEATURES_ENABLE to 188.8.131.52 as a precautionary measure.
So, 2 days back I modified OPTIMIZER_FEATURES_ENABLE parameter from 184.108.40.206 to 220.127.116.11 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 18.104.22.168. 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 22.214.171.124 , there weren't many hard parses.
ie. If changing this parameter to 126.96.36.199 caused lot of hard parses, why weren't there so much hard parses when I reverted the parameter to 188.8.131.52 ?
Unfortunately, I modified OPTIMIZER_FEATURES_ENABLE parameter from 184.108.40.206 to 220.127.116.11 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 18.104.22.168 and by 11:25 , I reverted it to 22.214.171.124 due to DB slowness.
All suggestions/findings welcome.