Oracle 查詢優化與 DDL/DML 操作視覺指南

概述

本文為「Oracle 全攻略」系列之查詢優化、DDL/DML 操作篇，以圖解方式呈現執行計畫分析、索引策略、統計資訊管理、分區剪枝、鎖機制、大量 DML 優化等核心主題。以下為知識脈絡摘要，建議搭配原文 12 張圖示閱讀。

核心知識體系

一、查詢優化基石

• 執行計畫：EXPLAIN PLAN、DBMS_XPLAN.DISPLAY_CURSOR、AUTOTRACE 解讀操作 (Access Path、Join Method、Partition Pruning)。
• 統計資訊：DBMS_STATS.GATHER_TABLE_STATS、METHOD_OPT 直方圖、STALE_PERCENT 自動收集閾值、PUBLISH=FALSE 測試新統計。
• 最佳化器模式：ALL_ROWS (預設)、FIRST_ROWS_n、OPTIMIZER_FEATURES_ENABLE 版本回溯。
• Hint 進階：LEADING、USE_NL/HASH/MERGE、INDEX/NO_INDEX、PUSH_PRED、OPT_ESTIMATE 修正基數估算。

二、索引策略與維護

• B-Tree vs Bitmap：高基數 vs 低基數、OLTP vs DW、BITMAP JOIN INDEX 星型查詢。
• 函數索引：CREATE INDEX idx ON table (UPPER(col)) 解決函數導致索引失效。
• 部分索引 (11g+)：CREATE INDEX ... WHERE status='ACTIVE' 縮小索引體積。
• 索引監控：ALTER INDEX ... MONITORING USAGE、V$OBJECT_USAGE 識別未用索引。
• 線上重建：ALTER INDEX ... REBUILD ONLINE、COALESCE 整理碎片。

三、DDL 操作最佳實踐

• 線上表重定義 (DBMS_REDEFINITION)：無停機變更結構 (加欄位、分區、移動表空間)。
• 不可見索引 (11g+)：ALTER INDEX ... INVISIBLE 驗證移除影響再決定 DROP。
• 延遲段建立 (11g+)：DEFERRED_SEGMENT_CREATION=TRUE 空表不分配段，節省空間。
• 虛擬欄位：col GENERATED ALWAYS AS (expr) VIRTUAL 免儲存衍生值可建索引。

四、大量 DML 優化

• 直接路徑插入：INSERT /*+ APPEND */ 繞過緩衝區寫入數據塊，需 NOLOGGING 配合減少 REDO。
• 並行 DML：ALTER SESSION ENABLE PARALLEL DML + PARALLEL Hint/屬性，利用多 CPU。
• 批次處理：FORALL + BULK COLLECT 減少 Context Switch，SAVE EXCEPTIONS 處理錯誤。
• 分區交換：ALTER TABLE ... EXCHANGE PARTITION 秒級歸檔/載入歷史資料。

五、鎖與並發控制

• 鎖類型：TM (DML)、TX (交易)、DDL、Latch、Mutex、Library Cache Lock/Pin。
• 死鎖偵測：V$SESSION_WAIT、V$LOCK、DBA_BLOCKERS、DBMS_LOCK 自訂鎖。
• 避免熱塊：反向鍵索引、分區、序列 CACHE/ORDER、全域暫存表。

圖解索引

原文 12 張圖示涵蓋：

DBA 日常優化清單

1. 每週檢視 V$SQL 高負載 SQL (ELAPSED_TIME、BUFFER_GETS、EXECUTIONS)。
2. 每月執行 DBMS_STATS.GATHER_DATABASE_STATS 或啟用自動收集作業。
3. 季度審視索引使用情況，移除未用索引減少 DML 開銷。
4. 大表變更結構必走 DBMS_REDEFINITION 流程並預演回滾。