From oracle-l-bounce@freelists.org Wed May 5 14:47:38 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45JlDl21630 for ; Wed, 5 May 2004 14:47:23 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45Jl3621567 for ; Wed, 5 May 2004 14:47:13 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E444872D8EE; Wed, 5 May 2004 14:37:00 -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 27488-16; Wed, 5 May 2004 14:37:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CADC772D8F2; Wed, 5 May 2004 14:36:58 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 14:35:36 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 0784D72D3CE for ; Wed, 5 May 2004 14:35:36 -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 26505-67 for ; Wed, 5 May 2004 14:35:35 -0500 (EST) Received: from priv-edtnes14-hme0.telusplanet.net (outbound03.telus.net [199.185.220.222]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 713C472D7F4 for ; Wed, 5 May 2004 14:35:35 -0500 (EST) Received: from altair.centrexcc.com ([137.186.208.78]) by priv-edtnes14-hme0.telusplanet.net (InterMail vM.6.00.05.02 201-2115-109-103-20031105) with ESMTP id <20040505194926.IADY25826.priv-edtnes14-hme0.telusplanet.net@altair.centrexcc.com> for ; Wed, 5 May 2004 13:49:26 -0600 Message-Id: <6.1.0.6.2.20040505134207.02a593d0@pop.centrexcc.com> X-Sender: centrex@hosting.telus.net@pop.centrexcc.com (Unverified) X-Mailer: QUALCOMM Windows Eudora Version 6.1.0.6 Date: Wed, 05 May 2004 13:49:23 -0600 To: oracle-l@freelists.org From: Wolfgang Breitling Subject: Re: Wrong results using decode when db upgraded to 9205 In-Reply-To: <016001c432d5$a52bcaf0$7102a8c0@Primary> References: <6.1.0.6.2.20040505130500.02a28558@pop.centrexcc.com> <016001c432d5$a52bcaf0$7102a8c0@Primary> Mime-Version: 1.0 Content-type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 4501 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: breitliw@centrexcc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hmm, interesting. Slow test1: alter system flush shared_pool; alter session set cursor_sharing = force; select count(*) from psoprdefn where decode('C8','C8','237','0') = '237'; text in v$sql: select count(*) from psoprdefn where decode(:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",:"SYS_B_3") = :"SYS_B_4 Slow test2: alter system flush shared_pool; alter session set cursor_sharing = exact; select count(*) from psoprdefn where decode('C8','C8','237','0') = '237'; alter session set cursor_sharing = force; select count(*) from psoprdefn where decode('C8','C8','237','0') = '237'; text in v$sql: select count(*) from psoprdefn where decode('C8','C8','237','0') = '237' Could it be that if Oracle finds an exact match for the sql in the shared pool that it doesn't bother doing the bind variable rewrite for cursor_sharing=force ? At 01:17 PM 5/5/2004, you wrote: >Quick test: > >alter session set cursor_sharing = force; > >select count(decode(n1,3,1,2,null,4,1,6,null,null)) >from t1 >where decode(n1,6,3,null) = 2; > >Text in v$sql >select >count(decode(n1,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",null,:"SYS_B_3",:"SYS_B_4", >:"SYS_B_5",null,null)) from t1 where >decode(n1,:"SYS_B_6",:"SYS_B_7",null) = :"SYS_B_8" regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------