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

Billy Verreynne

SQLCL is a client application. It does not have users and passwords to use.
SQLCL connects to database servers (db instances). The database typically requires a username and password for authentication. SQLCL may prompt for these credentials to supply to the database.
There are no default usernames and passwords to use in today's Oracle database. Most schemas are also locked by default when the database is created.
Ask your DBA for the details of the database and user schema to use.

User_21BG6

i have installed sqlcl-21.3.3.322.1724..but it is asked user name and password in command prompt..please give the solution

Mike Kutz

What database are you trying to connect to?

User_H3J7U

Use /nolog parameter.

thatJeffSmith-Oracle

If you don't know your username and/or password, you need to talk to the person responsible for your database. If you don't know who that is, we can't help you.

1 - 5
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,058 views