cursor_sharing should be used with caution - there were bugs in 8.1.6 and 8.1.7
in regard to this parameter, and it may 'autobind' statements that have literals on
purpose. So surely the best way to optimize SQL is to use bind variables where
they are needed (and only there) and not rely on an Oracle feature to do that for
you.
By the way, in 9i cursor_sharing may be FORCE or SIMILAR (and, of course,
EXACT, which is default behavior).
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Tom Best" <oracle_person_at_yahoo.com> wrote in message news:ahju4s$eof$1_at_news.bentley.com...
> Robert:
>
> Yes, it either matches a previous SQL stmt or not. Doesn't matter whether
> you have 4 out of 5 using BVs. It still hard-parses.
>
> But, keep in mind that you can use cursor_sharing, as in:
>
> alter session set cursor_sharing=force
>
> That will cause Oracle to look for opportunities to use BVs where you failed
> to use them. This is beside the point re: the "all or nothing" question,
> but is good to know whether you got 4 out of 5 or 0 out of 5. But, it is
> always best to implement the BVs yourself.
>
> HTH,
> Tom Best
>
> "R Chin" <rchin_at_panix.com> wrote in message
> news:ahjur7$cav$2_at_reader3.panix.com...
> > 8.1.7/AIX
> >
> > Is the usage of bind variable and the prevention of re-hard-parse an
> > ALL-OR-NOTHING
> > kind of situation ?
> >
> > I mean if in an SQL there are 5 places where BV should be used but I
> missed
> > one and
> > used only 4 BVs --- does this TOTALLY negate the 4 BVs
> > and causing a hard-parse every time ? that Oracle treats each SQL as a
> > different one
> > (for different values) ?
> >
> > Thanks
> >
> > Robert
> >
> >
> >
>
>
Received on Tue Jul 23 2002 - 14:07:53 CDT
