在oracle下 cursor,详解参数open_cursors与session_cached_cursor在Oracle中的作用

SQL> SELECT 'session_cached_cursors'PARAMETER,2 LPAD(VALUE, 5) VALUE,3 DECODE(VALUE, 0, 'n/a', TO_CHAR(100 * USED / VALUE, '990') || '%') USAGE4 FROM (SELECT MAX(S.VALUE) USED5 FROMV$STATNAME N, V$SESSTAT S6 WHERE N.NAME = 'session cursor cache count'

7 AND S.STATISTIC# =N.STATISTIC#),8 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'session_cached_cursors')9 UNION ALL

10 SELECT 'open_cursors',11 LPAD(VALUE, 5),12 TO_CHAR(100 * USED / VALUE, '990') || '%'

13 FROM (SELECT MAX(SUM(S.VALUE)) USED14 FROMV$STATNAME N, V$SESSTAT S15 WHERE N.NAME IN

16 ('opened cursors current', 'session cursor cache count')17 AND S.STATISTIC# =N.STATISTIC#18 GROUP BYS.SID),19 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors');

PARAMETER VALUE USAGE---------------------- ---------- -----

session_cached_cursors 20 100%open_cursors300 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更高的性能.

更新2：

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，在程序中的关闭只是一个软关闭，事实上，在会话中并未关闭，而是放在一个CURSOR缓冲区中。

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

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开始，这个参数就已经是动态的了，可以随时动态调整。