【DBA 脚本】查询current open cursor的脚本

最新推荐文章于 2022-08-06 10:00:57 发布

转载最新推荐文章于 2022-08-06 10:00:57 发布 · 66 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/29848

本文介绍了一个Oracle自定义视图user_cursors的创建过程，该视图用于统计每个会话的递归调用次数、累计打开游标数及当前打开游标数，帮助管理员监控数据库性能。

show parameter session_cached_cursor
drop view user_cursors;
create view user_cursors as
select
ss.username||'('||se.sid||') ' user_process, sum(decode(name,'recursive calls',value)) "Recursive Calls",
sum(decode(name,'opened cursors cumulative',value)) "Opened Cursors", sum(decode(name,'opened cursors current',value)) "Current Cursors"
      from v$session ss, v$sesstat se, v$statname sn
where  se.statistic# = sn.statistic#
            and (    name  like '%opened cursors current%'
                              OR name  like '%recursive calls%'
                              OR name  like '%opened cursors cumulative%')
            and  se.sid = ss.sid
            and    ss.username is not null
group by ss.username||'('||se.sid||') ';

ttitle 'Per Session Current Cursor Usage '
column USER_PROCESS format a25;
column "Recursive Calls" format 999,999,999;
column "Opened Cursors"  format 99,999;
column "Current Cursors"  format 99,999;

select * from user_cursors
order by "Recursive Calls" desc;