Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: cursor_sharing in 8.1.7.4 - good or bad
And don't count too heavily upon 9i's "bind value peek" capability.
Here's a simple test that Jonathan Lewis conjured up last year:
PREPARATION. Create data for "select c1, c2 from t1 where c1 = :bind1", where different bind1 values could have different paths if we used literals.
The moral: the kernel only peeks once per session.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Upcoming events:
- Hotsos Clinic 101 in Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details...
-----Original Message-----
Jesse, Rich
Sent: Tuesday, August 19, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
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 for sql not using bind
variables.
Goodluck.
"Fedock, John (KAM.RHQ)" <John.Fedock_at_us.kline.com> 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.
John
--
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: Cary Millsap
INET: cary.millsap_at_hotsos.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). Received on Tue Aug 19 2003 - 23:59:24 CDT
![]() |
![]() |