OPEN_CURSOR and v$open_cursor <f…

Oracle游标监控

最新推荐文章于 2023-04-18 16:30:00 发布

最新推荐文章于 2023-04-18 16:30:00 发布 · 174 阅读

文章标签：

#数据库

本文介绍如何在Oracle数据库中监控打开的游标数量，包括通过v$open_cursor视图查询不同类型的游标，并提供针对不同Oracle版本的具体SQL语句。

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;