From oracle-l-bounce@freelists.org Tue Aug 2 13:28:48 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j72ISm73010654 for ; Tue, 2 Aug 2005 13:28:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j72ISjIP010641 for ; Tue, 2 Aug 2005 13:28:45 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DD8FE1DBC15; Tue, 2 Aug 2005 13:28:41 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 08164-09; Tue, 2 Aug 2005 13:28:41 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 56D711DBB0C; Tue, 2 Aug 2005 13:28:41 -0500 (EST) X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5978F.BFEB0D9E" Subject: CURSOR_SHARING=SIMILAR + ODBC May Cause Problems Date: Tue, 2 Aug 2005 13:26:50 -0500 Message-ID: <1F989681BA05FA4CAD9FA849ED8520579B4BBF@pscdalpexch01.perotsystems.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: CURSOR_SHARING=SIMILAR + ODBC May Cause Problems Thread-Index: AcWXjVJXySjRIMjKTHKiMWMcEzBEGQAAYQuw From: "Post, Ethan" To: X-OriginalArrivalTime: 02 Aug 2005 18:26:50.0895 (UTC) FILETIME=[C019A1F0:01C5978F] X-archive-position: 23189 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Ethan.Post@ps.net Precedence: normal Reply-To: Ethan.Post@ps.net X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.0 required=5.0 tests=AWL,BAYES_00, HTML_FONTCOLOR_BLUE,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5978F.BFEB0D9E Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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. =20 Investigation showed that the hard parsing SQL looked like... =20 select foo from table where ID=3D123456789; =20 After parameter changes SQL looked like.... =20 select foo from table where substr(ID,:bind,:bind)=3D:bind; =20 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. =20 This was an ODBC application using pass-through to submit SQL to Oracle. =20 Not sure of others have seen this sort of thing but would be happy to learn more. ------_=_NextPart_001_01C5978F.BFEB0D9E Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Just wanted to share a situation I encountered last week at a = site. User=20 were complaining about performance in some conversion/testing = environments. I=20 noted that the conversion scripts were hard parsing 4000/minute on a 2 = cpu box.=20 I changed QUERY_REWRITE and CURSOR_SHARING to SIMILAR. Parsing dropped = to low=20 number but script writer called to complain about = slowdown.

Investigation showed that the hard parsing SQL looked=20 like...

select foo from table where ID=3D123456789;

After parameter changes SQL looked like....

select foo from table where=20 substr(ID,:bind,:bind)=3D:bind;

so basically after the change the index in ID was not used = because of=20 function but hard parse made SQL look great except and it used=20 index.

This was an ODBC application using pass-through to submit SQL = to Oracle.=20

Not sure of others have seen this sort of thing but would be = happy to=20 learn more.

------_=_NextPart_001_01C5978F.BFEB0D9E-- -- http://www.freelists.org/webpage/oracle-l