Re: cursor_sharing

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 Thu Aug 08 2002 - 07:58:05 CDT