Re: Wrong results using decode when db upgraded to 9205

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 05 May 2004 13:49:23 -0600
Message-Id: <6.1.0.6.2.20040505134207.02a593d0@pop.centrexcc.com>

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_at_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
-----------------------------------------------------------------

Received on Wed May 05 2004 - 14:47:38 CDT