| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: WARNING: CURSOR_SHARING=FORCE on 8.1.7
I think I need to have a chat with Kevin when I see him at the NYOUG meeting...
I'm on 8.1.6 and have been using CURSOR_SHARING=FORCE for about a year. No problems. Admittedly, we don't do too many complicated queries and we analyze all tables and don't use hints...
>From: "Jesse, Rich" <Rich.Jesse_at_qtiworld.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: WARNING: CURSOR_SHARING=FORCE on 8.1.7
>Date: Mon, 20 Aug 2001 07:05:29 -0800
>
>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
>
>
>
><< CS_FORCE.txt >>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.com 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: ListGuru_at_fatcity.com (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).Received on Mon Aug 20 2001 - 11:15:49 CDT
 |
 |