OPEN_CURSOR and v$open_cursor <f…

OPEN_CURSOR

To process a SQL statement, you must have an open cursor. When you call the OPEN_CURSOR Function function, you receive a cursor ID number for the data structure representing a valid cursor maintained by Oracle. These cursors are distinct from cursors defined at the precompiler, OCI, or PL/SQL level, and are used only by the DBMS_SQL package.

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

SESSION CURSOR CACHED
PL/SQL CURSOR CACHED

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


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

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

COUNT(*)
----------
37


or


11g 以前 使用以下查询
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
------------ ----------------------------------------------------------------
35 opened cursors current

对open cursor的监控

WITH a AS (SELECT VALUE init_open_cursors
FROM v$parameter
WHERE name = 'open_cursors'),
b AS (SELECT MAX (max_cursors) curr_max_cursors
FROM ( SELECT MAX (a.VALUE) max_cursors
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'
GROUP BY s.sid))
SELECT ROUND (b.curr_max_cursors / a.init_open_cursors * 100, 2) ratio
FROM a, b;

可以通过以下语句来了解系统中真正意义上的打开着的游标:

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;