Re: cursor_sharing

Is it correct to say that setting cursor_sharing=EXACT can only pose a problem if you are using CBO? Is it still a problem if I am using RBO? I know CBO is the way to go, but the application vendor will not support CBO yet.

rgds
Daud

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3d537141_at_dnews.tpgi.com.au>...
> 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 Sun Aug 11 2002 - 07:45:37 CDT