萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle SQL 效能優化系列講座 (一):優化器選擇、表存取路徑、共享游標與綁定變數

SQL 調優始於理解優化器決策邏輯。本講座涵蓋優化器模式選擇、表存取路徑 (全掃描 vs ROWID)、共享游標機制與綁定變數最佳實踐。

1. 優化器模式選擇

模式參數值特性適用場景
RULE (RBO)OPTIMIZER_MODE=RULE啟發式規則,不看統計資訊極舊版相容、特定封裝應用
COST (CBO)OPTIMIZER_MODE=ALL_ROWS基於統計資訊計算成本,目標總吞吐量**生產環境標準**,批次、報表、OLAP
CHOOSEOPTIMIZER_MODE=CHOOSE有統計用 CBO,無統計用 RBO**不推薦**;統計缺失時退化為 RBO 產生劣質計畫
FIRST_ROWS_nOPTIMIZER_MODE=FIRST_ROWS_10目標最快回傳前 n 列互動式查詢、分頁、Web 應用

建議:生產環境實例級設 ALL_ROWS,Session/Statement 級用 Hint (/*+ FIRST_ROWS(10) */) 覆蓋互動查詢。

2. 表存取路徑

  • Full Table Scan (FTS):多塊讀 (DB_FILE_MULTIBLOCK_READ_COUNT) 循序掃描所有區塊。適合:小表、大比例存取 (>5-10%)、無索引、並行查詢。
  • Index Access → ROWID → Table Access:索引範圍掃描取得 ROWID,再單塊讀取表資料。適合:高選擇性 (<1-5%)、OLTP 精準查詢。

**關鍵**:CBO 依統計資訊估算基數選擇路徑,**統計資訊準確性決定計畫好壞**。

3. 共享游標與綁定變數

Oracle 在 Shared Pool (Library Cache) 快取已解析 SQL 文本與執行計畫。**嚴格字元級匹配** (含空白、換行、大小寫) 才能共享。

硬解析 vs 軟解析

  • 硬解析:SQL 不在 Library Cache → 語法/語義檢查、優化、生成計畫 → 高 CPU、Latch 爭用。
  • 軟解析: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();

CURSOR_SHARING 參數 (救急非根治)

  • EXACT (預設):嚴格匹配。
  • FORCE:強制將字面值替換為系統產生綁定變數。**副作用**:可能產生非最優計畫、直方圖失效、ORA-07445。僅作過渡方案。
  • SIMILAR (11g 棄用):類似 FORCE 但保留直方圖資訊。

實戰檢查清單

  1. 實例參數 OPTIMIZER_MODE=ALL_ROWS,CURSOR_SHARING=EXACT。
  2. 核心表定期 DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')。
  3. 所有應用 SQL **強制使用綁定變數** (PreparedStatement、EXECUTE IMMEDIATE ... USING)。
  4. 監控 V$SQLAREA.VERSION_COUNT > 100 者為「游標爆炸」,排查字面值、優化器環境差異。
  5. 關鍵 SQL 加入 SQL Plan Baseline 固定優質計畫。

優化器模式正確、統計資訊準確、綁定變數普及,是 SQL 效能穩定的三大基石。

copyright © 萬盛學電腦網 all rights reserved