10g CURSOR_SHARING=FORCE and queries, dml's results
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: 10g CURSOR_SHARING=FORCE and queries, dml's results

Hybrid View

  1. #1
    Join Date
    Feb 2008
    Posts
    31

    10g CURSOR_SHARING=FORCE and queries, dml's results

    Hi,

    We have application from external software company. It probably not use bind variables so in db are many hard parses. I'm going to set CURSOR_SHARING=FORCE (now it is EXACT). But some Oracle's "Guru" has told me that it may cause wrong results for queries and DML's which use the same columns in where clause but different values. It is hard to believe but to be sure i would like to ask You :

    IS THAT POSSIBLE TO BE TRUE ?

    Best Regards Arkadiusz Masny

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    There is a bug that appears in 10.2.0.3 and 10.2.0.4 that causes errors in queries when cursor_sharing is set to something other than exact. You might want to take a look at Metalink to get the details.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2008
    Posts
    8
    Hi,
    I don't think they would have cautioned you regarding wrong result. What they would have meant is wrong plan. Yes it is possible due to bind peeking you might get wrong execution plan. Also there is one more issue with cursor_sharing, it has to generate system generated bind variables which will create additional overhead.
    In 11g they have addressed this issue. There is something like adjustable or optimal cursor sharing, which can take care of this.


    Regards,
    Satheesh Babu S

  4. #4
    Join Date
    Jun 2007
    Location
    Milwaukee,WI
    Posts
    27
    Yes, what they means looks correct to me. Whenever i set cursor_sharing to EXACT, so of my perl scripts fails saying this kind of message.

    DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: O

    CIStmtExecute) [for Statement "select wave_invitee_key from cmo_wave_invitee whe

    re to_char(createts,'yyyy/mm/dd')='2008/06/13' and wave_key=?" with ParamValues:

    1='200806130655301316392 '] at agreement_creation7.pl line 66.

    DBD::Oracle::st execute failed: ORA-01008: not all variables bound (DBD ERROR: O

    CIStmtExecute) [for Statement "select wave_invitee_key from cmo_wave_invitee whe

    re to_char(createts,'yyyy/mm/dd')='2008/06/13' and wave_key=?" with ParamValues:

    1='200806130655301316392 '] at agreement_creation7.pl line 66.

    Issuing rollback() for database handle being DESTROY'd without explicit disconne

    ct().

    Akash
    OCP 8i


  5. #5
    Join Date
    Mar 2008
    Posts
    8
    Hi Akash,
    If same piece of code is working fine with one setting and not working with other setting, then potentially you are hitting a bug. Support is the right place.

    Regards,
    Satheesh Babu S

  6. #6
    Join Date
    Jun 2007
    Location
    Milwaukee,WI
    Posts
    27
    For me same piece of code always throws error. Never encountered the issue mentioned by you.

    Akash
    OCP 8i


  7. #7
    Join Date
    Mar 2008
    Posts
    8
    Hi,
    Then you need to check your code. I am not a perl expert, can't help you much on this. But this error generally comes in dynamic sql where parameters are not properly passed.
    This may not be related to your case, but anyway if you change the cursor_sharing values then the datatype changes automatically. This may cause issue in some cases.

    Regards,
    Satheesh Babu S
    http://satheeshbabus.blogspot.com

  8. #8
    Join Date
    Jun 2007
    Location
    Milwaukee,WI
    Posts
    27
    Yup, if you change cursor_sharing you should be aware of the performance impacts. I'm not going to give lecture on performance benefits/degradation. There are tons of materials on Net.

    Whole point is if you change some parameter, i assume there are lot of testing done for this changes.

    Akash
    OCP 8i


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