cursor_sharing

[anandkl]

Hi friends,
I just wanted to know what are the advantages of setting cursor_sharing parameter.I know that this enalbes cursor sharing when sql statements are identical except for the where clause.But i also got to know that there are certain disadvantes on setting this parameter.I just wanted to know what these disadvantages are.

regards
anandkl

[aarroyob]

There are 2 values for this parameters: EXACT and FORCE. The default is exact, then oracle parse all queries are not EXACTLY equal :

select name, value from v$parameter<> select Name,Value from v$parameter;

And force value is usefull if you have lot of queries like:

select name, value from table where value=1;
select name, value from table where value=2;
...

In this case if you have this parameter to EXACT, oracle will need to parse all of your queries.

Hope that helps

Angel

[anandkl]

Hi,
Thanks for the response.I heard setting cusor_sharing=force will have come performance degradation on DSS and other applications.
This statement is provided in oracle documentaion.I wanted to know what these effects are.

regards
anandkl

[Tarry]

Originally posted by anandkl
Hi,
Thanks for the response.I heard setting cusor_sharing=force will have come performance degradation on DSS and other applications.
This statement is provided in oracle documentaion.I wanted to know what these effects are.

regards
anandkl

You mean overcome.
It can help overcome the need to discuss over with the developers who didn't use bind variables when they wrote the code...
But best is to use bind variables.

[chrisrlong]

Originally posted by anandkl
Hi,
Thanks for the response.I heard setting cusor_sharing=force will have come performance degradation on DSS and other applications.
This statement is provided in oracle documentaion.I wanted to know what these effects are.

regards
anandkl

What they are referring to is the lost ability to use histograms, I'm guessing. Histograms only work with hard-coded values, and FORCE changes all hard-coded values to binds, thereby eliminating the beneficial effects of histograms, assuming they already exist.

- Chris

[pando]

Originally posted by anandkl
Hi,
Thanks for the response.I heard setting cusor_sharing=force will have come performance degradation on DSS and other applications.
This statement is provided in oracle documentaion.I wanted to know what these effects are.

regards
anandkl

in DWH queries many times histrogramas has to be used. if we are using bind variables we wont be able to use histograms because the execution plan is generated before the bind variables are binded with values so the plan may not be the optimum