Reuse statements, cursor_sharing=force

Reuse statements

select * from emp where ename = :EMPNAME


select * from v$parameter where name like '%cursor_sharing%';

cursor_sharing=force

The kernel parameter cursor_sharing defaults to EXACT, but can be set to FORCE or SIMILAR in order to have the database convert literals to bind variables before parsing the statement.

Setting cursor_sharing =force greatly reduced the contention on the library cache and reduced CPU consumption. The end users reported a 75 percent improvement in overall performance.

Our queries use a lot of bind variables, by design. In recent benchmarks, CURSOR_SHARING=FORCE was helpful for benchmark runs reducing query execution time by several times.
However, for a specific query involving bind variables, using CURSOR_SHARING=FORCE was very much slower than if executed while CURSOR_SHARING=EXACT.


This is probably a bug. Cursor_sharing = force is not really reliable. We hash the value of the statement and then go looking for a matching hash, regardless of how many users are on the system or how many times the statement has been issued or even what the statement looks like. Force means force, regardless of what is going on, we are going to force the
sharing of cursors. This leads to problems like what you are seeing and the use of suboptimal plans. You can attempt to tune around the sub-optimal plan but you may find that there are a number of queries with that problem. The other possibility is to set cursor_sharing = exact. That will prevent this problem from occuring.