Oracle 效能調優三板斧：SGA 配置、統計收集與優化模式調整

前言：調優思路層層遞進

資料庫效能調優是永恆的議題。資深 DBA 常要求提供 Statspack、AWR 報表或執行計劃，但許多問題源頭其實在基礎配置層面。調優應遵循「由大到小」原則：網路 → 硬體 → OS → Oracle 實例 → 資料結構 → 單一 SQL。網路與硬體往往難以變更，應用程式碼也難動，因此重點落在實例參數配置與統計信息管理上。本文分享三招實戰「板斧」，專針對 OLTP 系統與 CBO 環境。

第一板斧：設定合適的 SGA

SGA（System Global Area）是 Oracle 共享記憶體區，核心組件為共享池與資料緩衝區。記憶體分配不當是「硬體好卻跑慢」的主因。以下為參考配置（假設專用資料庫主機，預留 OS 1-2GB）：

關鍵提醒：共享池過大反而增加 Latch 爭用；資料緩衝區宜大不宜小，但必須給 OS 與其他程序留足記憶體。

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

Oracle 預設優化模式為 CHOOSE。若表無統計信息，CBO 無法計算成本，常退回全表掃描，導致大量磁碟 I/O。解決之道：收集統計信息並確認優化模式。

收集統計信息

-- 小表（<100萬行）全採樣
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'TABLE_NAME', estimate_percent => 100, cascade => TRUE);

-- 大表採樣 10%-20%，避免過低導致統計失真
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'BIG_TABLE', estimate_percent => 10, cascade => TRUE);

-- 查看最後分析時間
SELECT table_name, last_analyzed, num_rows FROM dba_tables WHERE owner = 'SCHEMA';
SELECT index_name, last_analyzed FROM dba_indexes WHERE table_owner = 'SCHEMA';

實測對比：分析前後的執行計劃與耗時

環境：表 CASE_GA_AJZLZ 約 35 萬筆，有主鍵。

SQL> SET AUTOTRACE ON
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM CASE_GA_AJZLZ;

分析前：執行計劃顯示 FULL TABLE SCAN，耗時較長。
分析後：執行計劃轉為 INDEX FAST FULL SCAN 或 INDEX RANGE SCAN，耗時大幅下降。

第三板斧：善用 Statspack / AWR 定位頂級 SQL

基礎配置到位後，針對「跑最慢」或「資源消耗最大」的 SQL 進行精準調優：

1. 跑 Statspack 或 AWR 報表（@?/rdbms/admin/spreport.sql 或 DBMS_WORKLOAD_REPOSITORY）。
2. 關注「Top 5 Timed Events」、「SQL ordered by Elapsed Time/Buffer Gets/Executions」。
3. 對目標 SQL 生成執行計劃（EXPLAIN PLAN 或 DBMS_XPLAN.DISPLAY_CURSOR）。
4. 檢查：統計信息是否最新？索引是否缺失？SQL 寫法是否導致索引失效？連接順序是否最優？
5. 應用 Hint、改寫 SQL、新增/調整索引、調整優化模式或參數（如 OPTIMIZER_INDEX_COST_ADJ）。

附錄：常用快速檢查腳本

-- 檢查 SGA 組件大小
SELECT component, current_size/1024/1024 AS size_mb FROM v$sga_dynamic_components;

-- 檢查優化模式
SHOW PARAMETER optimizer_mode;

-- 檢查統計信息健康度
SELECT owner, table_name, num_rows, last_analyzed, stale_stats
FROM dba_tab_statistics
WHERE owner NOT IN ('SYS','SYSTEM') AND stale_stats = 'YES';

總結

「三板斧」不包治百病，但能解決 80% 以上的基礎效能問題：SGA 配對、統計收集、模式正確。打好地基，再談 SQL 級微調，事半功倍。