Loading ...
Sorry, an error occurred while loading the content.

Question on Cursor_sharing

Expand Messages
  • SP
    We are (still) on ORA 9.2.0.8 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.     Any success
    Message 1 of 3 , Jan 8, 2009
    • 0 Attachment
      We are (still) on ORA 9.2.0.8 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.  
       
      Any success story for  9.2.0.8 / PeopleSoft HRMS with self serivce / and CURSOR_SHARING=SIMILAR.
       
      Advance thanks for your input.
       
      SP

    • David Kurtz
      I have experimented with CURSOR_SHARING, and I would not recommend setting this parameter instance-wide to non-default values. It is not always beneficial.
      Message 2 of 3 , Jan 9, 2009
      • 0 Attachment
        I have experimented with CURSOR_SHARING, and I would not recommend setting this parameter instance-wide to non-default values.  It is not always beneficial.  Every time I have tried it, I have always found something else in the system that reacts badly.  However, I have had some success setting it at session level for specific batch programs. In which cases, I set it with a trigger on PSPRCSRQST which only fires for specified process - that way I don't have to customise any applications.
         
        It can be effective to set cusror sharing to similar on
        * SQR processes with contain a lot of dynamically generated SQL. 
        * Application Engine processes with a lot of loops in their processing (and which do not use the REUSE STATEMENT option).
        These process can consume a lot of time of SQL parse. 
         
        Cursor Sharing is about whether Oracle needs to hard parse a SQL statement, or whether it recognises a SQL statement with different litterals as the same statement because litteral values have been converted to bind variables. 
         
        The parse overhead can also be significantly increased (up to 4x) on unicode databases (prior to PeopleSoft Application version 9) because PeopleSoft created length checking constraints on all character columns instead of using Character Semantics.
         
         
        regards
        _________________________
        David Kurtz
        Go-Faster Consultancy Ltd.
        tel: +44 (0)7771 760660
        fax: +44 (0)7092 348865
        mailto:david.kurtz@...
        web: www.go-faster.co.uk
        Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
        DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle: http://blog.go-faster.co.uk
        PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
         


        From: psftdba@yahoogroups.com [mailto:psftdba@yahoogroups.com] On Behalf Of SP
        Sent: Friday, January 09, 2009 3:27 AM
        To: psftdba@yahoogroups.com
        Subject: PeopleSoft DBA Forum Question on Cursor_sharing

        We are (still) on ORA 9.2.0.8 with default setting for CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in Oracle 10g.  
         
        Any success story for  9.2.0.8 / PeopleSoft HRMS with self serivce / and CURSOR_SHARING=SIMILAR.
         
        Advance thanks for your input.
         
        SP

      • shajivps
        SP, We were using CURSOR_SHARING=EXACT in our old enviroment (9.2.0.3 db with psoft 8.42) and we are using the same setting in our new environment (10.2.0.3
        Message 3 of 3 , Jan 9, 2009
        • 0 Attachment
          SP,

          We were using CURSOR_SHARING=EXACT in our old enviroment (9.2.0.3 db
          with psoft 8.42) and we are using the same setting in our new
          environment (10.2.0.3 with psoft 9 finance).

          The primary benefit of setting it to SIMILAR is for reducing hard
          parses of sql with hard coded literals so that optimizer will end up
          choosing the same plan for similar looking sql's (havent tested how
          effective it is ).

          Once again, in a big system like peoplesoft, you cant switch this
          parameter without extensive testing. We had set the parameter to
          EXACT right from the beginning of the upgrade and whatever statements
          had issues, we fixed it manually using a bunch of hints, manipulating
          stats, freezing stats for some tables etc., We still have some heavy
          I/O statements that can be tuned. But, overall users are happy with
          the performance of online and batch. Will get back to this system
          maybe after a month to fix the sql's that are using unnecessary I/O's
          and hoping that I dont have to touch this db for the next 3 years till
          the next upgrade.

          Regards,

          Shaji.

          --- In psftdba@yahoogroups.com, SP <sripingali@...> wrote:
          >
          > We are (still) on ORA 9.2.0.8 with default setting for
          CURSOR_SHARING. Heard some good thing about with setting of SIMILAR in
          Oracle 10g.  
          >  
          > Any success story for  9.2.0.8 / PeopleSoft HRMS with self serivce /
          and CURSOR_SHARING=SIMILAR.
          >  
          > Advance thanks for your input.
          >  
          > SP
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.