Printable View
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
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
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.Quote:
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
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.
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.Quote:
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
- Chris
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 optimumQuote:
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