Oracle 資料庫運行期效能調優實戰：OS 層與 DB 層雙軌優化全攻略

接續設計階段調優，本文聚焦 資料庫運行階段 的效能調優，分為作業系統層級與資料庫層級雙軌並進。掌握系統資源監控、SGA 元件調校、I/O 與鎖競爭排查，是 DBA 從「會用」進階到「會調」的關鍵里程碑。

一、作業系統層級調優：記憶體與 CPU 的博弈

核心目標

• 減少記憶體交換
• 控制分頁
• 使 SGA (System Global Area) 常駐記憶體

1. 監控工具：vmstat 與 sar

# 每 5 秒顯示系統摘要
vmstat 5

# 每 10 秒顯示 10 次分頁活動
sar -p 10 10

關注 swapping（交換）、paging（分頁）、磁碟 I/O、CPU 使用率。

2. 減少記憶體交換

• 停止不必要的 daemon 與應用程式進程
• 在不顯著降低資料區塊命中率前提下，縮減資料庫緩衝區釋放記憶體
• 減少 UNIX 檔案緩衝區（尤其使用裸裝置時）

3. 控制分頁

• 持續用 vmstat 或 sar -p 監控
• 增加實體記憶體（最直接有效）
• 將部分工作負載遷移至其他系統
• 調整核心參數減少核心記憶體佔用
• 確保 SGA 位於單一共享記憶體段

4. SGA 常駐記憶體

SGA 是快速存取資料的關鍵，若頻繁釋放/配置將嚴重拖慢效能。需重新配置 UNIX 核心，調整共享記憶體相關參數（如 shmmax、shmall），確保 SGA 鎖定在實體記憶體中。

二、資料庫層級調優：SGA 結構與競爭熱點

每個 Oracle 執行個體 = 後台進程群 + SGA 記憶體區。效能受 SGA 分配合理性、I/O 與鎖競爭影響。

1. SGA 四大元件與命中率監控

共用池 - 目標：命中率 > 90%，Reloads/Pins < 1%

-- Library Cache 命中率
SELECT gethitratio FROM v$librarycache;

-- Library Cache 重載率
SELECT SUM(reloads)/SUM(pins) FROM v$librarycache;

-- Data Dictionary Cache 缺失率
SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache;  -- 應 < 15%

優化手段：

• 發現大型匿名區塊（command_type=47 且 LENGTH(sql_text) > 500）改寫為預存程序以重用
• 高頻使用的 PL/SQL 物件用 DBMS_SHARED_POOL.KEEP('object_name') 釘住記憶體防被淘汰

資料區塊緩衝區 - 目標：命中率 > 85%

SELECT 1 - (phy.value / (cur.value + con.value))
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
  AND con.name = 'consistent gets'
  AND phy.name = 'physical reads';

命中率 < 0.85 需增加 DB_BLOCK_BUFFERS（或 9i+ DB_CACHE_SIZE）。小型常用編碼表可用 ALTER TABLE ... CACHE 強制緩存。

重做日誌緩衝區

過小導致 DML/DDL 等待寫入。監控 log buffer space 等待事件，適度調大 LOG_BUFFER。

2. I/O 與資源競爭排查

• 避免動態空間擴展：預先分配足夠空間，啟用表空間本地管理（LMT）減少資料字典競爭
• SQL 調優：運行期監控未命中索引或缺索引的 SQL，建立索引或改寫 SQL
• 鎖競爭：調整 db_block_lru_latches、dml_locks 等初始化參數降低 Latch/Lock 爭用

三、常用調優工具與腳本

• utlbstat.sql / utlestat.sql：區間效能快照
• 動態效能視圖（V$）與資料字典視圖：命中率、等待事件
• Oracle Enterprise Manager (OEM) 圖形化監控
• Statspack / AWR 報告：深度分析

結語

資料庫效能調優是系統工程，涉及 OS、網路、儲存、SQL、應用架構等多層面。沒有銀彈，只有持續的觀測、假設、驗證、迭代。DBA 需在大量實戰中累積經驗，建立直覺與方法論，才能讓 Oracle 高效可靠運行。