Oracle SQL 效能調優全方法論：問題識別、優化器模式、執行計劃解讀與連接機制深度解析

一、四大問題 SQL 識別管道

1. 用戶回報：測試組/終端用戶反饋響應緩慢。
2. V$SQLAREA 掃描：聚焦高 DISK_READS、高 BUFFER_GETS、高 EXECUTIONS、高 SHARABLE_MEM 的語句。
   關鍵欄位：SQL_TEXT, COMMAND_TYPE (3=SELECT, 6=UPDATE, 7=DELETE, 47=PL/SQL), OPTIMIZER_MODE。
3. Session 監控：Enterprise Manager/Grid Control 觀察長時間運行 Session (沙漏圖示)。
4. SQL Trace + TKPROF：
   ALTER SESSION SET SQL_TRACE=TRUE; → 執行 SQL → FALSE → TKPROF 格式化。
   分析重點：PARSE 高 → 擴大 Shared Pool；QUERY/CURRENT 高 → 缺索引或連接順序不佳；DISK 高 → 索引可改善；LIBRARY CACHE MISS 多 → 擴大 Shared Pool。

二、優化器模式選擇策略

生產環境建議：實例級設 ALL_ROWS，會話/語句級用 Hint /*+ FIRST_ROWS(10) */ 覆蓋互動查詢。

三、執行計劃核心訪問路徑解讀

1. 全表掃描

觸發：無可用索引、大量資料回收 (>5-10%)、小表、Hint 強制、HWM 掃描。TABLE ACCESS FULL。

2. 索引掃描六大類型

• Unique Scan：唯一索引等值查找，返回 0/1 行。
• Range Scan：範圍條件 (<, >, BETWEEN, LIKE 'abc%')。
• Range Scan Descending：ORDER BY DESC 避免排序。
• Skip Scan：複合索引前導欄未用、基數低時跳躍掃描。
• Full Scan：掃描所有葉塊 (索引包含所有查詢欄位)。
• Fast Full Scan：多區塊 I/O 掃描索引段，無序，類似全表掃描但更窄。
• Index Join：多個單欄索引位圖/雜湊合併模擬覆蓋索引。
• Bitmap Join：星型模式維度表位圖索引連接。

3. CBO 訪問路徑選擇邏輯

1. 檢查 WHERE/FROM 子句，列舉所有可行路徑。
2. 依統計資訊計算各路徑成本 (CPU + I/O)。
3. 選擇成本最低路徑。

四、三大表連接機制深度對比

CBO 自動選擇邏輯：統計資訊準確時傾向 Hash Join (大數據量)；NL 適用首行響應優先；SMJ 為無索引大表等值連接備選。

五、EXPLAIN PLAN 實戰解讀關鍵欄位

• OPERATION / OPTIONS：如 TABLE ACCESS FULL, INDEX RANGE SCAN, HASH JOIN。
• OBJECT_NAME / OBJECT_TYPE：涉及物件。
• COST / CARDINALITY / BYTES：成本估算與基數。
• ID / PARENT_ID：樹狀結構，CONNECT BY PRIOR ID = PARENT_ID 可重建完整計劃樹。

六、AUTOTRACE 快速診斷

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SELECT * FROM emp WHERE deptno = 10;

輸出執行計劃 + 實際統計 (consistent gets, physical reads, sorts, rows processed)。