Re: cursor_sharing

There are two issues here. One is, do we share cursors. The other is, do we use the most efficient execution plan.

Sharing cursors means avoiding a hard parse. The inclusion of literals in a query instead of bind variables prevents the sharing of cursors, and thus increases the rate of hard parses (misses in the library cache), and the cpu consumption of the database as a result. None of which has anything to do with the optimization mode: you miss just as much with RBO and literals as you would with CBO and literals (in terms of high hard parse rates), and you benefit as much from bind variables (in terms of lower hard parse rates) with RBO as you would with CBO.

The second issue is related to the first, in the sense that -with the CBO only- the use of inappropriate bind variables (when the data is skewed) will lead to plans which are inefficient (fulls scans instead of index use, for example). The RBO doesn't suffer from that problem, because it suffers from an altogether worse one: it follows rules. And one of the rules is: if there's an index, use it -and never mind whether the data is skewed or not. So, you are right in a sense that only the CBO is fooled by bad bind variables: because the RBO wouldn't give a damn anyway, and is just as fooled by 'bad' bind variables as 'good' ones.

So, the lack of cursor sharing (because of the lack of coding of bind variables) is bad whatever the optimization mode: hard parses go up. But does that lack affect the RBO in the eventual efficiency of its execution plans? No, because the quality of the execution plans wouldn't be affected by the presence or absence of bind variables anyway: rules are rules.

Regards
HJR "Daud" <daud11_at_hotmail.com> wrote in message news:f0bf3cc3.0208110445.6070f2a1_at_posting.google.com...
> 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 - 09:09:24 CDT