cursor_sharing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: cursor_sharing

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    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

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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

  4. #4
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,587
    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)
    TarryBlogging
    --- Everything was meant to be---

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  


Click Here to Expand Forum to Full Width