Hi! Another interesting thing is that "explain plan" command doesn't often give you correct information which execution plan was used when using bind variables and histograms. You see index acces in v$sql_plan and 10046 trace, but explain plan keeps talking about FTS. (since autotrace uses explain plan command, it is affected too).
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 20, 2003 7:59 AM > 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).
