Oracle-L: RE: cursor_sharing in 8.1.7.4

From: Fedock, John (KAM.RHQ) <John.Fedock_at_us.kline.com>
Date: Thu, 21 Aug 2003 07:01:47 -0800
Message-ID: <F001.005CBF9E.20030821070147@fatcity.com>

Thanks to all for the advice. I plan to test sometime soon and will post results on how it works for us.

John

-----Original Message-----
Sent: Wednesday, August 20, 2003 12:44 PM To: Multiple recipients of list ORACLE-L

Like I said, it works fine here, but then again I also said, YMMV:

MYSID;/usr/oracle> uname -a
HP-UX mybox B.11.00 .........
MYSID;/usr/oracle> sqlplus /

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Aug 20 10:43:20 2003

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production

SQL> select value from v$parameter where name = 'cursor_sharing';

VALUE

FORCE SQL> Select 'the current date is: '||sysdate from dual;

'THECURRENTDATEIS:'||SYSDA

the current date is: 20-AUG-03

SQL> Select 'the current date is: '||sysdate from dual;

'THECURRENTDATEIS:'||SYSDA

the current date is: 20-AUG-03

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production JServer Release 8.1.7.4.0 - Production

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

-----Original Message-----
Sent: Wednesday, August 20, 2003 11:25 AM To: Multiple recipients of list ORACLE-L

NO...do not use. It is bugged. I believe it is fixed in 9x. It has problems with core dumps/600 errors when executing SQL statements depending on their length. It was not fixed as of 8.1.7.3, and I seem to remember it not supposed to being fixed until 9x.
this statement might fail.
Select 'the current date is: '||sysdate from dual; and this might succeed (only difference is an added space): Select 'the current date is: '||sysdate from dual; A consultant here supplied me with this info..as I was lookin at doing it for ourselves.
Brian Spears

-----Original Message-----
Sent: Tuesday, August 19, 2003 7:34 PM
To: Multiple recipients of list ORACLE-L

Point noted. Excellent posts from both you and Walt. I guess that is what the forum is for. To present one's experience without trashing someone else's point of view.

Since you have first hand experience on the latest I guess John should listen to both of you first. Thanks

"Jesse, Rich" <Rich.Jesse_at_qtiworld.com> wrote: True, but using binds isn't always an option, like when running a 3rd party product. I also could not justify weeks of recoding homegrown software for the relatively minor gains we would have using binds. And scalability (or lack thereof) didn't seem to be argument enough.

We're using CS=F on 8.1.7.4 on HP/UX 11.0. We haven't yet had a single issue attributable to it in the 7 months we've been at the .4 release. There were some nasty issues with 8.1.7.2 (ORA-600 and silently returning WRONG data), but like I said, .4 seems to have cleaned that up. YMMV.

Remember that under 8i, you may see some performance DROP by using binds. The case that comes to mind is if you're using histograms with ANALYZE or DBMS_STATS. Under 8i, the optimizer can no longer "see" the values and therefore can't use the histograms. Under 9i (R2 only?), you can tell the optimizer to peek at the bind variables' values in order to make use of histograms. We plan on doing this when we get to 9i in order to use a very low cardinality index, where less than 0.0001% of the rows is what we need to fetch, but 8i won't use the index because of the cardinality.

Your best bet, of course, is to test! HTH! GL! :)

Rich

Rich Jesse System/Database Administrator rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA

-----Original Message-----
Sent: Tuesday, August 19, 2003 2:49 PM
To: Multiple recipients of list ORACLE-L

Hi,
No. I recommend that you do not change cursor_sharing if you are doing it just to get rid of 4031. There could be serious problems. ora-600 etc. Maybe Oracle has fixed everything in latest 817 but I suggest not to take that chance.
For 4031 you can do the following : pin pl/sql sql preferably at startup, use bind variables and periodically check fo! r sql not using bind variables.
Goodluck.

"Fedock, John (KAM.RHQ)" wrote:

Does anyone have any experience using cursor_sharing in 8.1.7.4? I feel I need to use it due to literal SQL statements being used. We have had misc 4031 errors off and on and I feel this is my last hope. I have a very busy OPFS server (HP-UX).

I did try using cursor_sharing in 8.1.7.2, and I remember incorrect sorting and other unexpected results happening.

Thanks for any input.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Fedock, John (KAM.RHQ)
INET: John.Fedock_at_us.kline.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

RE: cursor_sharing in 8.1.7.4 - good or bad