整體流程圖
Client Process → Network → Server Process → Parse → Optimize → Execute → Fetch → Return
↑ ↓
Shared Pool (Library Cache) ←──────────────────────┘
階段一：客戶端發送
- Client Process (sqlplus/JDBC/OCI) 封裝 SQL 送至 Listener
- Listener 轉交 Dedicated/Shared Server Process
- 關鍵：Client 不執行任何邏輯，純網路傳輸
階段二：解析 - 核心六步驟
- 語法檢查：關鍵字、標點、結構正確性
- 語義檢查：物件存在、欄位正確、權限 (Dictionary Cache)
- 共享游標查找：Library Cache 中尋找完全相同 SQL (含空白、大小寫、註解)
- 命中 → Soft Parse，直接執行
- 未命中 → Hard Parse，繼續下步
- 物件鎖定：Library Cache Lock/Pin 防止 DDL 衝突
- 優化器生成執行計畫：
- RBO (Rule-based) - 已棄用
- CBO (Cost-based) - 依統計資訊估算成本
- 存入 Library Cache：SQL 文本、解析樹、執行計畫
Hard Parse vs Soft Parse
| 指標 | Hard Parse | Soft Parse |
|---|
| CPU | 高 | 極低 |
| Latch 爭用 | Library Cache Latch | 幾乎無 |
| 共享池壓力 | 大 | 小 |
⚡ 使用 Bind Variable 最大化 Soft Parse！
階段三：優化器 (CBO) 運作
- 收集統計資訊：表/索引/欄位/系統統計
- 轉換查詢：View Merging、Subquery Unnesting、Predicate Pushdown
- 生成候選計畫：Access Path (Full Scan/Index/Partition)、Join Method (NL/HJ/SM)、Join Order
- 成本模型評估：I/O + CPU + Network 成本
- 選擇最低成本計畫
階段四：執行
- 依計畫逐步操作：Table Access、Index Scan、Join、Sort、Aggregation
- PGA 分配排序/雜湊區
- Buffer Cache 讀取/修改區塊
- Redo Log Buffer 記錄變更
階段五：提取
- Array Fetch (預設 15 行/批次，可調
ARRAYSIZE)
- 網路傳輸回 Client
實戰優化檢查清單
| 階段 | 常見問題 | 解決方案 |
|---|
| 解析 | Hard Parse 暴增 | Bind Variable、CURSOR_SHARING、增加 Shared Pool |
| 優化 | 計畫錯誤 (Full Scan 應走 Index) | 收集統計、Histograms、Hint、SQL Profile、Baseline |
| 執行 | PGA 不足導致 Temp 磁碟排序 | 調大 PGA_AGGREGATE_TARGET、優化 SQL 邏輯 |
| 提取 | 網路往返過多 | 增加 ARRAYSIZE、減少選取欄位、壓縮傳輸 |
診斷工具
EXPLAIN PLAN / DBMS_XPLAN.DISPLAY_CURSORV$SQL, V$SQL_PLAN, V$SQLSTATS- 10046 Trace (Extended SQL Trace) + TKPROF
- SQL Monitoring (11g+)
DBMS_SQLTUNE.REPORT_SQL_MONITOR
- AWR/ASH 報告
