New bug in 8.1.7 (related to cursor

From: <Alexander.Feinstein_at_MitchellRepair.com>
Date: Wed, 20 Dec 2000 16:06:47 -0800
Message-Id: <10716.125061@fatcity.com>

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible.

------_=_NextPart_001_01C06AE1.E991CCEA
Content-Type: text/plain;

charset="ISO-8859-1"

Hi All,

Oracle EE 8.1.7.0.0 64-bit for HP-UX 11.0

cursor_sharing = force with CBO produce
ORA-12704: character set mismatch

Test case:

select * from v$version;
select value from v$parameter where name = 'cursor_sharing';

drop table test12704;
create table test12704 (field_1 varchar2(30), field_2 varchar2(30)); insert into test12704 values ('AAA', 'BBBBBBBBB'); insert into test12704 values ('CCCCCCCCC', 'DDDDDDDDD'); commit;
select * from test12704;
select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); analyze table test12704 compute statistics; select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); -- ERROR
select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB'); select * from test12704
where (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');

alter session set cursor_sharing=exact;
select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); -- OK
alter session set cursor_sharing=force;
select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); -- OK
alter system flush shared_pool;
select * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); -- ERROR
select /*+ RULE */ * from test12704
where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')

or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD'); -- OK

drop table test12704;

I have TAR opened.
Can anybody reproduce this on SUN or anywhere else? Trace file has
ORA-12704 ( dty=1 typ=1 csfm=1 csid=1 ) ORA-12704 ( dty=1 typ=3 styp=0 csfm=0 csid=0 ) for each occurrence.

Side notes.

1. event 10046 shows that it happens before BIND.
2. event 10053 does not show anything.
3. Steve Adams's script disk_sort.sql produce the same error,

even data dictionary was not analyzed, just because of the hints. Does it mean that with optimizer_mode=CHOOSE and hints Oracle will use CBO? Regards,
Alex

------_=_NextPart_001_01C06AE1.E991CCEA
Content-Type: text/html;

charset="ISO-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2650.12">
<TITLE>New bug in 8.1.7 (related to cursor_sharing = force)</TITLE>
</HEAD>
<BODY>

Hi All,


Oracle EE 8.1.7.0.0 64-bit for HP-UX 11.0


cursor_sharing = force with CBO produce
 ORA-12704: character set mismatch


Test case:


select * from v$version;
 select value from v$parameter where name = 'cursor_sharing';


drop table test12704;
 create table test12704 (field_1 varchar2(30), field_2 varchar2(30));
 insert into test12704 values ('AAA', 'BBBBBBBBB');
 insert into test12704 values ('CCCCCCCCC', 'DDDDDDDDD');
 commit;
 select * from test12704;
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 analyze table test12704 compute statistics;
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 -- ERROR
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB');
 select * from test12704
 where (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');


alter session set cursor_sharing=exact;
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 -- OK
 alter session set cursor_sharing=force;
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 -- OK
 alter system flush shared_pool;
 select * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 -- ERROR
 select /*+ RULE */ * from test12704
 where (field_1 = 'AAA' and field_2 = 'BBBBBBBBB')
    or (field_1 = 'CCCCCCCCC' and field_2 = 'DDDDDDDDD');
 -- OK


drop table test12704;


I have TAR opened.
 Can anybody reproduce this on SUN or anywhere else?
 Trace file has
 ORA-12704 (  dty=1 typ=1 csfm=1 csid=1 )
 ORA-12704 (  dty=1 typ=3 styp=0 csfm=0 csid=0 )
 for each occurrence.


Side notes.
 1. event 10046 shows that it happens before BIND.
 2. event 10053 does not show anything.
 3. Steve Adams's script disk_sort.sql produce the same error,
    even data dictionary was not analyzed, just because of the hints.
    Does it mean that with optimizer_mode=CHOOSE and hints Oracle will use CBO?
Received on Wed Dec 20 2000 - 18:06:47 CST