CURSOR_SHARING=SIMILAR + ODBC May Cause Problems
- From: "Post, Ethan" <Ethan.Post@xxxxxx>
- To: <Oracle-L@xxxxxxxxxxxxx>
- Date: Tue, 2 Aug 2005 13:26:50 -0500
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.
Other related posts:
