Skip to Main Content
Forums

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

unknown-7404 
2781564 wrote:




Oracle CRM is blocked by Java 8 due to "medium" security being removed.  Possibly this is because the application requests all permissions.  Is there a reconfiguring that can be done to allow it to run/launch in Java "high" security mode.  The desktop administrators just upgraded all call center desktops to Java 8 (1.8_20) and Contact Center and other "Java forms" based application components are failing to launch.




We are working with the Admin/Security teams to see if our site can be added to the site Exceptions list but this is a lengthy process and hope we can configure CRM to work with the higher "Java" security requirement.

This is not really a Java 8 question. You need to post in a CRM forum or open an SR with Oracle support via your MOS account.

As you already noted Java 8 "medium" security was removed.

https://www.java.com/en/download/help/jcp_security.xml

Starting with Java 8 Update 20, the Medium security level has been removed from the Java Control Panel. Only High and Very High levels are available. 


The exception site list provides users with the option of allowing the same applets that would have been allowed by selecting the Medium option but on a site-by-site basis therefore minimizing the risk of using more permissive settings.

Seems like someone in your org approved the Java update without properly testing ALL of the possible affected apps.

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

Post Details

Locked on Feb 23 2018
Added on Jan 25 2018
#general-database-discussions
7 comments
5,077 views