Re: cursor_sharing

Correct.

Bind variables, in principle, mean the optimiser is blind to what it is supposed to be searching for. With an even distribution of data, that's not a problem. With skewed data, it is. Always.

Regards
HJR "Tom Best" <oracle_person_at_yahoo.com> wrote in message news:aitm76$rv0$1_at_news.bentley.com...
> Howard:
>
> So, if I use "exact", and code with bind variables, then I can still have
> this problem you describe - not using an index when it should have.
Right?
>
> Thanks.
> Tom Best
>
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> news:ais9oc$8ho$1_at_lust.ihug.co.nz...
> > Consider the EMP table, which has mysteriously acquired a column
'GENDER'.
> > You do a 'select * from EMP where gender='M';
> >
> > That "M" is a literal value. CURSOR_SHARING=FORCE strips it out, and
> > replaces it with a bind variable. Your statement thus gets stored in the
> > library_cache as, effectively, 'select * from EMP where gender =
> something'.
> >
> > We develop an execution plan on this revised form of the statement. In
> this
> > case, the optimizer will probably look at the cardinality of the gender
> > column, find that it is two, and decide that you are therefore after
about
> > 50% of the EMP table rows, and therefore decide to do a full table scan
> > (indexes won't get used unless you are after around 2 to 5% of the
rows).
> So
> > your statment ends up proposing a full table scan.
> >
> > I now come along, and do a 'select * from EMP where gender = 'F";'. My
"F"
> > gets replaced with a bind variable because of CURSOR_SHARING. My revised
> > statement therefore matches yours, and I therefore get to share your
> > execution plan (which is what the Library Cache is therefore, after
all).
> > Excellent: my query doesn't go through a nasty hard parse, and my query
> > therefore executes more speedily than it would otherwise have done
without
> > cursor_sharing (if cursor_sharing was set to EXACT (the default) then my
> "F"
> > wouldn't have matched your "M", and we'd have both had to hard parse).
> >
> > Trouble is, this particular EMP table happens to list the people
employed
> by
> > the Australian Armed Forces, and without wishing to be sexist about it,
it
> > remains the case that 97% of said employees are blokes and a mere 3% are
> > women. So the fact that I share your execution plan (with its insistence
> on
> > a full table scan) is actually an utter disaster as far as I am
concerned.
> > If only the optimizer had known it was looking for "F"s, it would surely
> > have chosen an index access path, and my report would have returned
much,
> > much faster.
> >
> > In short, cursor_sharing=force, by hiding what it is precisely that you
> are
> > searching for from the optimiser, has done me a great disservice. And
that
> > will always be the case where you have wildly skewed data, and where the
> > quantities of data are such that slight imperfections in the execution
> plan
> > can turn into an extra hour or so of running time.
> >
> > That means data warehouses are a definite no-no for
cursor_sharing=force,
> > and it should also set alarm bells ringing wherever histograms are in
use
> > (because the presence of histograms indicates skewed data, and skewed
data
> > is another definite no-no for setting cursor_sharing to force).
> >
> > 9i tries to improve on this by having cursor_sharing=similar. That means
> > 'substitute the bind variable when it would make no difference to the
> > execution plan, but don't if it would'. That would resolve this
particular
> > example.
> >
> > But at the end of the day, any fiddling like this that Oracle gets up to
> has
> > to be a compromise. The much better bet is to get application code
written
> > sensibly in the first place. Cursor_sharing of any sort is a band-aid
for
> > when you are at the mercy of developers who haven't a clue (such as SAP,
> > Peoplesoft, or a myriad of others!). Oracle is quite right, therefore,
to
> > stick to its guns with the default of exact, and thereby prodding you
into
> > sorting out your code for yourself.
> >
> > Regards
> > HJR
> >
> > "Daud" <daud11_at_hotmail.com> wrote in message
> > news:f0bf3cc3.0208060417.4b6f127e_at_posting.google.com...
> > > Oracle 8.1.7.3.1
> > >
> > > In 8i there is a new parameter (new to me!) cursor_sharing. From the
> > > doc, setting it to FORCE means forcing oracle to convert all sqls that
> > > use literal values to use bind variables. This is good especially when
> > > you have an app that does not use bind variables.
> > > What's the negative impact of setting it to force? I am a little
> > > curious because this is not the default setting which one would expect
> > > it to be if it is really beneficial. Can someone also pls explain what
> > > extra effort does Oracle need to do when it is set to force and
> > > whether this extra effort is, in your opinion, worthwhile.
> > >
> > > rgds
> > > Daud
> >
> >
>
>
Received on Fri Aug 09 2002 - 02:38:30 CDT