Oracle SQL 效能調優實戰指南 (上)：優化器、存取路徑與 SQL 寫法核心原則

1. 選擇合適的優化器模式

設定層級：init.ora (OPTIMIZER_MODE) → ALTER SESSION → Hint (/*+ RULE/CHOOSE/FIRST_ROWS */)。
建議：生產環境明確設定 OPTIMIZER_MODE = ALL_ROWS (OLAP) 或 FIRST_ROWS_n (OLTP)，並建立統計收集排程。

2. 表存取路徑：全表掃描 vs ROWID 存取

• Full Table Scan (FTS)：依序讀取所有區塊，多區塊讀取 (DB_FILE_MULTIBLOCK_READ_COUNT) 優化。適合：小表、大比例存取、無合適索引。
• ROWID / Index Access：索引 → ROWID → 表區塊。適合：高選擇性條件 (<1-5%)、主鍵/唯一鍵查詢。

關鍵：索引連結「邏輯鍵值」與「物理 ROWID」，使點查詢極快。

3. 共享 SQL (Shared Pool / Library Cache) 機制與條件

相同 SQL 文本 (含空白、換行、大小寫) → 共享 Parsed Tree + Execution Plan，避免 Hard Parse。

三大共享條件 (必須同時滿足)

1. 字元級完全相同：

   SELECT * FROM EMP;        -- A
   SELECT * from EMP;        -- B (不同: 大小寫)
   Select * From Emp;        -- C (不同)
   SELECT * FROM EMP ;       -- D (不同: 結尾空白)

2. 參考物件完全相同：同義詞/擁有者解析結果一致。

   User JACK:  SELECT * FROM sal_limit;  -- 私有同義詞 → JACK.SAL_LIMIT
   User JILL:  SELECT * FROM sal_limit;  -- 公開同義詞 → PUBLIC.SAL_LIMIT
   -- 物件不同，無法共享

3. 綁定變數名稱完全相同：

   WHERE pin = :blk1.pin     -- 共享
   WHERE pin = :blk1.ot_ind  -- 不共享 (變數名不同)

調整建議

• 規範 SQL 格式 (統一大小寫、縮排、分號)。
• 使用公用同義詞或統一 Schema 限定名。
• 應用程式框架層統一綁定變數命名慣例。
• SHARED_POOL_SIZE 足夠大 (監控 V$SGASTAT / V$LIBRARYCACHE 刷新率)。

4. FROM 子句表順序影響 (僅 RBO 有效)

RBO 從右至左解析 FROM 列表，最右表為 Driving Table (驅動表)，優先掃描並排序。
原則：最小結果集 (最少行數) 的表放最右。

TAB1: 16,384 rows  |  TAB2: 1 row
SELECT COUNT(*) FROM tab1, tab2;  -- 0.96s (TAB2 驅動，佳)
SELECT COUNT(*) FROM tab2, tab1;  -- 26.09s (TAB1 驅動，差)

多表 (>3) 連接：選擇 Intersection Table (被多表參照的樞紐表) 作為驅動表。

-- EMP 連接 LOCATION、CATEGORY
SELECT * FROM location l, category c, emp e  -- 差
WHERE e.emp_no BETWEEN 1000 AND 2000
  AND e.cat_no = c.cat_no AND e.locn = l.locn;

SELECT * FROM emp e, location l, category c  -- 佳 (EMP 為樞紐)
WHERE e.cat_no = c.cat_no AND e.locn = l.locn
  AND e.emp_no BETWEEN 1000 AND 2000;

5. WHERE 子句條件順序 (自下而上解析)

RBO/CBO 均傾向 最後寫入的條件最先評估 (作為過濾謂詞)。
原則：連接條件寫在前，過濾性最強 (過濾掉最多行) 的條件寫在最後。

-- 低效 (156.3s)
SELECT ... FROM emp e
WHERE sal > 50000
  AND job = 'MANAGER'
  AND 25 < (SELECT COUNT(*) FROM emp WHERE mgr = e.empno);

-- 高效 (10.6s) - 子查詢最強過濾寫最後
SELECT ... FROM emp e
WHERE 25 < (SELECT COUNT(*) FROM emp WHERE mgr = e.empno)
  AND sal > 50000
  AND job = 'MANAGER';

核心原則總結

1. 統計資訊是 CBO 的生命：無統計 = 盲目優化。
2. 共享 SQL 需嚴格規範：格式、物件、綁定變數三一致。
3. 驅動表選擇決定連接效能：小表/樞紐表優先。
4. 過濾順序影響存取路徑：強過濾後置。
5. 綁定變數 + 統計 + 索引 = 穩定高效計畫。

結語

掌握優化器模式、存取路徑、共享機制、連接順序與條件排列，是 SQL 調優的「內功心法」。下篇將進入具體改寫技巧 (SELECT*、Round Trip、DECODE、TRUNCATE 等) 與實戰避坑指南。

1. 上一篇文章： Oracle SQL 效能調優實戰指南 (下)：進階技巧與避坑清單
2. 下一篇文章： Oracle Job 排程異常診斷實錄：497 天系統運作導致計時器溢位

Oracle 資料庫基礎

Oracle ESCAPE 轉義字符完整指南：LIKE 查詢中通配符_% 的精確匹配

核心語法 WHERE column LIKE pattern

Oracle 資料庫物件大小寫敏感性完整解析與最佳實踐

前言：為什麼 Oracle 的大小寫會讓開發者頭痛？ 在資料

Oracle 資料庫系統架構總體圖解析

Oracle 資料庫系統架構：實例與資料庫檔案的完整圖解 O

Oracle TO_DATE 與 TO_CHAR 日期轉換完全指南：格式模型、常見錯誤與最佳實踐

核心函數語法 TO_DATE(string, format_