Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> CURSOR_SHARING=SIMILAR + ODBC May Cause Problems

CURSOR_SHARING=SIMILAR + ODBC May Cause Problems

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Tue, 2 Aug 2005 13:26:50 -0500
Message-ID: <1F989681BA05FA4CAD9FA849ED8520579B4BBF@pscdalpexch01.perotsystems.net>


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.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 02 2005 - 13:28:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US