Oracle SQL 效能調優系列 (一)：優化器選擇、資料存取路徑、共享游標機制

1. 選用適合的優化器

設定：OPTIMIZER_MODE (init.ora) 或 ALTER SESSION SET OPTIMIZER_MODE=... 或 Hint /*+ RULE */ / /*+ ALL_ROWS */。

建議： 直接指定 CBO (ALL_ROWS 或 FIRST_ROWS_n)，避免 CHOOSE 導致計畫不穩定。

2. 存取 Table 的兩種路徑

• Full Table Scan (FTS)： 依序讀取所有區塊，多區塊讀取 (DB_FILE_MULTIBLOCK_READ_COUNT) 優化。適合：大部份資料、無索引、小表。
• Index Access + ROWID： 索引快速定位 ROWID，再由 ROWID 存取資料區塊。適合：高選擇性 (Selectivity < 5-10%) 查詢。

ROWID 包含：Data Object#、Relative File#、Block#、Row#，唯一標識一列物理位置。

3. 共享 SQL / Library Cache 機制

運作流程

1. User 送出 SQL
2. Oracle 在 Shared Pool (Library Cache) 尋找**完全相同**的已解析游標
3. 找到 → 直接重用執行計畫 (Soft Parse)
4. 找不到 → Hard Parse (語法檢查、語意分析、優化、產生計畫)

共享條件 (三者缺一不可)

1. 字元級完全相同： 大小寫、空白、換行、註解皆須一致
2. 參考物件完全相同： 同一 Schema、同名 Table/View/Synonym、同權限路徑
3. 綁定變數名稱相同： :b1 vs :b2 視為不同

範例

-- 以下四條互不共享
SELECT * FROM EMP;
SELECT * FROM EMP ;
Select * From Emp;
SELECT * FROM EMP;  -- 尾端多一空格

-- 綁定變數名稱不同，不共享
SELECT * FROM emp WHERE empno = :b1;
SELECT * FROM emp WHERE empno = :b2;

提升共享率策略

• 應用程式**統一 SQL 範本**，使用綁定變數
• 設定 CURSOR_SHARING = FORCE/SIMILAR (11g+ 建議 FORCE，12c+ SIMILAR 棄用) 強制將常值轉為綁定變數
• 調大 SHARED_POOL_SIZE，監控 V$LIBRARYCACHE.RELOADS / INVALIDATIONS
• 避免 DDL 導致游標失效 (11g+ DDL_LOCK_TIMEOUT、12c+ EDITION 緩解)

限制

共享機制**不支援多表連接查詢**的結果快取，僅快取單表/單一 SQL 文本的解析樹與執行計畫。

1. 上一篇文章： Oracle 常見錯誤代碼分析與解決 (三)：ORA-00600、ORA-03113、ORA-00942、ORA-01688
2. 下一篇文章： Oracle 兩表交集查詢：INTERSECT 與 IN 子查詢效能比較

Oracle 資料庫相關知識

Oracle 分區表資訊查詢完全手冊：DBA/ALL/USER 視圖對照與實戰 SQL

分區表是大型資料庫管理海量資料的核心機制。本文系統整理 Or

CentOS 6.3 安裝 Oracle 10g R2 完整實戰手冊：核心參數、依賴套件、環境變數、安裝流程

在 CentOS 6.3 (x86_64) 上安裝 Orac

Oracle Trigger 觸發器完整詳解：語法、類型、四大應用場景與實戰範例

什麼是觸發器 資料庫觸發器是一個與資料表關聯的預儲 PL/S

Oracle 10g 升級實戰：最小化停機時間的成熟流程與技術方案

硬體升級相對容易，資料庫升級卻充滿變數。資深 DBA 關注的