oracle参数open_cursors和session_cached_cursor详解

参数含义

• open_cursors：每个session（会话）最多能同时打开cursor（游标）数

• session_cached_cursor：每个session（会话）最多可以缓存已关闭cursor（游标）数

SQL> show parameter open_cursors           --每个session 最多能同时打开cursor数
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     3000
SQL> show parameter session_cached_cursor  --每个session 最多能缓存已关闭cursor数
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
session_cached_cursors               integer     50
SQL> select count(*) from v$open_cursor;  --当前时刻实例打开的cursor数

  COUNT(*)

----------
       108

参数作用

若要理解参数open_cursors和session_cached_cursor的作用，最好先弄清楚oracle是如何执行每个sql语句的。

通过上图，可以看出两点：

• 两个参数之间没有任何关系，相互之间不会有任何影响。
• 两个参数有着相同的作用：让后续相同的sql语句不再打开游标，从而避免软解析过程来提供应用程序的效率。

参数设置

Open_cursors设置

• 如果Open_cursors设置太小，对系统性能不会有明显改善，还可能触发ORA-O1000：m~imum open CUrsOrs exceeded．的错误。

• 如果设置太大，则无端消耗系统内存。

我们可以通过如下的sql语句查看你的设置是否合理：

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

HIGHEST_ OPEN CUR是实际打开的cursors 的最大值，MAX_OPEN_ CUR是参数Open_cursors的设定值，如果二者太接近，甚至触发ORA一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';

session_cached_cursors设置

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

 SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%';

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';

• session cursor cache hits：系统在高速缓存区中找到相应cursors的次数

• parse count(total)：总解析次数

二者比值越高，性能越好。如果比例比较低，并且有较多剩余内存的话，可以考虑加大该参数。

使用下面的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');

当我们执行一条 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 更高的性能。

OPEN_CURSORS是一个十分有趣的参数，经常有DBA发现自己的系统中的OPEN CURSORS十分大。我们看一个例子：

SQL>select sid,value from v$sesstat a,v$statname b where a.statistic#=b.statistic# and name='opened cursors current' order by 2;

      SID      VALUE

---------- ----------

      5430         93

      3527         95

      4055         96

      4090         97

      2012         98

      1819         98

      5349        102

      1684        103

      1741        116

      4308        169

      1970        170

      1369        181

      4208        184

       887        214

      5215        214

      3518        214

       868        214

      1770        215

      4050        215

      1809        231

      3010        235

       762        237

       731        471

      4013       1066

      2648       1152

      2255       1172

      2322       2620

前面我们已经查询出该系统的OPEN_CURSORS参数设置为3000，而会话中当前打开CURSOR最大的会话居然达到了2620。

在一般人的眼里，CURSOR使用后就关闭了，OPENED CURSORS的数量应该不会太多，难道应用程序出现了CURSOR泄漏，有些应用使用了CURSOR没有关闭？

实际上我们对OPEN CURSOR的概念一直存在误解。认为只有正在FETCH的CURSOR是OPEN状态的，而一旦FETCH结束，CLOSE CURSOR后，CURSOR就处于关闭状态了。因此一个会话中OPEN状态的CURSOR数量应该很少。事实上不是这样的，某些CURSOR在程序中是已经CLOSE了，但是Oracle 为了提高CURSOR的性能，会对其进行缓冲，这些缓冲的CURSOR，在程序中的关闭只是一个软关闭【1】，事实上，在会话中并未关闭，而是放在一个CURSOR缓冲区中。

当然Oracle不会让一个会话的OPEN CURSOR无限制增长，否则某个会话真的出现了CURSOR泄露，就会把整个数据库系统搞崩溃。Oracle通过Open_cursors参数来控制一个会话可以OPEN的CURSOR总量，这个参数的缺省值是300。

在Oracle 9.2.0.5之前，OPEN_CURSORS参数的作用是双重的，一方面是限制一个会话打开的CURSORS的总量。另外一方面，OPEN_CURSORS参数也作为PL/SQL CURSOR的缓冲。在PL/SQL中，如果某个CURSOR关闭了，这个CURSOR不会马上硬关闭【2】，而是首先保存在CURSOR缓冲中。如果这个会话当前打开的CURSOR数量还没有达到OPEN_CURSORS参数的值，那么就可以先保持OPEN状态。如果当前打开的CURSOR数量已经达到了OPEN_CURSORS参数的限制，那么首先会关闭一个被缓冲的，实际当时并未打开的CURSOR。如果缓冲池中的所有CURSOR都是实际打开的，那么就会报ORA-1000，“maximum open cursors exceeded”【3】。

Oracle 9.2.0.5以后，OPEN_CURSORS参数不再承担PL/SQL缓冲的工作,PL/SQL中的SQL也可以使用SESSION_CACHED_CURSORS的会话缓冲了。这个参数就成为了一个纯粹的限制。

虽然如此，OPEN_CURSORS参数仍然和CURSOR的缓冲机制密切相关，因为这个参数限制了当前某个会话打开CURSOR的最大值。设置一个较大的OPEN_CURSORS参数，可以避免出现ORA-1000，同时也可以让会话缓冲更多的CURSOR，改善SQL解析的性能。不过这个参数设置的较大会占用较大的PGA空间，消耗一定的物理内存。因此这个参数也不是设置的越大越好，一般的OLTP系统中，1000－3000就足够了。在共享服务器模式的系统中，这个参数的设置要略微保守一些，因为这个参数越大，占用的SGA空间也就越大。

另外要注意的是，从Oracle 9.0开始，这个参数就已经是动态的了，可以随时动态调整。

【1】Oracle把CURSOR关闭设计为为软关闭和硬关闭两种。软关闭的CURSOR虽然客户端程序关闭了CURSOR，但是实际上在CURSOR缓冲中，CURSOR还是处于OPEN状态，下回这个会话要执行这条SQL的时候，不需要再重新打开CURSOR了，而这个CURSOR在共享池中的共享部分，也因为还有会话存在引用而不会轻易被AGEOUT。这也是我们往往能够发现自己的会话中存在大量的OPEN状态的CURSOR的主要原因。

实际上，如果系统没有出现CURSOR溢出，存在大量的软关闭的CURSOR不会对系统产生太大的影响，Oracle 会自动维护这个CURSOR缓冲区，使之较为高效的运作，同时不会因为OPEN CURSOR数量太多而导致正常使用的CURSOR无法打开。

【2】当一个会话打开的CURSOR数量过多的时候，就会自动将一些本会话中客户端已经关闭的CURSOR真正的关闭掉。只有当CURSOR硬关闭了，这个CURSOR和会话之间的联系才彻底断掉了。下回这个会话要执行这个SQL，就需要重新创建CURSOR相关数据结构了。

【3】出现了ORA-01000的时候，我们可以通过v$open_cursor这个视图去查看哪些CURSOR是一直被打开的，通过查找SQL_TEXT我们就可以找到一些蛛丝马迹 。

相关文章：
ORA-01000: 超出打开游标的最大数 处理方法

参考文章：
https://blog.csdn.net/zq9017197/article/details/7345352/
https://www.cnblogs.com/lipera/p/6214443.html