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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Naveent_2785

The below is working:

use JSON.parse() on the string and stored the output in a JSON object. Send the JSON object as a request to the REST reference

mateo91

Thanks! This helps!

Demetrius Brackens

Was javascript used to implement this solution? Do you have an example? Thanks.

mateo91

You must use the Javascript component after your transform component. Then you just add the JSON.parse() call on your JSON Object variable used in the Transform (e.g. JSON.parse(<jsonObjectVariable>);)
I ended up not even needing my Transform Component since I was receiving JSON in my request. Then I just used my Javascript component to parse what I needed in the receive payload before sending it off to the external destination.

Demetrius Brackens

Thanks for your help. This is working for me now.

Hi, I am having the same parsing issue. Can you show me example of JSON.parse(<jsonObjectVariable>);

mateo91

@User_489HG - Did you get this resolved? Let me know and I can provide an example. You can do this with both a JavaScript component and an Assign Component.

Maruthi Gottumukkala

Can you please provide me examples.

Maruthi Gottumukkala

Finally I figured it out. Appreciated for the tip. Thank you so much guys.

User_FRYTH

I am having the same issue kind of issue, in OSB 12c, when using xslt and nXSDTraslate, it shows output in binary format,
Then I have used commons-codec-1.7.jar to extract the string from binary format.
Now my rest api is not accepting the request, so I need to covert it from string to json then send it to REST api.
mateo91/User_489HG , Can you please share some example, how to do that.

1552110

After you Convert your XML to String using Translate activity, Add a JavaScript activity and add $JsonInput = JSON.parse($jsonInputString) to the Code snippet to convert the String to JSON object. In the example here $JsonInput vairiable is a JSON object input for my REST service call. Hope this helps.

User_FYU9P

Thanks for help. It is working

1 - 12
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,046 views