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
