I'm resending my reply, because it appears to be lost. Sorry if you get a duplicate few days later (sigh).
Tanel. > Hi! > > How did you check which execution plan was chosen? Explain plan and > autotrace lie, use 10046 or v$sql_plan instead. > For num_buckets, dba_tab_cols view substracts one from real bucket count for > some reason. To get real value go to base tables and check row_cnt from > sys.hist_head$ or just count(*) appropriate rows from sys.histgrm$. > > But yes, the execution plan is chosen once per session, if first one took > FTS based on one value, then if second one picks index access, another > version of query execution plan is generated for the same SQL statement - > only soft parse is required. > > Tanel. > > ----- Original Message ----- > From: "Daniel Fink" <[EMAIL PROTECTED]> > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, August 21, 2003 7:54 PM > Subject: Re: cursor_sharing in 8.1.7.4 - good or bad > > > > 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). > > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
