如何找出Oracle instance中当前打开游标open cursor的总数？

http://t.askmaclean.com/thread-1302-1-33.html

如何找出Oracle instance中当前打开游标open cursor的总数？

 

v$open_cursor  包括多种cursor:
注意 11.2中  v$open_cursor 才有 cursor_type 这一字段 之前都没有 ， 即无法分清楚 是open cursor 还是cached cursor 

SQL> select distinct cursor_type from v$open_cursor;

CURSOR_TYPE
----------------------------------------------------------------
SESSION CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED
BUNDLE DICTIONARY LOOKUP CACHED


其中 部分是 CACHED的cursor 所以不能算作open cursor 


可以利用以下查询近似 了解系统中 open cursor的总数， 

SQL> select count(*)
  2    from v$open_cursor where cursor_type  in ('OPEN','OPEN-RECURSIVE');

  COUNT(*)
----------
        3

11g 以前 使用以下查询

SQL> 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;

SUM(A.VALUE) NAME
------------ ----------------------------------------
         149 opened cursors current

SQL> select * from v$sysstat where name like '%cursor%';

STATISTIC# NAME                                          CLASS      VALUE    STAT_ID
---------- ---------------------------------------- ---------- ---------- ----------
         2 opened cursors cumulative                         1    5276872   85052502
         3 opened cursors current                            1        149 2301954928
         9 pinned cursors current                            1         22 2771133180
       295 session cursor cache hits                        64    3169224 3678609077
       296 session cursor cache count                       64    1427737  568260813
       321 cursor authentications                          128      28222 4069981174

6 rows selected.

 通过以下脚本 可以获得 某个连接打开的游标数量

select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/

通过以下脚本 可以获得 某个连接 session cached cursor 

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 = 'session cursor cache count'
order by 1
/