Good stuff to know! In the specific case I'm thinking of, the query would always look for a column value of "N". This should only be about 5-10 rows at a time out of over 2M. Not the best design for scalability, but that's another thread.
It'll be interesting to see what'll happen in testing 9i and the opti-peeker because that column will not have any values of "N" at the time it's analyzed. Thanks for the heads-up! Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > -----Original Message----- > From: Cary Millsap [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 19, 2003 11:59 PM > To: Multiple recipients of list ORACLE-L > Subject: 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. > > 1. flush shared pool > set bind1 = 5 > execute query, notice an indexed access (optimal plan for bind1=5) > set bind1 = 70 > execute query, notice that kernel still uses the index > > 2. flush shared pool > set bind1 = 70 > execute query, notice a table scan (optimal plan for bind1=70) > set bind1 = 5 > execute query, notice that kernel still uses the table scan > > 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 > [EMAIL PROTECTED] 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)" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
