Oracle DBA 實戰腳本庫：環境設定、Runstats 效能比較、Mystat 統計分析

前言

源自 Thomas Kyte《Expert Oracle Database Architecture》實戰精華。建立標準化 SQL*Plus 環境、Runstats 套件比較兩種寫法效能、Mystat 腳本統計單一操作資源消耗，是 DBA 日常調優的三把利刃。

一、標準化 Login.sql 環境建置

置於 $ORACLE_HOME/sqlplus/admin/login.sql 或使用者家目錄：

-- 編輯器與輸出設定
DEFINE _editor = vi
SET SERVEROUTPUT ON SIZE 1000000
SET TRIMSPOOL ON
SET LONG 5000
SET LINESIZE 100
SET PAGESIZE 9999
COLUMN plan_plus_exp FORMAT A80

-- 動態提示符：username@dbname>
COLUMN global_name NEW_VALUE gname
SET TERMOUT OFF
DEFINE gname = idle
SELECT LOWER(USER) || '@' || 
       SUBSTR(global_name, 1, DECODE(INSTR(global_name, '.'), 0, LENGTH(global_name), INSTR(global_name, '.')-1)) AS global_name
FROM (SELECT global_name, INSTR(global_name, '.') AS dot FROM global_name);
SET SQLPROMPT '&gname> '
SET TERMOUT ON
SET TRIMSPOOL ON

效果： SCOTT@PRODDB> 即時顯示連線身分，防誤操作。

二、Runstats 套件：科學比較兩種寫法效能

核心統計三大維度

1. 時間：百秒 (hsecs)，手工執行誤差大，參考價值較低
2. 記憶體：Session PGA/UGA 分配變化
3. Latch：共享記憶體保護機制獲取次數，**最關鍵指標**

完整套件建立腳本

-- 1. 統計視圖
CREATE OR REPLACE VIEW stats AS
SELECT 'STAT...' || a.name AS name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
UNION ALL
SELECT 'LATCH.' || name, gets FROM v$latch;

-- 2. 全域暫存表（Commit 保留）
CREATE GLOBAL TEMPORARY TABLE run_stats (
  runid VARCHAR2(15),
  name  VARCHAR2(80),
  value NUMBER
) ON COMMIT PRESERVE ROWS;

-- 3. 套件規格
CREATE OR REPLACE PACKAGE runstats_pkg AS
  PROCEDURE rs_start;                              -- 執行前
  PROCEDURE rs_middle;                             -- 第一種寫法後
  PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0); -- 第二種寫法後
END;
/

-- 4. 套件主體
CREATE OR REPLACE PACKAGE BODY runstats_pkg AS
  g_start NUMBER; g_run1 NUMBER; g_run2 NUMBER;

  PROCEDURE rs_start IS
  BEGIN
    DELETE FROM run_stats;
    INSERT INTO run_stats SELECT 'before', name, value FROM stats;
    g_start := DBMS_UTILITY.GET_TIME;
  END;

  PROCEDURE rs_middle IS
  BEGIN
    g_run1 := DBMS_UTILITY.GET_TIME - g_start;
    INSERT INTO run_stats SELECT 'after 1', name, value FROM stats;
    g_start := DBMS_UTILITY.GET_TIME;
  END;

  PROCEDURE rs_stop(p_difference_threshold IN NUMBER DEFAULT 0) IS
  BEGIN
    g_run2 := DBMS_UTILITY.GET_TIME - g_start;
    DBMS_OUTPUT.PUT_LINE('Run1 ran in ' || g_run1 || ' hsecs');
    DBMS_OUTPUT.PUT_LINE('Run2 ran in ' || g_run2 || ' hsecs');
    DBMS_OUTPUT.PUT_LINE('Run1 ran in ' || ROUND(g_run1/g_run2*100,2) || '% of the time');

    -- 統計差異報表
    DBMS_OUTPUT.PUT_LINE(RPAD('Name',30) || LPAD('Run1',10) || LPAD('Run2',10) || LPAD('Diff',10));
    FOR x IN (
      SELECT RPAD(a.name,30) || TO_CHAR(b.value-a.value,'9,999,999') || 
             TO_CHAR(c.value-b.value,'9,999,999') || 
             TO_CHAR((c.value-b.value)-(b.value-a.value),'9,999,999') AS data
      FROM run_stats a, run_stats b, run_stats c
      WHERE a.name = b.name AND b.name = c.name
        AND a.runid = 'before' AND b.runid = 'after 1' AND c.runid = 'after 2'
        AND (c.value - a.value) > 0
        AND ABS((c.value-b.value)-(b.value-a.value)) > p_difference_threshold
      ORDER BY ABS((c.value-b.value)-(b.value-a.value))
    ) LOOP
      DBMS_OUTPUT.PUT_LINE(x.data);
    END LOOP;

    -- Latch 總計比較
    DBMS_OUTPUT.PUT_LINE('Run1 latches total versus Run2 -- difference and pct');
    FOR x IN ( ... ) LOOP  -- 略同統計邏輯，過濾 LATCH%
      DBMS_OUTPUT.PUT_LINE(x.data);
    END LOOP;
  END;
END;
/

實戰測試範例：批次 INSERT vs 逐筆 INSERT

SET SERVEROUTPUT ON
EXEC runstats_pkg.rs_start;

-- 寫法 1：批次 INSERT (單一 SQL)
CREATE TABLE wwm AS SELECT * FROM all_objects WHERE 1=2;
INSERT INTO wwm SELECT * FROM all_objects;  -- 34,750 rows

EXEC runstats_pkg.rs_middle;

-- 寫法 2：逐筆 INSERT (PL/SQL 迴圈)
CREATE TABLE wwm2 AS SELECT * FROM all_objects WHERE 1=2;
BEGIN
  FOR x IN (SELECT * FROM all_objects) LOOP
    INSERT INTO wwm2 VALUES x;
  END LOOP;
  COMMIT;
END;
/

EXEC runstats_pkg.rs_stop(1000000);  -- 只顯示差異 > 1M 的統計

輸出解讀重點

Run1 ran in 3558 hsecs
Run2 ran in 14263 hsecs
Run1 ran in 24.95% of the time

Name                              Run1        Run2        Diff
STAT...session pga memory              0     4,040,516   4,040,516
STAT...redo size               3,920,896    12,212,584   8,291,688
...
Run1 latches total versus Run2 -- difference and pct
Run1        Run2        Diff       Pct
305,006   1,462,079   1,157,073   20.86%

結論： 批次 INSERT (Run1) 僅花費 25% 時間、產生 1/3 Redo、Latch 獲取僅 21%。集合操作遠優於逐筆迴圈。

三、Mystat 腳本：單一操作資源消耗精準統計

Mystat.sql — 捕捉基準值

SET ECHO OFF VERIFY OFF
COLUMN value NEW_VALUE V
DEFINE S="&1"
SET AUTOTRACE OFF
SELECT a.name, b.value
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
  AND LOWER(a.name) LIKE '%' || LOWER('&S') || '%';
/

用法：@mystat "redo size" → 記錄初始 redo size 值

Mystat2.sql — 報告差異

SET ECHO OFF VERIFY OFF
SELECT a.name, b.value AS V, 
       TO_CHAR(b.value - &V, '999,999,999,999') AS DIFF
FROM v$statname a, v$mystat b
WHERE a.statistic# = b.statistic#
  AND LOWER(a.name) LIKE '%' || LOWER('&S') || '%';

用法：執行目標操作 (如 UPDATE wwm SET object_name = LOWER(object_name)) 後，@mystat2 "redo size"

實戰輸出

NAME                          V           DIFF
redo size              26,028,120     9,619,660

精確量化：該 UPDATE 產生 9.6 MB Redo。

四、開發人員自助調優指南

1. 在本地資料庫部署 Runstats/Mystat
2. 對同一邏輯寫兩種 SQL/PLSQL 版本
3. 執行 Runstats 比較 Latch/Redo/記憶體
4. 用 Mystat 量化關鍵指標 (Redo, Consistent Gets, Physical Reads, Parse Count)
5. 選擇資源消耗較低版本上線

五、進階擴展建議

• 加入 V$SESS_TIME_MODEL 統計 DB Time/CPU Time
• 整合 DBMS_MONITOR.SESSION_TRACE_ENABLE 產生 10046 Trace
• 封裝為 Python/Shell 自動化腳本，納入 CI/CD Pipeline
• 建立基準庫：記錄核心業務操作的「標準資源消耗畫像」

結語

Runstats 與 Mystat 將「感覺慢」轉化為「Latch 多 4.8 倍、Redo 多 3.1 倍」的量化證據。建立標準化環境、養成科學比較習慣、量化而非質疑，是從初級 DBA 邁向專家的必經之路。