用表函式實現OTOP 工具
一直覺得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;
/
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 用匯編實現add函式函式
- 一些很實用util工具函式函式
- 用Map+函式式介面來實現策略模式函式模式
- 用雲函式快速實現圖片爬蟲函式爬蟲
- 用函式正規化實現戰略模式函式模式
- 用listagg函式分組實現列轉行函式
- 前端簡潔並實用的工具類函式封裝前端函式封裝
- 虛擬函式表-C++多型的實現原理函式C++多型
- MySQL分析函式實現MySql函式
- MySQL排名函式實現MySql函式
- fcntl函式實現dup函式
- 用函式實現模組化程式設計二函式程式設計
- 用函式實現模組化程式設計三函式程式設計
- 用函式實現模組化程式設計一函式程式設計
- 用 splice函式分別實現 push、pop、shift、unshi函式
- Zepto 原始碼分析 3 - qsa 實現與工具函式設計原始碼函式
- JavaScript的迭代函式與迭代函式的實現JavaScript函式
- PostgreSQL LIST分割槽實現:繼承表+函式+觸發器。SQL繼承函式觸發器
- 實用函式式 Java (PFJ)簡介函式Java
- 去抖函式的實現函式
- bind 函式的實現原理函式
- 巧妙地實現 debugOnly 函式Go函式
- js實現函式過載JS函式
- async 函式的實現原理函式
- Golang實現PHP常用函式GolangPHP函式
- Go實現PHP常用函式GoPHP函式
- 用函式實現模組化程式設計習題函式程式設計
- 實現call函式,手寫Function.prototype.call函式函式Function
- 使用Chrome開發者工具研究JavaScript裡函式的原生實現ChromeJavaScript函式
- 虛擬函式,虛擬函式表函式
- 科裡化函式實現以及應用場景講解函式
- Excel VBA小程式 -使 用VBA實現VLOOKUP函式查詢?Excel函式
- SQLServer用函式實現對字串按照特定字元進行拆分SQLServer函式字串字元
- Vue元件實現函式防抖Vue元件函式
- Vue實現函式防抖元件Vue函式元件
- 虛擬函式的實現原理函式
- 函式實現閏年判斷函式
- Golang之不可重入函式實現Golang函式
- PHP內建字串函式實現PHP字串函式