Oracle Freelist 與 HWM 深度解析：原理、診斷與性能優化實戰

四、HWM 對全表掃描的性能影響

全表掃描讀取 所有 HWM 以下塊，含空塊。大量 DELETE 後 HWM 不下移，導致：

• 多餘 I/O：掃描大量空塊
• 緩存污染：空塊佔用 Buffer Cache
• 並行查詢傾斜：Granule 劃分不均

空間浪費率公式：p = 1 - (有數據行的塊數 / HWM下總塊數)

-- Oracle 8/9/10g+ 獲取有數據行的塊數
SELECT COUNT(DISTINCT SUBSTR(ROWID,7,3)||SUBSTR(ROWID,10,6)) FROM schema.table;

五、診斷工具與腳本

1. DBMS_SPACE 存儲過程

CREATE OR REPLACE PROCEDURE show_space(
  p_segname IN VARCHAR2, p_owner IN VARCHAR2 DEFAULT USER,
  p_type IN VARCHAR2 DEFAULT 'TABLE', p_partition IN VARCHAR2 DEFAULT NULL) AS
  l_free_blks NUMBER; l_total_blocks NUMBER; l_total_bytes NUMBER;
  l_unused_blocks NUMBER; l_unused_bytes NUMBER;
  l_last_used_ext_fid NUMBER; l_last_used_ext_bid NUMBER; l_last_used_block NUMBER;
  PROCEDURE p(lbl VARCHAR2, num NUMBER) IS BEGIN
    DBMS_OUTPUT.PUT_LINE(RPAD(lbl,40,'.')||num);
  END;
BEGIN
  DBMS_SPACE.FREE_BLOCKS(segment_owner=>p_owner, segment_name=>p_segname,
    segment_type=>p_type, partition_name=>p_partition, freelist_group_id=>0,
    free_blks=>l_free_blks);
  DBMS_SPACE.UNUSED_SPACE(segment_owner=>p_owner, segment_name=>p_segname,
    segment_type=>p_type, partition_name=>p_partition,
    total_blocks=>l_total_blocks, total_bytes=>l_total_bytes,
    unused_blocks=>l_unused_blocks, unused_bytes=>l_unused_bytes,
    last_used_extent_file_id=>l_last_used_ext_fid,
    last_used_extent_block_id=>l_last_used_ext_bid,
    last_used_block=>l_last_used_block);
  p('Free Blocks',l_free_blks); p('Total Blocks',l_total_blocks);
  p('Total Bytes',l_total_bytes); p('Unused Blocks',l_unused_blocks);
  p('Unused Bytes',l_unused_bytes); p('Last Used Ext FileId',l_last_used_ext_fid);
  p('Last Used Ext BlockId',l_last_used_ext_bid); p('Last Used Block',l_last_used_block);
END;
/
EXEC show_space('EMP','SCOTT');

2. Block Dump 診斷

-- Segment Header Dump
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 100;
-- Data Block Dump
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 101;

關注字段：flg: O (On Freelist)、fnx (下一塊 DBA)、itc (ITL 槽位數)。

六、優化實戰策略

1. 空間回收：HWM 以上未用空間

ALTER TABLE t1 DEALLOCATE UNUSED;           -- 全部回收
ALTER TABLE t1 DEALLOCATE UNUSED KEEP 10M;  -- 保留 10M

2. 表縮減：HWM 以下空洞

• TRUNCATE：DDL，即時重置 HWM，可選 REUSE STORAGE 保留區段
• ALTER TABLE ... MOVE：重建表，需重建索引、授權、觸發器
• DBMS_REDEFINITION：在線重定義，零停機，企業版功能
• 行移動 + SHRINK SPACE：ENABLE ROW MOVEMENT; SHRINK SPACE COMPACT; SHRINK SPACE; (ASSM 表空間專用)

3. Freelist 競爭優化

1. 確認競爭：SELECT * FROM V$WAITSTAT WHERE CLASS='data block'；V$SESSION_WAIT P3=0/1014 為讀競爭，其他為修改競爭
2. 定位段：SELECT * FROM DBA_EXTENTS WHERE FILE_ID=:p1 AND :p2 BETWEEN BLOCK_ID AND BLOCK_ID+BLOCKS-1
3. 計算 Freelists 數：Dump HWM 附近塊，統計 ITL 槽位峰值 +1
4. 重建表：CREATE TABLE new_t ... STORAGE (FREELISTS 16 FREELIST GROUPS 4) (RAC 環境需多組)

4. ASSM 遷移建議

10g+ 新建表空間強制 ASSM (EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO)。存量對象可通過 DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL 遷移，或重建表至 ASSM 表空間。ASSM 以位圖替代 Freelist，自適應管理，消除手工調優負擔。