CURSOR_SHARING=SIMILAR + ODBC May Cause Problems

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