From oracle-l-bounce@freelists.org Fri Oct 14 07:52:55 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9ECqtEe003632 for ; Fri, 14 Oct 2005 07:52:55 -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 j9ECqlvX003587 for ; Fri, 14 Oct 2005 07:52:48 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 5953D1FF502; Fri, 14 Oct 2005 07:51:21 -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 32490-07; Fri, 14 Oct 2005 07:51:21 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D3D481FE8D7; Fri, 14 Oct 2005 07:51:20 -0500 (EST) Date: Fri, 14 Oct 2005 12:49:08 +0000 From: Mladen Gogala Subject: Re: autotrace issue To: Yasin Baskan Cc: madan.sanjay@gmail.com, oracle-l@freelists.org References: <083667B535F3464CA0DD0D1DAFA4E3760773331C@camexc1.kfs.local> In-Reply-To: <083667B535F3464CA0DD0D1DAFA4E3760773331C@camexc1.kfs.local> (from yasbs@kocbank.com.tr on Fri Oct 14 08:23:50 2005) Message-Id: <1129294148l.5813l.0l@medo.noip.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-archive-position: 26984 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: gogala@sbcglobal.net Precedence: normal Reply-To: gogala@sbcglobal.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-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-3.9 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE autolearn=ham version=2.63 On 10/14/2005 08:23:50 AM, Yasin Baskan wrote: > > > Sure it will reuse the same plan for a sql statement with bind > variables. But it does not use the same plan for sqls with literals. Of course it doesn't reuse the same plan. > > Select * from > Where col1=1; > > Select * from
> Where col1=2; Those are two completely different SQL statements, related as much as a T-Rex and a hummingbird. You can force the connection by setting cursor sharing to force. I'm actually very interested in that. Cursor sharing didn't work in 8i, it didn't work in 9i and I'd like someone else to try it in production in 10g first. I'd be in your dept if you decide to do so. May the force of the cursor sharing be with you. > > The optimizer can give 2 different plans for these sqls (according to > the statistics of course). I was talking about sqls with literals as the > sqls in the question. Because optimizer will check histograms. The distribution of values may warrant 2 different plans. -- Mladen Gogala http://www.mgogala.com -- http://www.freelists.org/webpage/oracle-l