ORACLE 判断OPEN_CURSOR参数设置合理性

概念：

OPEN_CURSOR，定义每个Session最大能够打开的游标数量。在init。ora文件中定义，可以通过select ＊ from v＄parameter where name = 'open_cursors’查询。

V＄OPEN_CURSOR，当前Session缓存的游标，而不是曾经打开的游标。 V$SESSION_CACHED_CURSOR，当前Session已经关闭并被缓存的游标。

V＄OPEN_CURSOR中显示的当前Session游标缓存中游标，如果要精确查询当前Session打开的游标总数，需要从V$ SESSTAT中查询.

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 current’;

Session Cache的原理：

当设定SESSION_CACHED_CURSOR的值之后，当有parse请求的时候，Oracle会从library cache中查询。如果有超过3次同样的parse请求，这个游标将会存入Session的游标缓存中。对于将来同样的查询，就甚至不要soft parse，直接从Session的游标缓存中取。

验证：

登录两个SQL*PLUS客户端，分别为Session test和Session monitor。

1. 检查是否参数设置，以及执行的SQL语句是否在V$OPEN_CURSOR找到。

Session Test:

SQL〉 show parameter session_cached_cursors;

NAME TYPE VALUE

—---—-——-——-——-——---—-———--—-- session_cached_cursors integer 0

SQL〉 select sid from v＄mystat where rownum=1; SID

--—--—---— 9

SQL〉 select sid from v＄mystat where rownum=1； SID

---—---—-- 9

通过如上的执行结果可以知道，当前参数设置session_cached_cursors的值为0，不缓存当前Session关闭的游标。当前Session的ID为9.

Session Monitor: SQL> SELECT SID， n。NAME para_name, s。VALUE used 2 FROM SYS。v_＄statname n， SYS.v_$sesstat s

3 WHERE n.NAME IN (’opened cursors current'， ’session cursor cache count’） 4 AND s。statistic＃ = n。statistic# 5 AND SID = 9； SID PARA_NAME USED

——- ——-——-———--————-——----——-—--—- —-----—-—— 9 opened cursors current 1

9 session cursor cache count 0

SQL> select SID, USER_NAME,SQL_TEXT from v$open_cursor where sid=9； SID USER_NAME SQL_TEXT —-- -—-----—-—

——-———-—-----—--——--—-——-—-—--—-———--—-—--—-——-—----—------ 9 SCOTT select sid from v$mystat where rownum=1

通过如上的执行结果可以知道，当前在V$OPEN_CURSOR存储一个游标，对应SQL为Session Test执行的最后一条语句。V$SESSION_CACHED_CURSOR没有存储游标。

2. 更改参数V＄SESSION_CACHED_CURSOR值.

Session Test:

SQL> alter session set session_cached_cursors = 1; Session altered.

SQL> show parameter session_cached_cursors； NAME TYPE VALUE

--——-----—-—-——————--———-——-—————-—- ——--—-—-——— —-———-—-—-———-—--——---—-- session_cached_cursors integer 1

3。 验证如下结论。如果游标被存入SESSION_CACHED_CURSOR，前提是游标已经关闭,游标对应的SQL被执行3次以上。OPEN_CURSOR中会存储保存在SESSION_CACHED_CURSOR以及打开的游标（不是精确值）。

Session Test：

SQL〉 select sid from v＄mystat where rownum =1; SID

-—---—————

9

SQL> select sid from v＄mystat where rownum =1； SID

—-—-—---—— 9

SQL> select sid from v$mystat where rownum =1; SID

-—---——--— 9

Session Monitor：

SQL> select SID， USER_NAME,SQL_TEXT from v$open_cursor where sid=9; SID USER_NAME SQL_TEXT -—- —-—-------

——-—---—--—----—--———-—----———-—-—-—-—-——--——-—-—--——-—-——- 9 SCOTT select sid from v＄mystat where rownum =1

SQL〉 SELECT SID， n。NAME para_name, s。VALUE used 2 FROM SYS.v_$statname n， SYS。v_＄sesstat s

3 WHERE n.NAME IN (’opened cursors current', ’session cursor cache count’） 4 AND s.statistic# = n.statistic# 5 AND SID = 9;

SID PARA_NAME USED

-—- -—-———--——-——--——-—--——-—-—-—- ---—--———— 9 opened cursors current 1 9 session cursor cache count 0

v$open_cursor dooes not show all open cursors. it shows more than that， the best option to find the number of open cursors is from v＄sysstat. 通过如上的执行结果可以知道，即使同一个游标被打开3次,在SESSION_CACHED_CURSOR的数量仍然为0.

下面，将会在Session Test中关闭游标（通过执行一条其他的语句）。

Session Test: SQL> select ＊ from t where rownum!=7； no rows selected

Session Monitor：

SQL> SELECT SID, n.NAME para_name， s.VALUE used 2 FROM SYS。v_＄statname n， SYS.v_＄sesstat s

3 WHERE n.NAME IN （'opened cursors current’， 'session cursor cache count’) 4 AND s。statistic# = n.statistic＃ 5 AND SID = 9；

SID PARA_NAME USED

-—— ---—-—-—---—-———————-——-—----- ——--———--- 9 opened cursors current 1

9 session cursor cache count 1

SQL> select SID, USER_NAME，SQL_TEXT from v＄open_cursor where sid=9； SID USER_NAME SQL_TEXT —-— —-——----——

--—----—————--——————-———-————-——————-—————-———----—-———-—-—

9 SCOTT select sid from v$mystat where rownum=1 9 SCOTT select * from t where rownum！=7

通过如上的执行结果可以知道,游标被打开3次之后，如果这个游标关闭之后，游标会被存储到SESSION_CACHED_CURSOR当中。同时，通过OPEN_CURSOR中显示的SQL可以得知，OPEN_CURSOR中会存储保存在SESSION_CACHED_CURSOR以及打开的游标（不是精确值)。

其他：SESSION_CACHED_CURSOR采用的是LRU算法,如果如果有新的游标需要缓存，而当前游标

缓存已经满,最少使用的游标将会被清除出去。调整SESSION_CACHED_CURSOR参数.通过如下SQL得到从缓存中取游标以及取PARSE的数量，为调整作参考。

select cach.value cache_hits， prs。value all_parses, prs.value-cach.value sess_cur_cache_not_used from v$sesstat cach， v＄sesstat prs， v$statname nm1, v$statname nm2

where cach。statistic# = nm1。statistic＃ and nm1.name = ’session cursor cache hits' and prs。statistic#=nm2.statistic# and nm2。name= ’parse count (total)’ and cach.sid= &sid and prs。sid= cach。sid ;