Re: cursor_sharing

RBO is indifferent to bind variables vs. hardcoded literals. *However*: the parsing issue remains.
In case of RBO I would set CURSOR_SHARING = FORCE to reduce the number of hard parses.

I am quite aware, although CBO is 8 years old, many vendors, especially those developing for Sqlserver *and* Sybase *and* Oracle still stick to RBO. This will mean you are stuck once Oracle releases 10i.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

"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

> > > >

> > > >

> > >

> > >