記錄一個簡陋的根據statspack查詢哪段時間事務量最大的指令碼

生產系統上的STATSPACK是15分鐘一次的，但是一天這麼多次都發郵件出來也沒很大必要，因此每天凌晨把前一整天的報告發出來，有時候發現了TOP 5的等待事件中出現了以前沒出現過的異常，也不大好找這個等待集中發生在哪裡。

因此，先從事務量下手，寫個指令碼來統計哪個時間段內的事務量最大，當然，這應該是個系列，回頭碰見問題了再慢慢寫其他統計量最大的指令碼。

[@more@]

其實這些指令碼都可以從statspack的spreport的指令碼中稍微整理得到，記錄在次以方便以後使用：

DECLARE
--可以找到輸出的事務量最大的對應的那個statspack的endid，然後
--在找到前一個statspack的id，然後拿這兩個id之間的statspack出來
--進行深入詳細的分析
CURSOR C IS
SELECT SNAP_ID, SNAP_TIME
FROM STATS$SNAPSHOT
WHERE SNAP_TIME >= SYSDATE - 1 --要分析的時間段
AND INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM V$INSTANCE)
ORDER BY SNAP_ID;
V_TRAN NUMBER(20) := 0; --當前的statspack的id
V_TRAN_BEFORE NUMBER(20) := 0; --上一個statspack的id
VT DATE;
VT_BEFORE DATE; --上一次statspack產生的時間
V_OUT NUMBER(5, 2);
V_ID_BEFORE NUMBER(20) := 0; --記錄上一個statspack的id
BEGIN
FOR R IN C LOOP
V_TRAN_BEFORE := V_TRAN;
VT_BEFORE := VT;
--下面計算兩個statspack之間的commit和rollback的總事務量大小
SELECT SUM(VALUE)
INTO V_TRAN
FROM STATS$SYSSTAT
WHERE SNAP_ID = R.SNAP_ID
AND DBID = (SELECT DBID FROM V$DATABASE)
AND INSTANCE_NUMBER = (SELECT INSTANCE_NUMBER FROM V$INSTANCE)
AND (NAME = 'user commits' OR NAME = 'user rollbacks');
VT := R.SNAP_TIME;
--statspack間的事務量差/兩次statspack經過的時間，得到每秒的事務量大小
IF VT_BEFORE IS NOT NULL THEN
--判斷，第一個statspack的id不做計算
V_OUT := (V_TRAN - V_TRAN_BEFORE) / ((VT - VT_BEFORE) * 24 * 60 * 60);
IF V_OUT >= 4 THEN
--只輸出每秒事務量大於4的,返回statspack的endid和它和前一個statspack
--之間的事務量大小
DBMS_OUTPUT.PUT_LINE('sp : ' || V_ID_BEFORE || '-' || R.SNAP_ID || ' (' ||
V_OUT || ')');
END IF;
END IF;
V_ID_BEFORE := R.SNAP_ID;
END LOOP;
END;
/