Oracle 效能調校核心要點：存取路徑、高水位線、統計資訊與區塊大小策略

Oracle 效能調校是系統生命週期的核心課題。本文聚焦四大支柱：資料存取方法選擇、High Water Mark (HWM) 管理、表統計資訊收集與區塊大小規劃，提供可落地的診斷指令與優化策略。

一、 資料存取方法選擇

根據業務存取模式選擇最適結構：

• Cluster：常一起查詢的父子表（如訂單/明細）
• B-tree 索引：高選擇性欄位、OLTP 單筆查詢
• Bitmap 索引：低基數欄位、DSS 多維分析、AND/OR 複合條件
• Function-based 索引：WHERE 條件含函數運算（如 UPPER(name)）
• Index-Organized Table (IOT)：主鍵存取佔 90% 以上、資料量較小的查找表
• Materialized View：複雜彙總查詢、資料倉庫預計算

索引維護紅線：當索引中已刪除列超過 15%，應安排 ALTER INDEX ... REBUILD 或 COALESCE。

二、 應用層面的 SQL 優化原則

• 優先使用宣告式約束（PK/FK/Check）替代程式碼檢核
• 使用繫結變數（Bind Variable）促進游標共用
• 依據資料分布調整 CURSOR_SHARING（EXACT/SIMILAR/FORCE）

三、 避免動態擴展的空間管理策略

1. 採用本地管理表空間（LMT），啟用自動區塊分配
2. 依預估資料量合理設定 INITIAL/NEXT/PCTINCREASE
3. 監控即將擴展的 Segment：

   SELECT owner, table_name, blocks, empty_blocks
   FROM   dba_tables
   WHERE  empty_blocks / (blocks + empty_blocks) < 0.1;

四、 High Water Mark (HWM) 深度解析

• 定義：Segment Header 中記錄，標示已使用區塊的邊界，以 5 區塊為單位遞增
• TRUNCATE 會重設 HWM；DELETE 不會
• Full Table Scan 會讀取 HWM 以下所有區塊，HWM 以上空間浪費儲存但不影響效能
• 回收 HWM 以上空間：ALTER TABLE t DEALLOCATE UNUSED;
• 壓縮 HWM 以下空間：ALTER TABLE t MOVE;（需重建索引）或 Exp/Imp

五、 表統計資訊收集與解讀

ANALYZE TABLE ndls.t_wh_shipping_bill COMPUTE STATISTICS;

SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM   dba_tables
WHERE  owner = 'NDLS' AND table_name = 'T_WH_SHIPPING_BILL';

關鍵指標：

• CHAIN_CNT 高 → 需調大 PCTFREE 或遷移至較大區塊表空間
• AVG_SPACE 過大 → 考慮 MOVE 壓縮

六、 區塊大小選型策略

鐵律：區塊大小必須為 OS 區塊整數倍，且在資料庫建立時一次決定，後續不可輕易變更。

七、 綜合優化檢查清單

1. 確認高頻 SQL 執行計畫是否使用最佳存取路徑
2. 定期收集統計資訊（建議 DBMS_STATS 取代 ANALYZE）
3. 監控 HWM 與鏈式列，安排定期 MOVE/REBUILD
4. 依工作負載類型選擇區塊大小，新建表空間時規劃

效能調校無終點，唯有建立「監控→診斷→優化→驗證」的閉環機制，才能持續保障系統穩定高效運行。

1. 上一篇文章： Oracle 11g 函數呼叫五大語法詳解：位置、命名、混合與 SQL 實戰
2. 下一篇文章： Oracle 常用 DDL/DML 指令與核心資料類型完整速查表

Oracle 資料庫基礎

Oracle 資料導入實戰：SQL Developer 完勝 TOAD 的 Excel 匯入體驗

背景 公司上線 ERP，需將大量基礎資料從 Excel 匯入

基於 JDBC 的資料庫連線池技術深度解析：原理、關鍵技術與高效實現策略

摘要 本文系統闡述 JDBC 連線池工作原理、事務處理、多資

Oracle ALTER TABLE 完整操作指南：重命名、增刪欄位、修改型別、約束管理與最佳實務

基本語法結構 ALTER TABLE table_name

DB2 與 Oracle 異構資料庫遠端複寫實戰：聯合資料庫與 SQL 複寫架構詳解

背景與動機 DB2 聯合資料庫 (Federated Dat