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?