Re: CURSOR_SHARING=SIMILAR + ODBC May Cause Problems

On 8/2/05, Post, Ethan <Ethan.Post_at_ps.net> wrote:
>
> Just wanted to share a situation I encountered last week at a site. User
> were complaining about performance in some conversion/testing environments.
> I noted that the conversion scripts were hard parsing 4000/minute on a 2 cpu
> box. I changed QUERY_REWRITE and CURSOR_SHARING to SIMILAR. Parsing dropped
> to low number but script writer called to complain about slowdown.
>
> Investigation showed that the hard parsing SQL looked like...
>
> select foo from table where ID=123456789;
>
> After parameter changes SQL looked like....
>
> select foo from table where substr(ID,:bind,:bind)=:bind;
>
> so basically after the change the index in ID was not used because of
> function but hard parse made SQL look great except and it used index.
>
> This was an ODBC application using pass-through to submit SQL to Oracle.
>
> Not sure of others have seen this sort of thing but would be happy to learn
> more.

Ethan,

I didn't see the version/patchset of the Oracle db svr software. For such statements, if a code re-write is possible, the hint cursor_sharing_exact can return the prior behavior. For some statements, a function-based index will return the prior behavior.

We hit issues with cursor_sharing=similar and cursor_sharing=force in 10.1.0.3 on win32.
They were fixed in 10.1.0.4.

Paul

--
http://www.freelists.org/webpage/oracle-l