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

[amasny]

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 :

IS THAT POSSIBLE TO BE TRUE ?

Best Regards Arkadiusz Masny

[gandolf989]

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.

[aaryaan]

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.


Regards,
Satheesh Babu S

[akas_chauhan]

Yes, what they means looks correct to me. Whenever i set cursor_sharing to EXACT, so of my perl scripts fails saying this kind of message.

DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: O

CIStmtExecute) [for Statement "select wave_invitee_key from cmo_wave_invitee whe

re to_char(createts,'yyyy/mm/dd')='2008/06/13' and wave_key=?" with ParamValues:

1='200806130655301316392 '] at agreement_creation7.pl line 66.

DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: O

CIStmtExecute) [for Statement "select wave_invitee_key from cmo_wave_invitee whe

re to_char(createts,'yyyy/mm/dd')='2008/06/13' and wave_key=?" with ParamValues:

1='200806130655301316392 '] at agreement_creation7.pl line 66.

Issuing rollback() for database handle being DESTROY'd without explicit disconne

ct().

[aaryaan]

Hi Akash,
If same piece of code is working fine with one setting and not working with other setting, then potentially you are hitting a bug. Support is the right place.

Regards,
Satheesh Babu S

[akas_chauhan]

For me same piece of code always throws error. Never encountered the issue mentioned by you.

[aaryaan]

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.

Regards,
Satheesh Babu S
http://satheeshbabus.blogspot.com

[akas_chauhan]

Yup, if you change cursor_sharing you should be aware of the performance impacts. I'm not going to give lecture on performance benefits/degradation. There are tons of materials on Net.

Whole point is if you change some parameter, i assume there are lot of testing done for this changes.