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

1. 選擇適合的優化器

建議： 明確指定 RULE 或 COST，避免 CHOOSE 導致不穩定計畫。使用 CBO 必須定期 ANALYZE 或 DBMS_STATS 更新統計。

2. 表存取兩大路徑

• Full Table Scan：循序讀取所有區塊，多區塊讀取優化
• ROWID 存取：透過 Index 取得 ROWID → 直接定位資料塊，適合高選擇性查詢

3. 共享 SQL (Shared Cursor) 機制

• 首次解析後存入 SGA Shared Pool，後續相同 SQL 直接複用解析樹與執行計畫
• 需滿足三條件：
  1. 字元級完全相同（含空白、換行）
  2. 參照物件完全相同（同義詞、擁有者）
  3. 繫結變數名稱完全相同
• SHARED_POOL_SIZE 越大，可快取越多 SQL，命中率越高
• 僅支援單表查詢快取，多表 Join 不支援

共享失效典型案例

-- 大小寫不同 → 不共享
SELECT * FROM EMP;
select * from emp;

-- 同義詞指向不同物件 → 不共享
-- User A: private synonym sal_limit → table A
-- User B: private synonym sal_limit → table B
SELECT max(sal_cap) FROM sal_limit;  -- A/B 無法共享

-- 繫結變數名稱不同 → 不共享
SELECT pin FROM people WHERE pin = :blk1.pin;
SELECT pin FROM people WHERE pin = :blk1.ot_ind;

管理建議

• 統一 SQL 寫作風格（大寫關鍵字、固定縮排）
• 使用公用同義詞或 Schema 前綴消除物件解析歧義
• 規範繫結變數命名慣例
• 監控 V$SQLAREA、V$SQL_SHARED_CURSOR 分析未共享原因