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 <cary.millsap_at_hotsos.com> 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:oraoraora_at_rediffmail.com]
> > Reply To: oraora oraora
> > Sent: Sunday, July 21, 2002 8:24 PM
> > To: bbaker_at_denvernewspaperagency.com
> > 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: bbaker_at_denvernewspaperagency.com
>
> 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
>
> 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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).
Received on Mon Jul 22 2002 - 14:08:32 CDT
