Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> WARNING: CURSOR_SHARING=FORCE on 8.1.7

WARNING: CURSOR_SHARING=FORCE on 8.1.7

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 20 Aug 2001 07:17:12 -0700
Message-ID: <F001.00370CA2.20010820070529@fatcity.com>


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
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA
Received on Mon Aug 20 2001 - 09:17:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US