Oracle 效能分析 12：物件統計資訊完整解析（表、欄位、索引、直方圖）

Oracle 效能分析 12：物件統計資訊完整解析（表、欄位、索引、直方圖）

優化器依賴統計資訊產生執行計畫。Oracle 提供三類統計：表統計、欄位統計、索引統計，各自分為表/索引層級、分區層級、子分區層級。本文系統整理相關視圖、關鍵欄位含義、直方圖類型與優化器估算機制。

一、統計資訊相關視圖對照表

二、建立測試環境

-- 1. 建立測試表 (1000 筆)
CREATE TABLE test AS
SELECT ROWNUM AS id,
       ROUND(DBMS_RANDOM.NORMAL * 1000) AS val1,
       100 + ROUND(LN(ROWNUM / 3.25 + 2)) AS val2,
       100 + ROUND(LN(ROWNUM / 3.25 + 2)) AS val3,
       DBMS_RANDOM.STRING('p', 250) AS pad
FROM all_objects
WHERE ROWNUM <= 1000
ORDER BY DBMS_RANDOM.VALUE;

-- 2. 清理負值
UPDATE test SET val1 = NULL WHERE val1 < 0;

-- 3. 建立主鍵與索引
ALTER TABLE test ADD CONSTRAINT test_pk PRIMARY KEY (id);
CREATE INDEX test_val1 ON test (val1);
CREATE INDEX test_val2 ON test (val2);

-- 4. 收集統計資訊 (100% 採樣、歪斜欄位自動建直方圖)
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    OWNNAME => USER,
    TABNAME => 'TEST',
    ESTIMATE_PERCENT => 100,
    METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY',
    CASCADE => TRUE
  );
END;

三、表統計關鍵欄位

SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM user_tab_statistics WHERE table_name = 'TEST';

-- 結果範例
NUM_ROWS  BLOCKS  EMPTY_BLOCKS  AVG_SPACE  CHAIN_CNT  AVG_ROW_LEN
-------  ------  ------------  ---------  ---------  -----------
1000       39           0           0          0          265

欄位含義

1. NUM_ROWS：表資料列數
2. BLOCKS：High Water Mark (HWM) 以下的資料區塊數
3. EMPTY_BLOCKS：HWM 以上的空區塊數 (DBMS_STATS 不計算，常為 0)
4. AVG_SPACE：區塊平均剩餘空間 (DBMS_STATS 不計算，常為 0)
5. CHAIN_CNT：鏈列/遷移列總數 (DBMS_STATS 不計算，常為 0)
6. AVG_ROW_LEN：平均列長度 (Bytes)

四、欄位統計關鍵欄位

SELECT column_name, num_distinct, low_value, high_value, density,
       num_nulls, avg_col_len, histogram, num_buckets
FROM user_tab_col_statistics WHERE table_name = 'TEST';

欄位含義

1. NUM_DISTINCT：不重複值個數 (NDV)
2. LOW_VALUE / HIGH_VALUE：最小/最大值 (內部儲存格式，字串僅前 32 Bytes)
3. DENSITY：0~1 之間，越接近 0 表示過濾選擇性越高
   無直方圖：DENSITY = 1 / NUM_DISTINCT
   有直方圖：依類型不同計算
4. NUM_NULLS：NULL 值總數
5. AVG_COL_LEN：平均欄位長度 (Bytes)
6. HISTOGRAM：NONE、FREQUENCY (頻率)、HEIGHT BALANCED (等高)
7. NUM_BUCKETS：Bucket 數 (1~254)

轉換 LOW/HIGH VALUE 為可讀格式

DECLARE
  l_val test.val1%TYPE;
BEGIN
  FOR v IN (SELECT low_value, high_value FROM user_tab_col_statistics WHERE table_name = 'TEST') LOOP
    DBMS_STATS.CONVERT_RAW_VALUE(v.low_value, l_val);
    DBMS_OUTPUT.PUT_LINE('LOW  : ' || l_val);
    DBMS_STATS.CONVERT_RAW_VALUE(v.high_value, l_val);
    DBMS_OUTPUT.PUT_LINE('HIGH : ' || l_val);
  END LOOP;
END;

五、直方圖：解決資料分布不均問題

優化器需估算 WHERE col = value 回傳行數。資料均勻時用 Min/Max/NDV 即可估算；資料傾斜時需直方圖。

1. 頻率直方圖

• Bucket 數 = NDV (不重複值個數)
• ENDPOINT_VALUE = 該值本身 (數字類型；字串轉換取前 6 Bytes)
• ENDPOINT_NUMBER = 累積出現次數，相減得單一值頻率

-- 查詢 VAL2 的頻率分布
SELECT column_name, endpoint_value, endpoint_number,
       endpoint_number - LAG(endpoint_number, 1, 0) OVER (ORDER BY endpoint_number) AS frequency
FROM user_tab_histograms
WHERE table_name = 'TEST' AND column_name = 'VAL2'
ORDER BY endpoint_number;

-- 結果
COLUMN_NAME  ENDPOINT_VALUE  ENDPOINT_NUMBER  FREQUENCY
-----------  --------------  ---------------  ---------
VAL2                 101                8          8
VAL2                 102               33         25
VAL2                 103              101         68
VAL2                 104              286        185
VAL2                 105              788        502
VAL2                 106             1000        212

優化器精確估算驗證

EXPLAIN PLAN SET STATEMENT_ID = '101' FOR SELECT * FROM test WHERE val2 = 101;
EXPLAIN PLAN SET STATEMENT_ID = '102' FOR SELECT * FROM test WHERE val2 = 102;
...
SELECT statement_id, cardinality FROM plan_table WHERE id = 0;

-- 結果：CARDINALITY 精確等於 FREQUENCY
STATEMENT_ID  CARDINALITY
-----------  -----------
101                    8
102                   25
103                   68
104                  185
105                  502
106                  212

2. 等高直方圖

• NDV > 254 時使用
• Bucket 數 < NDV，同一 Bucket 可能包含多個值
• 不儲存單值頻率，ENDPOINT_NUMBER = Bucket 編號
• 值被壓縮 (Compression) 導致估算不準、不穩定

六、索引統計關鍵欄位

SELECT index_name, blevel, leaf_blocks, distinct_keys, num_rows,
       clustering_factor, avg_leaf_blocks_per_key, avg_data_blocks_per_key
FROM user_ind_statistics WHERE table_name = 'TEST';

欄位含義

1. BLEVEL：從 Root Block 到 Leaf Block 的 Branch Block 層數 (含 Root)
2. LEAF_BLOCKS：索引 Leaf Block 總數
3. DISTINCT_KEYS：不重複索引鍵值數
4. NUM_ROWS：索引項目總數
5. CLUSTERING_FACTOR：表資料與索引順序一致度 (越接近 Blocks 越好，越接近 Num_Rows 越差)
6. AVG_LEAF_BLOCKS_PER_KEY = LEAF_BLOCKS / DISTINCT_KEYS
7. AVG_DATA_BLOCKS_PER_KEY = CLUSTERING_FACTOR / DISTINCT_KEYS

七、統計收集最佳實踐

1. 採樣比例：大表用 AUTO_SAMPLE_SIZE (11g+)，小表用 100%
2. METHOD_OPT：'FOR ALL COLUMNS SIZE AUTO' 讓 Oracle 自動判斷直方圖
3. CASCADE：TRUE 同步收集索引統計
4. 排程：DBMS_STATS.GATHER_SCHEMA_STATS 配合維護窗口
5. 鎖定統計：穩定執行計畫時用 DBMS_STATS.LOCK_TABLE_STATS
6. 匯出/匯入：跨環境同步統計用 DBMS_STATS.EXPORT/IMPORT_*_STATS