-
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
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
anandkl
-
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.
Tarry Singh
I'm a JOLE(JavaOracleLinuxEnthusiast)
--- Everything was meant to be---
-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|