用表函式實現OTOP 工具

westzq1984發表於2012-04-06

一直覺得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/，如需轉載，請註明出處，否則將追究法律責任。

（函式）實現strstr函式
2017-02-04
函式
一些很實用util工具函式
2021-09-09
函式
用匯編實現add函式
2018-09-24
函式
用自訂函式實現功能-------根據指定表名得到表的結構 (轉)
2007-12-14
函式
用Map+函式式介面來實現策略模式
2020-06-24
函式模式
用ORACLE分析函式實現行列轉換
2010-08-18
Oracle函式
用函式正規化實現戰略模式
2019-01-30
函式模式
用雲函式快速實現圖片爬蟲
2018-11-02
函式爬蟲
用虛擬函式實現事件驅動！ (轉)
2007-12-11
函式事件
函式式工具
2013-01-28
函式
前端簡潔並實用的工具類函式封裝
2018-03-06
前端函式封裝
使用Chrome開發者工具研究JavaScript裡函式的原生實現
2020-07-12
ChromeJavaScript函式
虛擬函式表-C++多型的實現原理
2021-02-01
函式C++多型
MySQL排名函式實現
2020-07-22
MySql函式
MySQL分析函式實現
2018-10-29
MySql函式
fcntl函式實現dup
2020-12-03
函式
用 splice函式分別實現 push、pop、shift、unshi
2021-09-09
函式
Zepto 原始碼分析 3 - qsa 實現與工具函式設計
2018-11-19
原始碼函式
PostgreSQL LIST分割槽實現：繼承表+函式+觸發器。
2018-12-07
SQL繼承函式觸發器
JavaScript的迭代函式與迭代函式的實現
2019-01-07
JavaScript函式
函式節流、函式防抖實現原理分析
2017-02-21
函式
實用函式式 Java （PFJ）簡介
2021-11-12
函式Java
ORACLE 實用函式總結
2015-01-19
Oracle函式
async 函式的實現原理
2020-02-25
函式
去抖函式的實現
2019-03-06
函式
Golang實現PHP常用函式
2021-01-18
GolangPHP函式
Go實現PHP常用函式
2021-01-18
GoPHP函式
bind 函式的實現原理
2018-07-04
函式
mysql實現開窗函式
2016-08-06
MySql函式
atoi函式簡單實現
2016-07-10
函式
實現c中memcpy函式
2017-07-31
memcpy函式
【面試】shuffle函式的實現
2016-03-26
面試函式
js實現函式過載
2024-05-08
JS函式
用listagg函式分組實現列轉行
2019-02-13
函式
實現call函式,手寫Function.prototype.call函式
2020-10-18
函式Function
虛擬函式，虛擬函式表
2018-08-07
函式
使用函式式實現觀察者模式模式
2019-01-30
函式模式
（函式分治法）實現pow函式（x的y次方冪）
2017-02-06
函式

用表函式實現OTOP 工具

相關文章