理解游标CURSOR,OPEN_CURSORS参数 以及视图V$OPEN_CURSOR, V$SESSION_CACHED_CURSOR

游标概念： 
游标的作用就是用于临时存储从数据库中提取的数据块，由系统或用户以变量的形式定义。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
Cursor游标分两种，一种是Shared cursor，位于SGA的一种library cache object，通常我们所说的SQL的父cursor，child cursor就是指这一类； 另一种是Session cursor，是SQL的一个内存工作区（或者内存结构），位于PGA的UGA部分，为了使每一个SQL的会话拥有单独的1个私有SQL区(PrivateSQL Area)，一次只处理1个SQL，私有SQL区包含了绑定变量信息及运行时期内存结构。一个session cursor只能对应一个shared cursor，而一个shared cursor却可能同时对应多个session cursor。通常open_cursors参数配置的便是1个session的最大Session cursors。即1个session最多可以拥有多少个PrivateSQL Area，直白一点就是1个session最多能在UGA保存多少个不同的SQL语句的信息(包括绑定变量信息，与Shared cursor的关联信息)，超过则一些执行频度低的游标会关闭。
私有SQL区包括 
永久区：包含绑定变量信息。当游标关闭时被释放。
运行区：当执行结束时释放。
“软软解析”：
  当某个session cursor和其对应的shared cursor建立关联后，如果把cursor_space_for_time调成true（有利有弊，需要SGA和PGA都足够大），当一个session cursor处理完一条sql后，它就不会被destroy，Oracle会把其cache起来（我们称之为soft closed session cursor），这么做的目的是很明显的，因为这个soft closed掉的sessioncursor已经和包含其执行计划和parse tree的shared cursor建立了联系，那么当在这个session中再次执行同样的sql的时候，Oracle就不再需要去扫描library cache了，直接把刚才已经soft closed掉的session cursor拿过来用就好了，这就是所谓的软软解析。
查询opened cursors
 OPEN_CURSOR，定义每个Session最大能够打开的游标数量。在init.ora文件中定义，可以通过select * from v$parameter where name = 'open_cursors'查询。 
查询真正意义上的打开着的游标:
select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;


 
 
V$OPEN_CURSOR，包含多种CURSOR_TYPE的游标SESSION CURSOR CACHED，包括；可以通过以下SQL查询当前系统的游标类型及数量：
select kgllkmod, kgllkctp, count(*)      from X$KGLLK     where KGLHDNSP = 0     group by kgllkmod, kgllkctp;  KGLLKMOD KGLLKCTP                      COUNT(*)   ---------- --------------------------- ----------         1 SESSION CURSOR CACHED              32         1 DICTIONARY LOOKUP CURSOR CACHED   96         1 OPEN-RECURSIVE                      42         1 PL/SQL CURSOR CACHED               19         1 OPEN                                 216  
而不是曾经打开的游标。 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 ;