Oracle 資料庫效能優化核心：SQL 執行效率決定 70-90% 資源消耗

核心觀點

應用系統 70%-90% 資料庫資源消耗在 SQL 執行上。查詢優化非純 DBMS 內建功能，開發人員撰寫的 SQL 直接決定優化器能否產生好計劃。優秀計劃可提升數十倍效能，且優化成本遠低於程式碼重構。

四大優化主軸

1. 高效索引建立策略

• 常用連接欄（非外鍵）建索引
• 常用 ORDER BY/GROUP BY 欄建索引
• 條件欄位區分度高（唯一值多）建索引；區分度低（如性別、狀態碼）勿建
• 多欄常一起排序/分組 → 覆合索引
• 索引放在與使用者表空間不同磁碟的索引表空間，降低 I/O 爭用
• 索引類型選擇：分區、完整、唯一、位圖（低基數欄）
• 建立前測量選擇性：COUNT(DISTINCT col) / COUNT(*)

2. 叢集索引適用場景

大量重複值 + 範圍查詢（BETWEEN, >, <, >=, <=）+ ORDER BY/GROUP BY → 考慮叢集索引（IOT 索引組織表）。

3. 覆合索引設計原則

常同時存取多欄、各欄有重複值 → 覆合索引。前導欄必須為最高頻查詢欄，形成索引覆蓋避免回表。

4. 運算式優化與優化器工具

• 能用範圍查詢（BETWEEN, >, <）不用 LIKE，萬用字元匹配極耗時
• 善用 Oracle Optimizer（EXPLAIN PLAN、AUTOTRACE、SQL Tuning Advisor）
• 行鎖管理器減少鎖爭用，提高併發

開發者行動清單

1. 每支上線 SQL 必跑 EXPLAIN PLAN 檢視存取路徑
2. 統計資訊定期收集（DBMS_STATS），避免優化器「盲人摸象」
3. 綁定變數提高計劃重用，但警惕直方圖欄位的窺探問題
4. 避免隱含型別轉換（WHERE salary > 60000 對 money 欄位）
5. 大量資料操作用批次（FORALL、BULK COLLECT）減少上下文切換