Oracle 效能調優三板斧：SGA 設定、統計資訊收集、等待事件分析實戰

調優思維：由大到小，由外而內

資深 DBA 面對「Oracle 很慢」時，不會急著抓最慢 SQL，而是遵循：網路 → 硬體 → OS → Oracle 參數 → 資料結構 → 單支 SQL 的層層排查。網路與硬體往往難以變更，應用程式碼也難動，因此Oracle 參數調校與資料結構優化是最高 CP 值的切入點。本文總結三大核心調優動作（三板斧），針對 OLTP、CBO 環境極具實效。

第一斧：設定合適的 SGA 記憶體架構

SGA 太小導致頻繁磁碟 I/O，太大造成 OS 交換。依實體記憶體建議配置：

關鍵參數說明

• SHARED_POOL_SIZE：存放 SQL 執行計劃、PL/SQL 程式碼、字典快取。太小導致硬解析激增、Library Cache 爭用
• DB_CACHE_SIZE / DB_BLOCK_BUFFERS：資料塊快取。OLTP 系統核心，命中率應 > 95%
• PGA_AGGREGATE_TARGET：別忘了 PGA，排序、雜湊聯結、位圖操作都在這裡，建議佔實體記憶體 20%~30%

實務檢查指令

-- 檢查 SGA 元件大小
SELECT * FROM V$SGAINFO;

-- Buffer Cache 命中率
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets','consistent gets','physical reads');
-- 命中率 = 1 - (physical reads / (db block gets + consistent gets))

第二斧：分析表與索引，切換優化模式

為什麼要收集統計資訊

Oracle 預設優化模式 CHOOSE：若表無統計資訊 → RBO（規則基礎）→ 全表掃描；有統計 → CBO（成本基礎）→ 走索引。未分析的表是全表掃描元兇。

收集統計資訊指令

-- 單表完整分析（<100 萬筆建議）
ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS;

-- 大表抽樣分析（百分比勿太低，建議 10%~30%）
ANALYZE TABLE BIG_TABLE ESTIMATE STATISTICS SAMPLE 20 PERCENT;

-- 索引分析
ANALYZE INDEX IDX_NAME COMPUTE STATISTICS;

-- 10g/11g+ 建議用 DBMS_STATS（功能更強、支援並行、直方圖）
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME', CASCADE => TRUE, DEGREE => 4);

驗證分析效果

-- 檢查最後分析時間
SELECT TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'SCHEMA';
SELECT INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE TABLE_OWNER = 'SCHEMA';

-- 開啟 AUTOTRACE 對比執行計劃
SET AUTOTRACE ON EXPLAIN
SET TIMING ON
SELECT COUNT(*) FROM CASE_GA_AJZLZ;  -- 35 萬筆測試表

實測對比範例

分析前：全表掃描，Cost 高，Physical Reads 大

分析後：索引快速全掃描 / 索引範圍掃描，Cost 大幅下降，邏輯讀取代替物理讀取

優化模式參數

ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS_10;  -- 線上交易優先回應前 10 筆
ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS SCOPE=BOTH;  -- 批次/報表優先總成本

第三斧：等待事件分析——精準定位瓶頸

調優核心轉向「優化等待」。查詢 V$SESSION_WAIT、V$SYSTEM_EVENT 找出系統在等什麼。

核心等待事件速查

快速找出高耗 Session 與 SQL

-- 依 Physical Reads 排序，找出高 I/O Session
SELECT se.sid, se.serial#, pr.spid, se.username, se.program, 
       st.event, si.physical_reads, si.block_changes
FROM v$session se, v$session_wait st, v$sess_io si, v$process pr
WHERE st.sid = se.sid AND si.sid = se.sid AND se.paddr = pr.addr
  AND se.sid > 6 AND st.wait_time = 0 AND st.event NOT LIKE '%SQL%'
ORDER BY si.physical_reads DESC;

-- 取得該 Session 正在執行的 SQL
SELECT sql_text FROM v$sqltext WHERE address = (
    SELECT sql_address FROM v$session WHERE sid = :sid
) ORDER BY piece;

常見 SQL 寫法優化口訣

• IN → 改 EXISTS（IN 內部轉 OR，即使有索引也慢）
• NOT IN → 改 NOT EXISTS（NULL 語意陷阱 + 效能）
• SELECT COUNT(*) 判斷存在 → 改 EXISTS (SELECT 1 ...)
• WHERE 條件順序 → 依索引欄位順序、選擇性高者優先
• 避免索引欄位上做函數/運算 → WHERE TRUNC(date_col) = ... 會導致索引失效

附錄：Burleson 效能報告腳本

文章提及的附件腳本可自動產出：SGA 狀態、Top 10 高耗 SQL、等待事件統計、表空間使用率等報表，建議納入每日巡檢自動化。

總結

1. SGA 記憶體給足、分對：Buffer Cache 最大、Shared Pool 適中、預留 OS/PGA
2. 統計資訊要新、要全：定期 DBMS_STATS、大表抽樣、直方圖給斜態欄位
3. 看等待事件找瓶頸：V$SESSION_WAIT 是調優羅盤針，對症下藥事半功倍

這三板斧解決 80% 的 OLTP 效能問題。剩下 20% 需深入 SQL 重寫、架構調整（分區、MV、快取）、應用層優化，屬於進階戰役。

1. 上一篇文章： Oracle 資料庫設計規範化核心原則：避免空值與重複欄位的實戰策略
2. 下一篇文章： Oracle RAC 部署避坑指南：11 大關鍵檢查點與三階段安裝流程

Oracle 資料庫基礎

《構建 Oracle 高可用環境》：淘寶實戰經驗凝聚的企業級資料庫架構聖經

權威推薦：業界大咖聯袂背書 馮春培（阿里巴巴首席 DBA、

Oracle 使用者密碼管理：解鎖、變更與還原密碼實作

適用情境 使用者帳號被鎖定（ORA-28000）。 需以特

Oracle 全局臨時表實戰指南：會話級與事務級差異、適用場景與擴展設計

核心價值 大表關聯查詢時，將中間結果集物化到臨時表，避免重複

Oracle 分組統計實戰：TRUNC 日期截取、DECODE/CASE 條件聚合與多種寫法對比

業務場景 統計 heart_active 欄位（0/1）在不