Oracle查看 open_cursors 和 session_cached_cursors
本文转自 http://blog.itpub.net/25583515/viewspace-2152997/
查看当前session已使用的最大open cursor数 和cached cursor数:
SELECT ‘session_cached_cursors‘ PARAMETER, LPAD (VALUE, 5) VALUE, DECODE (VALUE, 0, ‘ n/a‘, TO_CHAR (100 * USED / VALUE, ‘990‘) || ‘%‘) USAGE FROM (SELECT MAX (S.VALUE) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME = ‘session cursor cache count‘ AND S.STATISTIC# = N.STATISTIC#), (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘session_cached_cursors‘) UNION ALL SELECT ‘open_cursors‘, LPAD (VALUE, 5), TO_CHAR (100 * USED / VALUE, ‘990‘) || ‘%‘ FROM ( SELECT MAX (SUM (S.VALUE)) USED FROM V$STATNAME N, V$SESSTAT S WHERE N.NAME IN (‘opened cursors current‘, ‘session cursor cache count‘) AND S.STATISTIC# = N.STATISTIC# GROUP BY S.SID), (SELECT VALUE FROM V$PARAMETER WHERE NAME = ‘open_cursors‘);
注:如果查询的session open cursor到达100% client就很可能报错ORA-01000
查看session open cursor的具体程式、open cursor数量:
SELECT a.inst_id, a.sid, a.USERNAME, a.SCHEMANAME, a.OSUSER, a.machine, a.TERMINAL, a.LOGON_TIME, a.PROGRAM, a.STATUS, b.name, b.used FROM gv$session a, (SELECT n.inst_id, sid, n.name, s.VALUE used FROM gv$statname n, gv$sesstat s WHERE n.name IN (‘opened cursors current‘, ‘session cursor cache count‘) AND s.statistic# = n.statistic# AND n.inst_id = s.inst_id) b WHERE a.sid = b.sid AND a.inst_id = b.inst_id AND b.name <> ‘session cursor cache count‘ ORDER BY b.used DESC;
查看session open cursor的具体SQL(此方法是联合v$open_cursor得出的结果,可能有不准确。但session最多open_cursors的SQL一定是在运行结果中)
SELECT distinct a.inst_id, a.sid, a.USERNAME, a.SCHEMANAME, a.OSUSER, a.machine, a.TERMINAL, a.LOGON_TIME, a.PROGRAM, a.STATUS, b.name, b.used,c.sql_id FROM gv$session a, (SELECT n.inst_id, sid, n.name, s.VALUE used FROM gv$statname n, gv$sesstat s WHERE n.name IN (‘opened cursors current‘, ‘session cursor cache count‘) AND s.statistic# = n.statistic# and n.inst_id=s.inst_id ) b,v$open_cursor c WHERE a.sid = b.sid and a.inst_id = b.inst_id and a.sid=c.sid and c.CURSOR_TYPE in(‘OPEN‘,‘OPEN-PL/SQL‘,‘OPEN-RECURSIVE‘) and b.name <> ‘session cursor cache count‘ order by b.used desc;
相关推荐
oraclemch 2020-11-06
solarspot 2020-07-28
houmenghu 2020-11-17
我心似明月 2020-11-09
ltd00 2020-09-12
康慧欣 2020-09-10
waveclouds 2020-09-04
蓝色深海 2020-09-15
jincheng 2020-09-01
思君夜未眠 2020-08-25
取个好名字真难 2020-08-06
歆萌 2020-08-03
阳光之吻 2020-08-03
婷婷小屋 2020-07-28
MLXY 2020-07-26
dxbjfu0 2020-07-26
songbinxu 2020-07-19
83520298 2020-07-06