SQL 調優始於理解優化器決策邏輯。本講座涵蓋優化器模式選擇、表存取路徑 (全掃描 vs ROWID)、共享游標機制與綁定變數最佳實踐。
| 模式 | 參數值 | 特性 | 適用場景 |
|---|---|---|---|
| RULE (RBO) | OPTIMIZER_MODE=RULE | 啟發式規則,不看統計資訊 | 極舊版相容、特定封裝應用 |
| COST (CBO) | OPTIMIZER_MODE=ALL_ROWS | 基於統計資訊計算成本,目標總吞吐量 | **生產環境標準**,批次、報表、OLAP |
| CHOOSE | OPTIMIZER_MODE=CHOOSE | 有統計用 CBO,無統計用 RBO | **不推薦**;統計缺失時退化為 RBO 產生劣質計畫 |
| FIRST_ROWS_n | OPTIMIZER_MODE=FIRST_ROWS_10 | 目標最快回傳前 n 列 | 互動式查詢、分頁、Web 應用 |
建議:生產環境實例級設 ALL_ROWS,Session/Statement 級用 Hint (/*+ FIRST_ROWS(10) */) 覆蓋互動查詢。
DB_FILE_MULTIBLOCK_READ_COUNT) 循序掃描所有區塊。適合:小表、大比例存取 (>5-10%)、無索引、並行查詢。**關鍵**:CBO 依統計資訊估算基數選擇路徑,**統計資訊準確性決定計畫好壞**。
Oracle 在 Shared Pool (Library Cache) 快取已解析 SQL 文本與執行計畫。**嚴格字元級匹配** (含空白、換行、大小寫) 才能共享。
-- 錯誤:字面值不同 → 硬解析 SELECT * FROM emp WHERE empno = 7369; SELECT * FROM emp WHERE empno = 7499; -- 正確:綁定變數 → 軟解析 SELECT * FROM emp WHERE empno = :b1; -- Java: ps.setInt(1, 7369); ps.executeQuery();
EXACT (預設):嚴格匹配。FORCE:強制將字面值替換為系統產生綁定變數。**副作用**:可能產生非最優計畫、直方圖失效、ORA-07445。僅作過渡方案。SIMILAR (11g 棄用):類似 FORCE 但保留直方圖資訊。OPTIMIZER_MODE=ALL_ROWS,CURSOR_SHARING=EXACT。DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')。EXECUTE IMMEDIATE ... USING)。V$SQLAREA.VERSION_COUNT > 100 者為「游標爆炸」,排查字面值、優化器環境差異。SQL Plan Baseline 固定優質計畫。優化器模式正確、統計資訊準確、綁定變數普及,是 SQL 效能穩定的三大基石。