Oracle性能优化之参数open_cursors和session_cached_cursor详解

 SQL> show parameter open_cursors --每个session（会话）最多能同时打开多少个cursor（游标）  
    NAME                                 TYPE        VALUE  
    ------------------------------------ ----------- ------------------------------  
    open_cursors                         integer     300  
    SQL> show parameter session_cached_cursor  --每个session（会话）最多可以缓存多少个关闭掉的cursor  
    NAME                                 TYPE        VALUE  
    ------------------------------------ ----------- ------------------------------  
    session_cached_cursors               integer     20  
   
SQL> select count(*) from v$open_cursor;  --是指当前实例的某个时刻的打开的cursor数目
        COUNT(*)  
    ----------  
           108

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        20%
open_cursors             300         8%