前言
一條 SELECT 語句在 Oracle 內部經歷什麼？理解 **解析、優化、執行、提取** 四大階段，能幫助 DBA 定位效能瓶頸、開發人員撰寫高效 SQL、架構師設計快取策略。本文以簡單查詢為例逐層剖析。
階段一：Client 發送 → Server Process 接收
- Client (JDBC/OCI/ODP.NET/SQL*Plus) 建立 TCP 連線 → Server 端啟動 **專用伺服器進程** (Dedicated Server) 或從 **共用伺服器池** 取得 Dispatcher/Shared Server。
- **關鍵點**：Client 端**不執行任何 SQL 邏輯**，僅負責封裝請求、傳輸協定 (TTC)、接收結果集。所有運算在 Server 進程完成。
- Client Process 與 Server Process **一對一對應** (Dedicated) 或 **多對一** (Shared Server)，Server Process 可視為 Client 的「影子進程」。
階段二：SQL 解析 — 最耗 CPU、最易爭用
Server Process 對 SQL 文本執行：
- 語法檢查：關鍵字拼寫、子句順序、標點符號。錯誤回傳
ORA-009xx。
- 語義檢查：Data Dictionary 查核表名、欄位名、權限、同義詞解析。錯誤回傳
ORA-00904 (欄位不存在) 、ORA-01031 (權限不足)。
- 共用游標查找 (Library Cache)：計算 SQL 文本 Hash 值 → 尋找相同文本 (含大小寫、空白、字面值) 的 Parent Cursor → 找到則**軟解析** (Soft Parse)，直接重用執行計畫；找不到則**硬解析** (Hard Parse)。
- 硬解析步驟：
- 物件解析鎖 (Row Cache Lock) 保護定義不被 DDL 變更。
- 優化器 (CBO/RBO) 依統計資訊、Hint、參數產生多個執行計畫 → 選擇最低 Cost。
- 生成二進制執行代碼 (Pro*C 風格指令集) 存入 Library Cache。
效能啟示：使用**綁定變數** 最大化游標共用、減少硬解析；CURSOR_SHARING=FORCE/SIMILAR 可強制字面值轉綁定 (副作用需評估)。
階段三：SQL 執行 — 資料存取與運算
- 依執行計畫逐步操作：Table Access Full / Index Range Scan / Nested Loops / Hash Join / Sort / Aggregation。
- Buffer Cache 邏輯讀**：優先從 SGA Buffer Cache 讀取資料塊 (Consistent Get / Current Get)；缺失則發起**物理讀** (Physical Read) 從磁碟載入。
- PGA 工作區**：排序、雜湊連接、位圖合併在 PGA 完成；記憶體不足溢寫至 Temporary Tablespace (Direct Path Write/Read)。
- 一致性讀取 (Consistent Read)**：查詢見到 SCN 為查詢開始時刻的資料快照，透過 Undo Segment 重構舊版本區塊 (CR Block)。
階段四：資料提取與網路傳輸
- Server Process 將結果集分批封裝成 **SDU (Session Data Unit, 預設 8KB)** 封包，經由 **Oracle Net Services** (TNS/TCP) 傳送給 Client。
- Client 驅動程式接收 → 解封裝 → 轉換為語言原生資料結構 (
ResultSet, DataReader, cx_Oracle Cursor 等)。
- Array Fetch Size**：Client 端設定
FetchSize (JDBC 預設 10、ODP.NET 100) 決定每次網路往返回傳行數，**適當調大 (500~1000) 可大幅減少 Round-trip**，提升大量查詢吞吐。
兩類快取的常見誤區
| 快取類型 | 位置 | 內容 | 一致性 | 常見誤解 |
|---|
| Library Cache / Buffer Cache | Server SGA | 執行計畫、資料塊 | 強一致性 (SCN、鎖、Undo) | 以為 Client 也有此快取 |
| 應用層快取 | Client 記憶體 / Redis / Memcached | 查詢結果物件 | **最終一致性**，需手動失效/TTL | 以為資料庫異動會自動更新 |
總結：開發與調優檢核清單
- **綁定變數** → 減少硬解析、Library Cache 爭用。
- **統計資訊最新** → 優化器決策準確。
- **適當索引** → 減少 Full Table Scan、物理讀、PGA 壓力。
- **FetchSize 調大** → 減少網路 Round-trip。
- **區分 Server/Client 快取** → 避免髒讀、邏輯錯誤。
- **權限檢查在語義階段** → 應用層授權可提前攔截、減輕資料庫負擔。
