Re: CURSOR_SHARING=SIMILAR + ODBC May Cause Problems

I've had issues with cs=similar on all versions - haven't even bothered with 10.1.0.4 <http://10.1.0.4>.

On 8/2/05, Paul Drake <bdbafh_at_gmail.com> wrote:
>
> 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 <http://10.1.0.3> on win32.
> They were fixed in 10.1.0.4 <http://10.1.0.4>.
>
> Paul
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

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