Re: cursor_sharing

Hi Daud,

Firstly, note the bugs as already mentioned by Anurag.

Secondly consider DSS/Data Warehouse environments where the performance of large queries might be subject to non uniform distribution of data and the CBO is relying heavily on histogram statistics to pick appropriate execution paths.

The use of bind variables, the sharing of cursors and the non use of histograms could be one ugly mother in such an environment.

Horses for courses and this option is definitely for the sprinters and not for the stayers.

Cheers

Richard

Daud wrote:
>
> 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