oracle参数open_cursors和session_cached_cursor详解

 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

  1、open_cursors与session_cached_cursor的作用？

open_cursors设定每个session（会话）最多能同时打开多少个cursor（游标）。session_cached_cursor设定每个session（会话）最多可以缓存多少个关闭掉的cursor。想要弄清楚他们的作用，我们得先弄清楚Oracle如何执行每个sql语句。

看完上图后我们明白了两件事：

a、两个参数之间没有任何关系，相互也不会有任何影响。b、两个参数有着相同的作用：让后续相同的sql语句不在打开游标，从而避免软解析过程来提供应用程序的效率。
 
2、如何正确合理设置参数的大小?
a、如果Open_cursors设置太小，对系统性能不会有明显改善，还可能触发ORA-O1000：m~imum open CUrsOrs exceeded．的错误。如果设置太大，则无端消耗系统内存。我们可以通过如下的sql语句查看你的设置是否合理：

   SELECT MAX(A.VALUE) AS HIGHEST_OPEN_CUR, P.VALUE AS MAX_OPEN_CUR  
     FROM V$SESSTAT A, V$STATNAME B, V$PARAMETER P  
    WHERE A.STATISTIC# = B.STATISTIC#  
      AND B.NAME = 'opened cursors current'  
      AND P.NAME = 'open_cursors'  
    GROUP BY P.VALUE;  
      
    HIGHEST_OPEN_CUR MAX_OPEN_CUR  
    ---------------- --------------------  
                  28 300  

HIGHEST_ OPEN CUR是实际打开的cursors 的最大值，MAX_OPEN_ CUR是参数Open_cursors的设定值，如果二者太接近，甚至触发eRA一01000错误，那么你就应该调大参数Open_cursors的设定值。如果问题依旧没有解决，盲目增大Open_cursors也是不对的，这个时候你得检查应用程序的代码是否合理，比如说应用程序是否打开了游标，却没有在它完成工作后没有及时关闭。以下语句可以帮助你确定导致游标漏出的会话：

    SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL#  
      FROM V$SESSTAT A, V$STATNAME B, V$SESSION S  
     WHERE A.STATISTIC# = B.STATISTIC#  
       AND S.SID = A.SID  
       AND B.NAME = 'opened cursors curent';  

b、同样，session_cached_cursors的值也不是越大越好，我们可以通过下面两条语句得出合理的设置。

     SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';  
      
    NAME                                                                  VALUE  
    ---------------------------------------------------------------- ----------  
    opened cursors cumulative                                             15095  
    opened cursors current                                                   34  
    session cursor cache hits                                             12308  
    session cursor cache count                                              775  
    cursor authentications                                                  324  
      
   SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';  
      
    NAME                                                                  VALUE  
    ---------------------------------------------------------------- ----------  
    parse time cpu                                                          332  
    parse time elapsed                                                     1190  
    parse count (total)                                                    9184  
    parse count (hard)                                                     1031  
    parse count (failures)                                                    3  

session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数，parse count(total)就是总的解析次数，二者比值越高，性能越好。如果比例比较低，并且有较多剩余内存的话，可以考虑加大该参数。


c、使用下面的sql判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值。

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

当我们执行一条sql语句的时候,我们将会在shared pool产生一个library cache object,cursor就是其中针对于sql语句的一种library cache object.另外我们会在pga有一个cursor的拷贝,同时在客户端会有一个statement handle,这些都被称为cursor,在v$open_cursor里面我们可以看到当前打开的cursor和pga内cached cursor.

session_cached_cursor
这个参数限制了在pga内session cursor cache list的长度,session cursor cache list是一条双向的lru链表,当一个session打算关闭一个cursor时,如果这个cursor的parse count超过3次,那么这个cursor将会被加到session cursor cache list的MRU端.当一个session打算parse一个sql时,它会先去pga内搜索session cursor cache list,如果找到那么会把这个cursor脱离list,然后当关闭的时候再把这个cursor加到MRU端.session_cached_cursor提供了快速软分析的功能,提供了比soft parse更高的性能.

===================补充========================================
V$open_cursor与Open_cursor参数

这个视图和参数涉及游标的打开。什么是游标的打开，就是在库缓存中，用户在软、硬解析游标时，会在游标对象的句柄上加一个锁，也就是Library cache lock。在解析并执行完游标后，这个锁并不会马上去掉，而是会一直保留着，直到用户发出了Close命令关闭游标时为止。我们在SQL*Plus命令窗口中发出的命令，在抓取完所有行后，SQL*Plus将自动为我们发出Close命令来关闭游标。
SQL> show parameter cursor_sharing

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT


当游标打开时，Library cache lock将一直保持，这样，即使库缓存内存紧张，需要老化对象，也不会老化这些还正在加锁的对象。因此，如果用户不停的要求数据库服务器打开游标、执行SQL，但却忘了关闭游标，这很容易耗尽共享池的内存。为此，Oracle准备了一个参数，就是Open_cursor，它的默认值在9i下是50，在10g中是300，也就是说，在10g下，每个会话最多只能同时打开300个游标。有了这个限制，就不用害怕用户不停的打开游标但又不关闭它，而耗尽共享池内存了。如果会话同时打开的游标数量超出了Open_cursor参数的限制，Oracle将禁止会话打开新的游标。同时报出错误：ORA-01000: 超出打开游标的最大数 。在用户断开会话的连接后，会话打开的这些游标将自动关闭。

V$open_cursor视图专用来查看当前会话打开的游标信息。它只能查看当前会话打开的游标。


CURSOR_SHARING参数
如果应用程序中有很多类似下面这样的SQL语句：
select * from 某表 where id=1;
select * from 某表 where id=2;
select * from 某表 where id=50;
等等，这些SQL语句严格来说是无法共享游标（也就是共享执行计划）的，但是这些语句所需要的执行计划其实都是一样的。无论你在表中查询ID为1的行还是查询ID为100的行，执行方式应该是一样的。如果你想让这样的语句共享游标，那么，你可以改变Cursor_sharing参数的值。

此参有三个值：
EXACT：这个值是默认值。除非游标文本一模一样，否则不会共享游标。

SIMILAR：这个最智能，如果游标只有条件中的数据值部分不同，并且库缓存中原有游标的执行计划对于新执行的SQL语句也是最优的，将不再为SQL语句创建新的游标，而是让它共享库缓存中原有的游标。

FORCE ：不比较执行计划是否最优，只要游标中除了条件中的数据值部分不同外，其他部分都相同，就会共享游标。

Oracle可以将一部分执行次数比较多的游标的信息缓存在会话的私人内存PGA中，这样当被缓存游标再被执行时，很多数据不必再到库缓存中寻找，会话直接可以在自己的PGA中取出。这可以大大提高软解析的速度，这样的解析被称为更软的软解析（或快速软解析）。一般来说，会话在执行游标时，第一步会到自己的PGA中搜索游标，如果找到了，这就是更软的软解析。如果游标没有被缓存到PGA中，再到库缓存中查找，如果找到了这就是普通的软解析。如果库缓存中也没有，就进行硬解析，重新生成游标相关数据和执行计划。如果会话在执行游标时，发现游标的总的执行次数已经超过了三次，就会将游标信息缓存在自己的PGA中。此参数的作用是设定一个会话共可以缓存多少个游标。此参数的值如果比较大，将会耗用更多的PGA和共享池内存，但是，这对提高软解析速度是很有帮助的。如果你的数据库软解析耗用了过多的时间，可以尝试加大此参数的值。在10g中，此参数默认值是30。在大型OLTP应用中，此参数的值一般都设置为几百甚至上千。


version count
V$SQLAREA有一个列是V$SQL中没有的，就是：VERSION_COUNT，它是对应同一父游标的子游标的数量。如果这个数字太高，可能代表由于某些原因使本可以共享执行计划的游标没有共享

version count就表示当前父游标下对应子游标的个数。如果一个父游标对应的子游标version count过多，也就是对应了很多的子游标对象。这样，当server process检查可共享的游标时，就需要长时间的检索子游标列表。

最有名的version count过多问题是由于设置cursor_sharing参数为similar后，引发的version count错误。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2137413/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2137413/