5 Replies Latest reply: Oct 5, 2012 12:03 PM by rp0428 RSS

    CURSOR_SHARING parameter

    user94513
      Hi,,

      I need some details about this cursor_sharing parameter to set SIMILAR,FORCE,EXACT..

      please give the answer for my below questions..

      1.what is cursor and its operation in oracle?example fot this?
      2. if setting cursor_sharing=force,exact,similar ..what are the operation will be done by this parameter?example?


      Pls help me.....i have very confused about this...?


      Regards,
      Mugunthan.
        • 1. Re: CURSOR_SHARING parameter
          rajeysh
          cursor_sharing parameter in the init. ora allows you to change the shared pool's default behaviour when parsing and caching SQL statements.
          force - allows two SQL statements, which differ only by a literal value, to share parsed code cached in the shared pool. the difference in literal values must not change the meaning of the statement.
          similar - allows two sql statements, which differ only by a literal value, to share parsed code cached in the shared pool. the difference in literal values must not change the meaning of the statement or its cached execution plan.
          exact - two sql statements must match exactly in order to share the parse code cached in shared pool.
          refer the link given below , you will get answer for all your questions.
          http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html

          to know Parsing in Oracle, refer.
          http://www.databasejournal.com/features/oracle/article.php/3341851/Parsing-in-Oracle.htm

          Edited by: rajeysh on Jun 19, 2010 12:40 PM
          • 2. Re: CURSOR_SHARING parameter
            P.Forstmann
            Please read Efficient use of Bind Variable, cursor_sharing and Related Cursor Parameters : http://www.oracle.com/technology/deploy/performance/pdf/designing_applications_for_performance_and_scalability.pdf.
            • 3. Re: CURSOR_SHARING parameter
              user94513
              got the answer and clarify my doubts....
              • 4. Re: CURSOR_SHARING parameter
                966342
                thankxx rajeysh...very nicely explained......
                i am new to oracle to oracle and trying my best to master it...hope to receive help from you... :)
                my email id id vijesh.gangadhran@gmail.com same as facebook.
                • 5. Re: CURSOR_SHARING parameter
                  rp0428
                  >
                  I need some details about this cursor_sharing parameter to set SIMILAR,FORCE,EXACT..

                  please give the answer for my below questions..

                  1.what is cursor and its operation in oracle?example fot this?
                  2. if setting cursor_sharing=force,exact,similar ..what are the operation will be done by this parameter?example?
                  >
                  See my reply in this thread from a couple of days ago.
                  Re: Considerations for setting cursor_sharing=force

                  It has references to articles by Jonathan Lewis and Charles Hooper that discuss cursor sharing in detail.