当前位置：文江博客话题详情

如何在Oracle中查找当前打开的游标

发布于 2024-07-25 16:43:56 字数 94 浏览 3 评论 0原文

用于查找 Oracle 实例中当前打开的游标数量的查询是什么？

另外，该数据的准确性/更新频率是多少？

我使用的是Oracle 10gR2

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

维持三分热 2024-08-01 16:43:56

按会话打开的游标总数：

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

来源：http://www.orafaq.com/node/758

据我所知，对 v$ 视图的查询基于直接指向 SGA 相关部分的伪表（“x$”表），因此您无法获得比这更准确的信息；然而，这也意味着它是时间点的（即脏读）。

Total cursors open, by session:

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

Source: http://www.orafaq.com/node/758

As far as I know queries on v$ views are based on pseudo-tables ("x$" tables) that point directly to the relevant portions of the SGA, so you can't get more accurate than that; however this also means that it is point-in-time (i.e. dirty read).

回复收藏 0 原文

忆沫 2024-08-01 16:43:56

select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

似乎对我有用。

select  sql_text, count(*) as "OPEN CURSORS", user_name from v$open_cursor
group by sql_text, user_name order by count(*) desc;

appears to work for me.

回复收藏 0 原文

江挽川 2024-08-01 16:43:56

以下是如何查找已解析的打开游标。您需要以有权访问 v$open_cursor 和 v$session 的用户身份登录。

COLUMN USER_NAME FORMAT A15

SELECT s.machine, oc.user_name, oc.sql_text, count(1) 
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC
;

如果为您提供了部分 SQL 文本，因此它对于识别存在泄漏的应用程序很有用。如果游标尚未被解析，则它不会出现在此处。请注意，Oralce 有时会比您保持打开状态的时间更长。

Here's how to find open cursors that have been parsed. You need to be logged in as a user with access to v$open_cursor and v$session.

COLUMN USER_NAME FORMAT A15

SELECT s.machine, oc.user_name, oc.sql_text, count(1) 
FROM v$open_cursor oc, v$session s
WHERE oc.sid = s.sid
GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2
ORDER BY count(1) DESC
;

If gives you part of the SQL text so it can be useful for identifying leaky applications. If a cursor has not been parsed, then it does not appear here. Note that Oralce will sometimes keep things open longer than you do.

回复收藏 0 原文

淡墨 2024-08-01 16:43:56

1)你的id应该有sys dba访问权限
2）

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
 s.username, s.machine
 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.username, s.machine
 order by 1 desc;

1)your id should have sys dba access
2)

select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
 s.username, s.machine
 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.username, s.machine
 order by 1 desc;

回复收藏 0 原文

神魇的王 2024-08-01 16:43:56

Oracle 有一个针对此问题的页面，其中包含 SQL 和故障排除建议。

“解决打开光标问题”
http://docs.oracle.com/cd/E40329_01 /admin.1112/e27149/cursor.htm#OMADM5352

回复收藏 0 原文

や莫失莫忘 2024-08-01 16:43:56

我将使用这个快速 SQL 来比较当前的最高使用量与允许的最大值。这将使您能够立即查看是否有任何进程对游标的使用率达到了危险的程度。

SELECT MAX (a.VALUE) AS highest_open_cur, p.VALUE AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'
AND p.name = 'open_cursors'
GROUP BY p.VALUE;

从上面的 SQL 中可以清楚地看出，OPEN_CURSORS 值是一个 Oracle 参数，可以在运行时找到，如下所示。

SELECT * FROM v$parameter WHERE NAME = 'open_cursors';

您可能需要具有 sysdba 或同等权限才能查询上表。

I would use this quick SQL to compare the highest current use compared to the max allowed. This will allow you to immediately see if any process has a dangerously high use of cursors.

SELECT MAX (a.VALUE) AS highest_open_cur, p.VALUE AS max_open_cur
FROM v$sesstat a, v$statname b, v$parameter p
WHERE a.statistic# = b.statistic#
AND b.name = 'opened cursors current'
AND p.name = 'open_cursors'
GROUP BY p.VALUE;

As is probably clear from the above SQL, the OPEN_CURSORS value is an Oracle parameter and can be found at runtime like this.

SELECT * FROM v$parameter WHERE NAME = 'open_cursors';

You may need to be sysdba or equivalent to query the above tables.

回复收藏 0 原文

相守太难 2024-08-01 16:43:56

我用这样的东西：

select 
  user_name, 
  count(*) as "OPEN CURSORS" 
from 
  v$open_cursor 
group by 
  user_name;

I use something like this:

select 
  user_name, 
  count(*) as "OPEN CURSORS" 
from 
  v$open_cursor 
group by 
  user_name;

回复收藏 0 原文

秋千易 2024-08-01 16:43:56

这可以工作：

SELECT    sql_text "SQL Query", 
          Count(*) AS "Open Cursors" 
FROM      v$open_cursor 
GROUP BY  sql_text 
HAVING    Count(*) > 2 
ORDER BY  Count(*) DESC;

This could work:

SELECT    sql_text "SQL Query", 
          Count(*) AS "Open Cursors" 
FROM      v$open_cursor 
GROUP BY  sql_text 
HAVING    Count(*) > 2 
ORDER BY  Count(*) DESC;

回复收藏 0 原文

~没有更多了~