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@xxxxxxxxx> wrote:
> 
> On 8/2/05, Post, Ethan <Ethan.Post@xxxxxx> 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

Other related posts: