Oracle 性能優化三板斧：SGA 科學配置、統計信息收集與優化器模式鎖定

優化哲學：自大處著眼，自小處著手

網絡、硬件、OS 往往不可變，應用代碼難改 → 重點優化資料庫配置與數據結構，建立良好基線，再針對慢 SQL 微調。

第一板斧：科學配置 SGA

原則：留足 OS 與其他進程內存，32 位系統 SGA 上限 1.75GB（部分可突破）。

關鍵參數：SHARED_POOL_SIZE、DB_CACHE_SIZE（9i+）、LARGE_POOL_SIZE（並行/備份/共享服務器）、JAVA_POOL_SIZE、STREAMS_POOL_SIZE。

第二板斧：收集統計信息 + 鎖定優化器模式

現狀痛點

默認 OPTIMIZER_MODE = CHOOSE：表無統計 → RBO（全表掃描多）；表有統計 → CBO。同一 SQL 在測試/生產產生截然不同計劃，極不穩定。

行動指南

1. 全庫收集統計信息：

   EXEC DBMS_STATS.GATHER_DATABASE_STATS(options => 'GATHER AUTO');

   或按表：ANALYZE TABLE tab COMPUTE STATISTICS;（10g+ 推薦 DBMS_STATS）。
2. 大表採樣收集：ESTIMATE PERCENT 10（不可過低，否則統計失真）。
3. 檢查收集時間：

   SELECT table_name, last_analyzed FROM dba_tables WHERE owner = 'SCOTT';

4. 鎖定優化器模式：生產庫建議 ALTER SYSTEM SET OPTIMIZER_MODE = ALL_ROWS SCOPE=BOTH;（或 FIRST_ROWS_10），徹底禁用 CHOOSE。

實戰驗證：統計信息收集前後的執行計劃對比

-- 環境：表 CASE_GA_AJZLZ 約 35 萬行，有主鍵
SQL> SET AUTOTRACE ON
SQL> SET TIMING ON

-- 收集前（假設無統計，CHOOSE 退化為 RBO）
SQL> SELECT COUNT(*) FROM CASE_GA_AJZLZ;
-- 計劃：TABLE ACCESS FULL，Cost 高，耗時長

-- 收集統計
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER', 'CASE_GA_AJZLZ');

-- 收集後
SQL> SELECT COUNT(*) FROM CASE_GA_AJZLZ;
-- 計劃：INDEX FAST FULL SCAN (PK)，Cost 大幅降低，耗時秒級

第三板斧：建立執行計劃基線（進階）

使用 SQL Plan Management (SPM) 或 Stored Outlines 固化核心業務 SQL 計劃，防止統計信息波動導致計劃突變。

附件：Burleson 性能報告腳本

文中提及的附件腳本可自動輸出 SGA 配置、緩存命中率、等待事件、Top SQL 等關鍵指標，建議納入巡檢體系。