Oracle数据库游标数总结

最新推荐文章于 2021-11-07 21:59:26 发布

凤舞飘伶

最新推荐文章于 2021-11-07 21:59:26 发布

阅读量1.4k

点赞数

分类专栏： oracle

本文链接：https://blog.csdn.net/woshaguayi/article/details/116661864

版权

oracle 专栏收录该内容

64 篇文章 3 订阅

订阅专栏

各用户的打开游标总数
SELECT A.USER_NAME, COUNT(*) FROM V$OPEN_CURSOR A GROUP BY A.USER_NAME;

查找数据库各用户各个终端的缓存游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE) 
FROM (
SELECT A.VALUE, S.MACHINE, S.USERNAME 
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S 
WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'session cursor cache count') AA 
GROUP BY AA.USERNAME, AA.MACHINE 
ORDER BY AA.USERNAME, AA.MACHINE;

查找数据库各用户各个终端的打开游标数
SELECT AA.USERNAME, AA.MACHINE, SUM(AA.VALUE)
FROM (
SELECT A.VALUE, S.MACHINE, S.USERNAME 
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') AA 
GROUP BY AA.USERNAME, AA.MACHINE 
ORDER BY AA.USERNAME, AA.MACHINE;


查看游标使用情况
select o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type, count(*) num_curs 
from v$open_cursor o, v$session s 
where user_name like 'BIM%' and o.sid = s.sid 
group by o.sid, osuser, machine,o.sql_id,o.sql_text,o.cursor_type 
order by num_curs desc;

查看当前打开的游标数目
select count(*) from v$open_cursor; 
查看缓存游标数目
show parameter session_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');

SQL>  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  2    3    4    5    6    7    8    9   10   11   12  (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.STA 13   14   15   16   17  TISTIC#
           GROUP BY S.SID),
          (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'open_cursors'); 18   19  

PARAMETER	       VALUE		    USAGE
---------------------- -------------------- -----
session_cached_cursors	  50		     100%
open_cursors		 300		      18%

SQL>

 查询游标使用排名
 select SID,count(*) from v$open_cursor O WHERE O.user_name like 'BIM%' GROUP BY O.SID
ORDER BY 2 DESC;
----查询游标使用情况以及游标最大数----
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;