7 Replies Latest reply: Jan 7, 2009 1:00 AM by Aman.... RSS

    Measuring the value of "session_cached_cursors"  and "open_cursor"

    shipon_97
      Friends ,

      Recently In my Database production server Oracle10g (version : 10.2.0.1.0.), I got the "open_cursor" and "session_cached_cursors" related error where OEM asks to increase the value . I have increase the value but the problem still is not solved .

      Can anybody plz tell me , how can I measure the Standard value of "open_cursor" and also "session_cached_cursors" of my database server ?


      Another question ,

      SQL> show parameter open_

      NAME TYPE VALUE
      ------------------------------------ ----------- ------
      open_cursors integer 500

      In above output , what is the unit of 500 value . Is this value related with the SGA memory area ?
        • 1. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
          Aman....
          shipon_97 wrote:

          Can anybody plz tell me , how can I measure the Standard value of "open_cursor" and also "session_cached_cursors" of my database server ?
          The standard value for both the parameters is mentioned in the oracle docs here,
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams138.htm#REFRN10137
          >
          OPEN_CURSORS

          Property     Description
          Parameter type     Integer
          Default value     50
          Modifiable     ALTER SYSTEM
          Range of values     0 to 65535
          Basic     Yes>
          http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams189.htm#REFRN10195
          >
          SESSION_CACHED_CURSORS

          Property     Description
          Parameter type     Integer
          Default value     0
          Modifiable     ALTER SESSION
          Range of values     0 to operating system-dependent
          Real Application Clusters     Multiple instances can have different values.>
          open_cursors integer 500

          In above output , what is the unit of 500 value . Is this value related with the SGA memory area ?
          These are the number of cursors that you may open.
          HTH
          Aman....
          • 2. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
            627663
            Here is the query where you can find the cursor usage for any instance . ORACLE recommends If either of the Usage column figures approaches 100%, then the corresponding parameter should normally be increased. Once again it is an recommendation only . Find the sample out put also.

            select
            'session_cached_cursors' parameter,
            lpad(value, 5) value,
            decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
            from
            ( select
            max(s.value) used
            from
            v$statname n,
            v$sesstat s
            where
            n.name = 'session cursor cache count' and
            s.statistic# = n.statistic#
            ),
            ( select
            value
            from
            v$parameter
            where
            name = 'session_cached_cursors'
            )
            union all
            select
            'open_cursors',
            lpad(value, 5),
            to_char(100 * used / value, '990') || '%'
            from
            ( select
            max(sum(s.value)) used
            from
            v$statname n,
            v$sesstat s
            where
            n.name in ('opened cursors current', 'session cursor cache count') and
            s.statistic# = n.statistic#
            group by
            s.sid
            ),
            ( select
            value
            from
            v$parameter
            where
            name = 'open_cursors'
            )
            /

            Sample Output
            PARAMETER VALUE USAGE
            ---------------------- -------- ---------
            session_cached_cursors 0 n/a
            open_cursors 300 52%
            • 3. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
              shipon_97
              Thx Aman ...

              But One thing is not still cleared to me and that is ,

              when I increase cursor value (suppose 300 to 301) then how many SGA memory is allocated for each cursor ?
              I think my ques is clear to u ...

              waiting for ur kind reply .. ...
              • 4. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
                153119
                shipon_97,

                cursors are not allocated in SGA. Could you please, please, please, please, try some to make some effort to read docs?
                Also, could you please, please, please, please try to make some effort to drop the MSN brabble, and write complete words?

                THANK YOU!!

                --
                Sybrand Bakker
                Senior Oracle DBA
                • 5. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
                  601585
                  shipon_97 wrote:
                  Friends ,

                  Recently In my Database production server Oracle10g (version : 10.2.0.1.0.), I got the "open_cursor" and "session_cached_cursors" related error where OEM asks to increase the value . I have increase the value but the problem still is not solved .
                  What do you mean by "open_cursor and session_cached_cursors related error"?
                  Do you have any error number of message?

                  Dion Cho
                  • 6. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
                    shipon_97
                    Thanks all for reply ..

                    I have another query ...

                    How can I find the standarnd value of "open_cursor" as well as "session_cached_cursors" parameter value in the respect of my oracle database server . And what are the recommended value of these parameters . I am using oracle database 10g (v-10.2.0.1.0 ) .


                    In present the usage of those parameter in my database server are :

                    select
                    'session_cached_cursors' parameter,
                    lpad(value, 5) value,
                    decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
                    from
                    ( select
                    max(s.value) used
                    from
                    v$statname n,
                    v$sesstat s
                    where
                    n.name = 'session cursor cache count' and
                    s.statistic# = n.statistic#
                    ),
                    ( select value from v$parameter where name = 'session_cached_cursors' )
                    union all select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%' from
                    ( select max(sum(s.value)) used from v$statname n, v$sesstat s where n.name in ('opened cursors current', 'session cursor cache count') and s.statistic# = n.statistic#
                    group by s.sid
                    ),
                    ( select value from v$parameter where name = 'open_cursors'
                    )


                    Parameter value usage
                    ------------- -------- ---------
                    session_cached_cursors 50 172%
                    open_cursors 500 26%

                    Edited by: shipon_97 on Jan 7, 2009 11:23 AM
                    • 7. Re: Measuring the value of "session_cached_cursors"  and "open_cursor"
                      Aman....
                      shipon_97 wrote:
                      Thanks all for reply ..

                      I have another query ...

                      How can I find the standarnd value of "open_cursor" as well as "session_cached_cursors" parameter value in the respect of my oracle database server . And what are the recommended value of these parameters . I am using oracle database 10g (v-10.2.0.1.0 ) .
                      Shipon,
                      You can see the values of the parameters in your db with the simple show parameter command,
                      >
                      show parameter open_cursors
                      show parameter session_cached_cursors>
                      About the settings of the parameters and their optimal value, I guess there wont' be any "concrete" answer to that. Session cached cursors is set to 50 default in Oracle which means 50 cursors can be marked as 'hot cursors' for the the system and will be avoided from the library cache lookup. This also has a condition that the cursor will be marked as hot only when its run for 3 times. So you need to check back with your system that how many queries are actually requiring this optimization. And more over, this is used or said to be used when you are seeing a Library Cache Latch contention. I don't think that just for the sake of change, you need to modify the parameter from default.

                      The smae is true for the OPEN_CURSORS as well. The value is required to be changed if you are seeing an error about maximum opened cursor exceeding from the set value. Generally , a value of 2000 is enough for most of the systems but again, that may depend on site to site and surely enough , you need to check yours befoe playing around.
                      HTH
                      Aman....