I was curious about your statement "the kernel only peeks once per session". I wondered if another session that executed the same statement would be impacted by the peeking or would the optimizer reparse the statement.
So I set up a test. Million row table with 2 columns. c1 - number, c2 - date. C1 contains 2 distinct values 0 (999 rows) and 1 (999001 rows). I analyzed the table and 'tried' to generate a 2 bucket histogram. However, the num_buckets is still 1 on the column. When I run the query with a bind variable, it always selects a FTS, regardless of the first value I place in the variable. I am obviously missing something, but I've run out of ideas. I 'think' the problem is the number of buckets, but the analyze table column command seems to think 1 bucket is enough. Daniel Cary Millsap wrote: > 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). > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Cary Millsap > 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).
begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;;;;;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
