Oracle SQL 效能調優全攻略：識別問題語句、優化器模式、執行計劃分析

一、識別高耗資源 SQL 的四大方法

1. 用戶回報：測試組或終端用戶回饋反應緩慢。
2. V$SQLAREA 視圖：關鍵欄位 EXECUTIONS、DISK_READS、BUFFER_GETS、COMMAND_TYPE、OPTIMIZER_MODE、SQL_TEXT、SHARABLE_MEM。用途：找出高頻、高耗、需索引優化的 SQL。
3. Session 監控：出現沙漏圖示代表該 Session 執行時間過長。
4. SQL Trace + TKPROF：

   ALTER SESSION SET SQL_TRACE=TRUE;
   -- 執行目標 SQL
   ALTER SESSION SET SQL_TRACE=FALSE;
   -- 使用 TKPROF 分析 trace 檔
   

   分析重點：Parse 次數多→增大 Shared Pool；query/current 大→缺索引；disk 大→索引可改善；Library Cache Miss 多→增大 Shared Pool。

二、優化器模式選擇與管理

兩大優化器：RBO (Rule-Based)、CBO (Cost-Based)。OPTIMIZER_MODE 參數值：

• CHOOSE：有統計資訊用 CBO，無則用 RBO (預設)。
• ALL_ROWS：全 CBO，目標最佳吞吐量。
• FIRST_ROWS_n：全 CBO，目標前 N 列最佳回應時間 (n=1,10,100,1000)。
• FIRST_ROWS：相容模式，混合成本與啟發式。
• RULE：強制 RBO。

建議：避免 CHOOSE 造成非預期全表掃描，明確指定 ALL_ROWS 或 FIRST_ROWS_n 並定期 ANALYZE 收集統計資訊。

三、執行計劃與 AUTOTRACE 分析

EXPLAIN PLAN

需先建立 PLAN_TABLE (執行 @?/rdbms/admin/utlxplan.sql)。關鍵欄位：OPERATION、OPTIONS、OBJECT_NAME、ID、PARENT_ID。利用 CONNECT BY 可重構執行計劃樹。

存取路徑解讀

• 全表掃描：無索引、大量資料、小表、HWM、Hint 強制。
• 索引掃描：唯一、範圍、降序、跳躍、全索引、快速全索引、索引連接、位圖連接。

CBO 依 WHERE/FROM 子句列舉可用路徑，依統計資訊評估成本，選擇最低成本計劃。