Rich:
This is Bug #1561329: ORA-12704 character set mismatch on select with cursor_sharing =
force. This bug apparently appears only in 8.1.7 on HP-UX. In this case,
CURSOR_SHARING must be set to EXACT. I'm not sure, but I seem to remember that it
didn't give me problems in 8.1.7.1.x, only in 8.1.7.0.x.
Jon Walthour
>
> From: "Jesse, Rich" <[EMAIL PROTECTED]>
> Date: 2001/08/20 Mon AM 11:05:29 EDT
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: WARNING: CURSOR_SHARING=FORCE on 8.1.7
>
> Hi all,
>
> Just thought I'd share an "experience" (ON-TOPIC!) with everyone. After
> upgrading from 8.0.6 to 8.1.7 on HP/UX 11.0, we've had a few problems with
> CURSOR_SHARING=FORCE. The reason I decided to use it is because our apps
> don't use bind variables and I wanted to decrease the size of our shared
> pool to help reduce latch contention. I figured this was the *easy*
> solution!
>
> Well, after almost three weeks, we're OK, but there is one lingering
> occasional problem. Under certain circumstances (I can't seem to get what
> exactly those are), specific DML will always generate an "ORA-12704
> character set mismatch" error when using CURSOR_SHARING=FORCE and CBO.
> Oracle knows about this and say it's fixed in 9i. However, they also say
> that a backport of the fix to 8.1.7 is "not feasible". <sigh>
>
> One common theme in the offending DML seems to be the use of functions in a
> GROUP BY or ORDER BY clause, but it's not consistent. However, Oracle
> Support was able to consistently reproduce the error. I've attached the
> output from the script (set echo on) in case anyone is interested in
> testing. Note however that not all SQL I've had blow up with this uses the
> ORDERED hint or deals with unanalyzed tables. Also note that Oracle Support
> says the workaround is to ALTER SESSION SET CURSOR_SHARING=EXACT for each
> problem statement.
>
> And last week I went to a quickie seminar with Kevin Loney/TUSC/Veritas,
> where Kevin recommended waiting for 9i before using CURSOR_SHARING=FORCE.
> <sigh> Now I think I'll need to plan how I'm going to turn it off: How big
> to make the shared pool? Do I now use histograms? etc. Like I didn't have
> enough to do...
>
> Hope this helps someone else planning an upgrade to 8i! :)
>
> Rich Jesse System/Database Administrator
> [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
>
>
>
>
> SQL> alter session set cursor_sharing=force;
>
> Session altered.
>
> SQL> drop table test12704;
>
> Table dropped.
>
> SQL> create table test12704 (f1 varchar2(3), f2 varchar2(3)) tablespace users ;
>
> Table created.
>
> SQL> insert into test12704 values ('A', 'B');
>
> 1 row created.
>
> SQL> insert into test12704 values ('C', 'D');
>
> 1 row created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> select * from test12704;
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> select * from test12704 where (f1 = 'A' and f2 = 'B') or (f1 = 'C' and f2 =
>'D');
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> select /*+ ORDERED */ * from test12704 where (f1 = 'A' and f2 = 'B') or (f1
>= 'C' and f2 = 'D');
> where (f1 = 'A' and f2 = 'B') or (f1 = 'C' and f2 = 'D')
> *
> ERROR at line 2:
> ORA-12704: character set mismatch
>
> SQL> analyze table test12704 compute statistics;
>
> Table analyzed.
>
> SQL> select * from test12704 where (f1 = 'A' and f2 = 'B') or (f1 = 'C' and f2 =
>'D');
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> alter session set cursor_sharing=exact;
>
> Session altered.
>
> SQL> select * from test12704;
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> select * from test12704 where (f1 = 'A' and f2 = 'B') or (f1 = 'C' and f2 =
>'D');
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> select /*+ ORDERED */ * from test12704 where (f1 = 'A' and f2 = 'B') or (f1
>= 'C' and f2 = 'D');
>
> F1 F2
> --- ---
> A B
> C D
>
> SQL> analyze table test12704 compute statistics;
>
> Table analyzed.
>
> SQL> select * from test12704 where (f1 = 'A' and f2 = 'B') or (f1 = 'C' and f2 =
>'D');
>
> F1 F2
> --- ---
> A B
> C D
>
>
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jon Walthour
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
