Cary, You said "Barb's bottom line is absolutely correct; use bind variables. However you can."
Would you include using cursor_sharing = force as part of that "however you can"? I could have sworn that Jeff and Gary said NOT to use it, when they presented to the NYOUG DBA SIG. Rachel --- Cary Millsap <[EMAIL PROTECTED]> wrote: > I think I saw the correct answer to this one shoot by on the list. In > Oracle8, the use of bind variables completely prevents the Oracle > query > optimizer from using histograms. Could make a big difference in which > plan CBO selects if there's a lot of data skew. > > In Oracle9, it gets a little better, but things still aren't > completely > right. Apparently, a session only peeks at the value once and then > uses > the first plan it computes thereafter. > > Barb's bottom line is absolutely correct; use bind variables. However > you can. > > > Cary Millsap > Hotsos Enterprises, Ltd. > http://www.hotsos.com > > Upcoming events: > - Hotsos Clinic, Jul 23-25 Chicago > - Miracle Database Forum, Sep 20-22 Middlefart Denmark > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 > Dallas > > > > -----Original Message----- > Barbara > Sent: Monday, July 22, 2002 10:08 AM > To: Multiple recipients of list ORACLE-L > > prem. > Never did get an answer to this question. I don't know why using > bind > variables changed the execution path. My best guess comes from the > developer. She thinks that when we supplied the values, the > optimizer > knew > what the range of values would be, and could therefore determine to > use > the > index. With the bind variable, the optimizer did not have a range of > values > to work with and therefore did not choose the index in the execution > path. > > I have no knowledge that using bind variables will suppress indexes. > Just > happened that it did in this case. > Also keep in mind that this particular database is using an old > version > of > Oracle (7.3.4). Optimizer got much better in version 8. > > The list helped me out with a work-around, which was to index-hint > the > index > I wanted. > > Bind variables are definitely "good guys". I highly recommend you > continue > with your code changes to include binds. > > Good luck. > Barb > > > > ---------- > > From: oraora oraora[SMTP:[EMAIL PROTECTED]] > > Reply To: oraora oraora > > Sent: Sunday, July 21, 2002 8:24 PM > > To: [EMAIL PROTECTED] > > Subject: Re: bind vars change explain plan > > > > Baker, > > > > sorry i did not read the reply to ur query. > > what was the reply ? > > will using bind vars suppress index ? > > kindly let me know b'coz i have also changed my code to SQL with > > bind vars just now. > > > > Regards, > > prem. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Baker, Barbara > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > -------------------------------------------------------------------- > 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.com > -- > Author: Cary Millsap > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing > Lists > -------------------------------------------------------------------- > 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). __________________________________________________ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- 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).
