If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

10g CURSOR_SHARING=FORCE and queries, dml's results

Hi,

We have application from external software company. It probably not use bind variables so in db are many hard parses. I'm going to set CURSOR_SHARING=FORCE (now it is EXACT). But some Oracle's "Guru" has told me that it may cause wrong results for queries and DML's which use the same columns in where clause but different values. It is hard to believe but to be sure i would like to ask You :

There is a bug that appears in 10.2.0.3 and 10.2.0.4 that causes errors in queries when cursor_sharing is set to something other than exact. You might want to take a look at Metalink to get the details.

Hi,
I don't think they would have cautioned you regarding wrong result. What they would have meant is wrong plan. Yes it is possible due to bind peeking you might get wrong execution plan. Also there is one more issue with cursor_sharing, it has to generate system generated bind variables which will create additional overhead.
In 11g they have addressed this issue. There is something like adjustable or optimal cursor sharing, which can take care of this.

Hi,
Then you need to check your code. I am not a perl expert, can't help you much on this. But this error generally comes in dynamic sql where parameters are not properly passed.
This may not be related to your case, but anyway if you change the cursor_sharing values then the datatype changes automatically. This may cause issue in some cases.