OPEN_CURSOR and v$open_cursor <f…
来源:互联网 发布:js实现上一页下一页 编辑:程序博客网 时间:2024/05/21 00:50
OPEN_CURSOR
To process a SQLstatement, you must have an open cursor. When you call the OPEN_CURSOR Function function, you receive a cursorID
number for the data structure representing a validcursor maintained by Oracle. These cursors are distinct fromcursors defined at the precompiler, OCI, or PL/SQL level, and areused only by the DBMS_SQL
package.
v$open_cursor
包括多种cursor:
注意 11.2中 v$open_cursor 才有 cursor_type 这一字段 之前都没有 , 即无法分清楚 是opencursor 还是cached cursor
SQL> select distinct cursor_type fromv$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;
- OPEN_CURSOR and v$open_cursor <f…
- Why ASMLIB and why not? <转载…
- v$open_cursor与session_cached_cu…
- 理解V$OPEN_CURSOR
- 关于V$OPEN_CURSOR
- perl sort <=> and cmp
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- FSB Extractor - F…
- undocumented views
- solaris常用命令
- c++指针|指针入门
- ORA-00119 Invalid specification …
- SEGMENT SHRINK and details
- OPEN_CURSOR and v$open_cursor <f…
- Windows 2003 R2 上安装oracle 10g…
- ORA-02778 : Name given for the l…
- oracle 11g 新参数 MEMORY_TARGET
- Linux ( kernel<<2.6.18-164 )开启…
- Install or Upgrade VMware Tools …
- AIX6.1TL7 11gR2 RAC 安装me…
- Oracle_AWR_报告分析实例讲…
- 验证concepts中关于sga_target与sg…