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!

11gR2 cursor_sharing=force and adaptive cursor sharing

ji liFeb 13 2013 — edited Feb 13 2013
From what I'm understanding with Oracle 11gR2, ACS (adapter cursor sharing) is enabled when setting CURSOR_SHARING=FORCE.

In our OLTP environment, we saw that our soft_parse hit ratio was down near 50% so I set CURSOR_SHARING=FORCE.
(This was originally set to EXACT.)

Initially, everything seems good, and when I check our soft_parse hit ratio, it improved to 99.25%.
Wow! What an improvement.

But, it seems the application queries are suffering and running slower. Why?

i check the explain plans, and for example, I see:
select statement
  count stopkey
     view
        table access by index rowid   (highest cost)
           index full scan descending
We do have histograms in place (from what I can tell by selecting from the DBA_TAB_HISTOGRAMS table).
We also have optimizer_use_sql_plan_baselines=TRUE.
We are using AMM so shared_pool=0, but shared_pool_reservied_size=1G so this should not be an issue.

Any ideas?
This post has been answered by Mohamed Houri on Feb 13 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 13 2013
Added on Feb 13 2013
16 comments
5,773 views