ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 用表函数实现OTOP 工具

用表函数实现OTOP 工具

原创 Linux操作系统 作者:westzq1984 时间:2012-04-06 10:56:01 0 删除 编辑
一直觉得dcba写的otop很好用,最近用表函数实现了下这个工具

set pagesize 0 linesize 170 arraysize 1

select * from table(topsess('redo'));

--------------------------------------------------------------------

CREATE OR REPLACE FUNCTION TOPSESS(AVC_STATS   VARCHAR2,
                   AN_INTERVAL NUMBER DEFAULT 60,
                   AN_COUNT    NUMBER DEFAULT 100000)
/******************************************************************************
    TOPSESS 类OTOP工具
    Created by zhangqiaoc@olm.com.cn
 
    INPUT KEY:
      AVC_STATS  : 监控的统计值,GETS/CPUT/DISK/SORT/WAIT/REDO/EXEC/CBLK/BMOD
      AN_INTERVAL: 监控间隔,默认60秒
      AN_COUNT   : 监控的次数,默认100000次
  ******************************************************************************/
  --RETURN SYSMAN.VARCHAR2_TABLE
 RETURN DBMS_STATS.CHARARRAY
  PIPELINED IS
  LVC_SQL_SYSSTAT   VARCHAR2(4000);
  LVC_SQL_SESSTAT   VARCHAR2(4000);
  LVC_SQL_SESSTAT9  VARCHAR2(4000);
  LVC_SQL_SESSTAT10 VARCHAR2(4000);
  LVC_SQL_INFO      VARCHAR2(4000);
  LVC_SQL_VERSION   VARCHAR2(4000);
  LVC_SQL_STATS#    VARCHAR2(4000);
  LVC_DBNAME        VARCHAR2(30);
  LVC_HOST          VARCHAR2(30);
  LN_CPUS           NUMBER;
  LN_VERSION        NUMBER;
  LVC_OUTPUT        VARCHAR2(200);
  LN_OB             NUMBER;
  LN_RN             NUMBER;
  LVC_STATS         VARCHAR2(100);
  LN_TOTAL          NUMBER;

  TYPE TABLE_SYSSTAT IS RECORD(
    STATISTIC# NUMBER,
    NAME       VARCHAR2(100),
    VALUE      NUMBER,
    DPNAME     VARCHAR2(20));
  TYPE TYPE_SYSSTAT IS TABLE OF TABLE_SYSSTAT INDEX BY BINARY_INTEGER;
  SYSSTAT_1 TYPE_SYSSTAT; -- SYSTEM统计,前一个级别
  SYSSTAT_2 TYPE_SYSSTAT; -- SYSTEM统计,后一个级别

  TYPE TABLE_SESSTAT IS RECORD(
    SID      NUMBER,
    SERIAL   NUMBER,
    VAL      NUMBER,
    MACHINE  VARCHAR2(100),
    CURR_SQL VARCHAR2(20));
  TYPE TYPE_SESTAT IS TABLE OF TABLE_SESSTAT INDEX BY BINARY_INTEGER;
  SESSTAT_1    TYPE_SESTAT; -- 上上次采样,SID为下标
  SESSTAT_2    TYPE_SESTAT; -- 上一次采样,SID为下标
  SESSTAT_3    TYPE_SESTAT; -- 当前采样, SID为下标
  SESSTAT_0    TYPE_SESTAT; -- 计算值,VAL值为下标
  SESSTAT_T    TYPE_SESTAT; -- SQL取出值
  SESSTAT_NULL TYPE_SESTAT;

  /**
    计算每个间隔内的系统统计的增量,按秒统计
    先检查传入的STATISTIC# 和 查出来的值是否匹配,用于应对以后STATISTIC#变化时,可以及时发现
    INPUT KEY
      LN_STAT  STATISTIC#
      LN_RN    嵌套表中的序号
  **/
  FUNCTION F_CALC_SYSSTAT(LN_STAT NUMBER, LN_RN NUMBER) RETURN NUMBER IS
  BEGIN
    IF SYSSTAT_2(LN_RN).STATISTIC# = LN_STAT AND SYSSTAT_1(LN_RN)
    .STATISTIC# = LN_STAT THEN
      RETURN(SYSSTAT_2(LN_RN).VALUE - SYSSTAT_1(LN_RN).VALUE) / AN_INTERVAL;
    ELSE
      RETURN - 1;
    END IF;
  END;

  /**
    格式化数字,以按照K,M显示
    INPUT KEY
      LN_TMP   数字
  **/
  FUNCTION F_FM_NUMBER(LN_TMP NUMBER) RETURN VARCHAR2 IS
  BEGIN
    IF LN_TMP < 1024 THEN
      RETURN ROUND(LN_TMP, 0);
    ELSIF LN_TMP >= 1024 AND LN_TMP < 1024 * 1024 THEN
      RETURN ROUND(LN_TMP / 1024, 0) || 'K';
    ELSIF LN_TMP >= 1024 * 1024 THEN
      RETURN ROUND(LN_TMP / 1024 / 1024, 0) || 'M';
    END IF;
  END;

  /**
    构造SESSTAT_0,SESSTAT_3 两个嵌套表
    SESSTAT_3 保存当前查询出的SESSION统计,下标为SID
    SESSTAT_0 保存经过排序的TOP 10 SESSION统计
    需要先对SESSTAT_T进行填充,在调用该方法构造相关对象
  **/
  PROCEDURE P_RESTRUCT_SESSTAT IS
    LN_VAL   NUMBER;
    LN_VAL_2 NUMBER;
  BEGIN
    -- 初始化SESSTAT_0
    SESSTAT_0 := SESSTAT_NULL;
    FOR J IN 1 .. 10 LOOP
      SESSTAT_0(J).VAL := 0;
    END LOOP;
 
    -- 从SESSTAT_T中构造出SESSTAT_3,SESSTAT_0
    FOR J IN 1 .. SESSTAT_T.COUNT LOOP
      -- 构造SESSTAT_3,KEY为SID
      SESSTAT_3(SESSTAT_T(J).SID) := SESSTAT_T(J);
   
      -- 计算SESSTAT_0中的VALUE值
      IF SESSTAT_2.EXISTS(SESSTAT_T(J).SID) THEN
    LN_VAL_2 := SESSTAT_2(SESSTAT_T(J).SID).VAL;
      ELSE
    LN_VAL_2 := 0;
      END IF;
   
      LN_VAL := SESSTAT_T(J).VAL - LN_VAL_2;
   
      -- 构造SESSTAT_0,按VAL排序,保留10条
      FOR K IN 1 .. SESSTAT_0.COUNT LOOP
    IF LN_VAL > SESSTAT_0(K).VAL THEN
      -- 数组向后移一位
      FOR M IN REVERSE 9 .. K LOOP
        SESSTAT_0(M + 1) := SESSTAT_0(M);
      END LOOP;
      SESSTAT_0(K) := SESSTAT_3(SESSTAT_T(J).SID);
      SESSTAT_0(K).VAL := LN_VAL;
      GOTO END1;
    END IF;
      END LOOP;
      <>
      NULL;
    END LOOP;
  END;

  /**
    格式化SESSTAT的输出
    INPUT KEY
      LN_N        SESSTAT_0 的下标,需要处理的行
    RETURN
      LVC_RETURN  一串字符
  **/
  FUNCTION F_FM_SESSTAT_OUTPUT(LN_N NUMBER) RETURN VARCHAR2 IS
    LVC_RETURN   VARCHAR2(4000);
    LVC_PREVSQL2 VARCHAR2(20);
    LVC_PREVSQL1 VARCHAR2(20);
  BEGIN
    IF SESSTAT_1.EXISTS(SESSTAT_0(LN_N).SID) THEN
      LVC_PREVSQL2 := SESSTAT_1(SESSTAT_0(LN_N).SID).CURR_SQL;
    END IF;
 
    IF SESSTAT_2.EXISTS(SESSTAT_0(LN_N).SID) THEN
      LVC_PREVSQL1 := SESSTAT_2(SESSTAT_0(LN_N).SID).CURR_SQL;
    END IF;
 
    LVC_RETURN := ' ' || LPAD(SESSTAT_0(LN_N).SID, 5, ' ') || ' ' ||
          LPAD(SESSTAT_0(LN_N).SERIAL, 6, ' ') || ' ' ||
          LPAD(F_FM_NUMBER(SESSTAT_0(LN_N).VAL), 6, ' ') || ' ' ||
          LPAD(F_FM_NUMBER(SESSTAT_0(LN_N).VAL / AN_INTERVAL),
               6,
               ' ') || LPAD(TO_CHAR(SESSTAT_0(LN_N)
                        .VAL / AN_INTERVAL / LN_TOTAL * 100,
                        999.99),
                    7,
                    ' ') || ' ' ||
          LPAD(NVL(LVC_PREVSQL2, ' '), 13, ' ') || ' ' ||
          LPAD(NVL(LVC_PREVSQL1, ' '), 13, ' ') || ' ' ||
          LPAD(NVL(SESSTAT_0(LN_N).CURR_SQL, ' '), 13, ' ') || '  ' ||
          SESSTAT_0(LN_N).MACHINE;
    RETURN LVC_RETURN;
  END;

BEGIN

  LVC_SQL_SYSSTAT   := 'SELECT A.STATISTIC#,A.NAME,B.VALUE,
            DECODE(A.NAME,''logons cumulative'',''logon'',''opened cursors cumulative'',''open cursors'',''user commits'',''user commits'',''user rollbacks'',''rollback'',''user calls'',''user calls'',''CPU used by this session'',''CPU time'',
                      ''enqueue waits'',''enqueue wait'',''db block gets'',''db block get'',''consistent gets'',''consist get'',''physical reads'',''phy read'',''physical reads direct'',''Direct reads'',''db block changes'',''DB Changes'',
                      ''physical writes'',''phy write'',''physical writes direct'',''Direct write'',''free buffer requested'',''free buf req'',''dirty buffers inspected'',''Dirty inspec'',''free buffer inspected'',''free buf ins'',''CR blocks created'',
                      ''CR created'',''physical reads direct (lob)'',''LOB read'',''physical reads direct temporary tablespace'',''Temp Read'',''physical writes direct (lob)'',''LOB write'',''physical writes direct temporary tablespace'',''Temp Write'',
                      ''redo size'',''redo size'',''redo blocks written'',''redo write'',''gc cr blocks served'',''HA cr blocks'',''gc cr block send time'',''HA cr STime'',''gc current blocks served'',''HA xr blocks'',''gc current block send time'',''HA xr STime'',
                      ''gc blocks lost'',''HA blks lost'',''table scans (direct read)'',''table direct'',''table scan rows gotten'',''Rows scan'',''table fetch by rowid'',''Rows by Indx'',''parse count (total)'',''Total parse'',''parse count (hard)'',''Hard parse'',
                      ''execute count'',''exec count'',''bytes sent via SQL*Net to client'',''byte SQL*Net'',''sorts (memory)'',''sort memory'',''sorts (disk)'',''sort disk'',NULL) DPNAME
             FROM V$STATNAME A,V$SYSSTAT B
            WHERE A.NAME IN(''logons cumulative'', ''opened cursors cumulative'',''user commits'', ''user rollbacks'', ''user calls'',''CPU used by this session'', ''enqueue waits'',''db block gets'', ''consistent gets'', ''physical reads'',''physical reads direct'', ''db block changes'',
                    ''physical writes'', ''physical writes direct'',''free buffer requested'', ''dirty buffers inspected'',''free buffer inspected'', ''CR blocks created'',''physical reads direct (lob)'',''physical reads direct temporary tablespace'',
                    ''physical writes direct (lob)'',''physical writes direct temporary tablespace'',''redo size'', ''redo blocks written'', ''gc cr blocks served'',''gc cr block send time'', ''gc current blocks served'',''gc current block send time'', ''gc blocks lost'',
                    ''table scans (direct read)'', ''table scan rows gotten'',''table fetch by rowid'', ''parse count (total)'',''parse count (hard)'', ''execute count'',''bytes sent via SQL*Net to client'', ''sorts (memory)'',''sorts (disk)'')
              AND A.STATISTIC# = B.STATISTIC# ORDER BY A.STATISTIC#';
  LVC_SQL_SESSTAT9  := 'SELECT S.SID,S.SERIAL#,T.VALUE,S.MACHINE || ''('' || S.USERNAME || '')'',S.SQL_HASH_VALUE FROM V$SESSION S, V$SESSTAT T WHERE S.SID = T.SID AND s.sid <> (select sid from v$mystat where rownum< 2) AND T.STATISTIC# = :A ORDER BY S.SID';
  LVC_SQL_SESSTAT10 := 'SELECT S.SID,S.SERIAL#,T.VALUE,S.MACHINE || ''('' || S.USERNAME || '')'',S.SQL_ID         FROM V$SESSION S, V$SESSTAT T WHERE S.SID = T.SID AND s.sid <> (select sid from v$mystat where rownum< 2) AND T.STATISTIC# = :A ORDER BY S.SID';
  LVC_SQL_INFO      := 'SELECT SYS_CONTEXT(''USERENV'', ''DB_NAME'') db_name,SYS_CONTEXT(''USERENV'', ''HOST'') host,VALUE CPUS from v$parameter WHERE NAME = ''cpu_count''';
  LVC_SQL_VERSION   := 'SELECT substr(version,1,instr(version,''.'')-1) FROM v$instance';
  LVC_SQL_STATS#    := 'select max(STATISTIC#) from V$STATNAME where NAME = :A';

  -- 对应条件的name
  LVC_STATS := CASE UPPER(AVC_STATS) WHEN 'GETS' THEN 'consistent gets' WHEN 'CPUT' THEN 'CPU used by this session' WHEN 'DISK' THEN 'physical reads' WHEN 'SORT' THEN 'sorts (disk)' WHEN 'WAIT' THEN 'enqueue waits' WHEN 'REDO' THEN 'redo size' WHEN 'EXEC' THEN 'execute count' WHEN 'CBLK' THEN 'CR blocks created' WHEN 'BMOD' THEN 'db block changes' ELSE NULL END;

  EXECUTE IMMEDIATE LVC_SQL_INFO
    INTO LVC_DBNAME, LVC_HOST, LN_CPUS;
  EXECUTE IMMEDIATE LVC_SQL_VERSION
    INTO LN_VERSION;
  EXECUTE IMMEDIATE LVC_SQL_STATS#
    INTO LN_OB
    USING LVC_STATS;

  IF LN_VERSION >= 10 THEN
    LVC_SQL_SESSTAT := LVC_SQL_SESSTAT10;
  ELSE
    LVC_SQL_SESSTAT := LVC_SQL_SESSTAT9;
  END IF;

  IF LVC_STATS IS NULL THEN
    PIPE ROW('   Order by (GETS/CPUT/DISK/SORT/WAIT/REDO/EXEC/CBLK/BMOD)');
    GOTO END_FLAG;
  END IF;

  FOR I IN 0 .. AN_COUNT LOOP
 
    SYSSTAT_1 := SYSSTAT_2;
    SESSTAT_1 := SESSTAT_2;
    SESSTAT_2 := SESSTAT_3;
 
    EXECUTE IMMEDIATE LVC_SQL_SYSSTAT BULK COLLECT
      INTO SYSSTAT_2;
    EXECUTE IMMEDIATE LVC_SQL_SESSTAT BULK COLLECT
      INTO SESSTAT_T
      USING LN_OB;
 
    -- 构造SESSTAT_3,SID为该集合的下标
    -- 构造SESSTAT_0
    P_RESTRUCT_SESSTAT;
 
    IF I > 0 THEN
      PIPE ROW(' OTop ( Host:' || LVC_HOST || ' DBName:' || LVC_DBNAME ||
           ' CPUs:' || LN_CPUS || ' Interval:' || AN_INTERVAL || 's ' ||
           TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') || ' ) Order by ' ||
           UPPER(AVC_STATS) || '  ' || I);
      PIPE ROW('-Statistics (Per Second Value)------------------------------------------------------------------');
   
      -- 输出系统统计
      FOR J IN 1 .. SYSSTAT_2.COUNT LOOP
    LVC_OUTPUT := LVC_OUTPUT ||
              LPAD(F_FM_NUMBER((SYSSTAT_2(J).VALUE - SYSSTAT_1(J)
                       .VALUE) / AN_INTERVAL),
               5,
               ' ') || ' ' ||
              RPAD(SYSSTAT_2(J).DPNAME, 13, ' ');
    IF SYSSTAT_2(J).NAME = LVC_STATS THEN
      LN_RN := J;
    END IF;
     
    IF MOD(J, 5) = 0 OR J = SYSSTAT_2.COUNT THEN
      PIPE ROW(LVC_OUTPUT);
      LVC_OUTPUT := NULL;
    END IF;
      END LOOP;
   
      PIPE ROW('');
   
      PIPE ROW('---SID-Serial--VALUE--VAL/S------%------PrevSQL2------PrevSQL1-------CurrSQL--Machine-----------');
   
      -- 监控指标的TOTAL值,用于计算百分比
      LN_TOTAL := F_CALC_SYSSTAT(LN_OB, LN_RN);
      LN_TOTAL := CASE LN_TOTAL WHEN 0 THEN 1 ELSE LN_TOTAL END;
   
      -- 输出TOP SESSION
      FOR J IN 1 .. SESSTAT_0.COUNT LOOP
    -- 判断VAL是否为0,为0不输出
    IF SESSTAT_0(J).VAL > 0 THEN
      -- 可能生成SESSTAT_0时有问题,用于去重复数据
      IF (J > 1 AND SESSTAT_0(J - 1).SID <> SESSTAT_0(J).SID) OR J = 1 THEN
        PIPE ROW(F_FM_SESSTAT_OUTPUT(J));
      END IF;
    END IF;
      END LOOP;
    END IF;
 
    PIPE ROW('');
    PIPE ROW('');
    DBMS_LOCK.SLEEP(AN_INTERVAL);
  END LOOP;

  <>
  NULL;
END TOPSESS;
/

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-720509/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    941336