Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: cursor_sharing

Re: cursor_sharing

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 8 Aug 2002 09:17:27 +1000
Message-ID: <ais9oc$8ho$1@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 Wed Aug 07 2002 - 18:17:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US