Oracle SQL 性能優化 53 條鐵律：從優化器選擇到共享游標深度解析

1. 選對優化器：拒絕 CHOOSE

• RULE：基於規則，穩定可預測。
• COST (CBO)：基於成本，需定期 ANALYZE 收集統計信息。
• CHOOSE：若表已分析用 CBO，否則用 RULE，**不可控，易導致全表掃描**。
• 建議：會話級或語句級顯式指定 /*+ RULE */ 或 /*+ FIRST_ROWS */，避免默認 CHOOSE。

2. 表訪問雙路徑

• 全表掃描 (FTS)：多塊讀優化，適用小表/大比例訪問。
• ROWID 訪問：索引 → ROWID → 數據塊，適用高選擇性查詢。

3. 共享 SQL (Shared Pool) 機制與三大匹配條件

SQL 解析後駐留於 SGA Shared Pool，再次執行同語句可復用計劃，節省 CPU/內存。

嚴格字符級匹配 (含空格/換行/大小寫)：

SELECT * FROM EMP;
SELECT * from EMP;  -- 不同
Select * From Emp;  -- 不同

對象解析完全一致：同義詞/所有者/權限路徑須一致。

綁定變量類型/長度一致：避免隱式轉換導致重新解析。

4. 綁定變量 vs 字面值

• OLTP 高併發：強制綁定變量，利用共享游標，防止 Shared Pool 碎片化。
• DSS/報表：字面值 讓 CBO 生成最優計劃 (直方圖生效)。
• 11g+ CURSOR_SHARING=FORCE/SIMILAR 謹慎使用，可能引發計劃不穩定。

5. 索引使用鐵律

• WHERE 列勿加函數/運算 (WHERE TRUNC(dt)=... → 失效)。
• 複合索引必須包含**導讀列 (Leading Column)**。
• IS NULL 不走普通索引 (位圖索引除外)。
• LIKE '%abc%' 導讀列以通配符開頭失效。
• 隱式類型轉換 (WHERE num_col='123') 導致索引失效。

6. 連接優化

• 小表驅動大表 (NL Join)，大表關聯大表用 Hash Join。
• 確保連接列有索引、統計信息準確、數據類型一致。
• 避免笛卡爾積，檢查缺少連接條件。

7. 子查詢轉換

• IN / EXISTS 互轉，視外表/內表大小決定。
• NOT IN 含 NULL 陷阱，改用 NOT EXISTS。
• 標量子查詢下推、去相關化。

8. 分頁與 Top-N

• 12c+ OFFSET ... FETCH FIRST n ROWS ONLY。
• 舊版 ROWNUM 雙層嵌套，外層過濾 RN BETWEEN :start AND :end。
• 避免 ROWNUM 直接配合 ORDER BY 邏輯錯誤。

9. 統計信息管理

• DBMS_STATS.GATHER_TABLE_STATS 替代 ANALYZE。
• 直方圖針對傾斜列，METHOD_OPT=>'FOR COLUMNS SIZE AUTO col'。
• 鎖定統計信息 (LOCK_TABLE_STATS) 防止夜間自動收集導致計劃突變。

10. 監控與診斷工具箱

• SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
• DBMS_XPLAN.DISPLAY_CURSOR 看實際執行計劃 (含 E-Rows/A-Rows)。
• V$SQL_PLAN、V$SQLSTATS、SQL Monitoring (11g+)。
• SQL Trace (10046) + TKPROF 深度分析。

總結

性能優化是系統工程：**規範開發 → 統計維護 → 計劃穩定 → 持續監控**。53 條規則非孤立技巧，需融入團隊 Code Review 與發布流程，方能長效受益。