Oracle 效能調校核心：SGA 記憶體結構優化實戰

一、Shared Pool 優化優先級最高

Shared Pool Cache Miss 代價遠大於 Buffer Cache Miss。關鍵在 Library Cache（SQL/PL/SQL 區）。

關鍵指標與閾值

物件釘選策略

-- 找出未釘選的大型 PL/SQL 物件
SELECT * FROM v$db_object_cache
 WHERE sharable_mem > 10000
   AND type IN ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY')
   AND kept = 'NO';

-- 釘選常用大物件：常用 Package、觸發器、Sequence（防號碼遺失）
EXEC DBMS_SHARED_POOL.KEEP('schema.package_name');

匿名區塊優化

• 大型匿名 PL/SQL → 拆小區塊呼叫 Stored Procedure。
• 查找超大匿名區塊：SELECT sql_text FROM v$sqlarea WHERE command_type=47 AND LENGTH(sql_text)>500;

9i+ 新功能

• V$SQL_PLAN 保存執行計畫，利於事後診斷。
• 考慮啟用 CURSOR_SHARING=SIMILAR 減少硬解析（評估副作用）。

二、Buffer Cache 優化

Granule 與 SGA 粒度

• SGA < 128M → Granule = 4M；否則 16M。DB_CACHE_SIZE 需為 Granule 整數倍。

依 V$DB_CACHE_ADVICE 科學調整

SELECT size_for_estimate, buffers_for_estimate,
       estd_physical_read_factor, estd_physical_reads
  FROM v$db_cache_advice
 WHERE name = 'DEFAULT'
   AND advice_status = 'ON'
   AND block_size = (SELECT value FROM v$parameter WHERE name='db_block_size');

目標：ESTD_PHYSICAL_READ_FACTOR <= 1（無額外物理讀獲益）。

Cache Hit Ratio 計算與提升

SELECT 1 - (phy.value - dir.value - lob.value) / log.value
  FROM v$sysstat log, v$sysstat phy, v$sysstat dir, v$sysstat lob
 WHERE log.name = 'session logical reads'
   AND phy.name = 'physical reads'
   AND dir.name = 'physical reads direct'
   AND lob.name = 'physical reads direct (lob)';

• 目標 > 90%。
• 提升手段：增大 Buffer Cache、多 Buffer Pool（KEEP/RECYCLE）、ALTER TABLE ... CACHE、Sort/Parallel 專用 Cache。

影響因子

• 全表掃描、應用設計不當、大表隨機存取、Cache Hit 分布不均。

自動段空間管理 (ASSM)

表空間採用 SEGMENT SPACE MANAGEMENT AUTO，消除 Free List 爭用。

三、其他 SGA 元件

Redo Log Buffer

• 參數 LOG_BUFFER（預設 ~500K，OS 相關）。
• 檢查 V$SESSION_WAIT 是否有 log buffer wait；V$SYSSTAT redo buffer allocation retries 接近 0（< 1% redo entries）。
• Log File Switch 等待 → 移至快速儲存、增大 Log File、增加 Group。
• Checkpoint 設定：LOG_CHECKPOINT_INTERVAL/TIMEOUT；Alert.log 出現 checkpoint not complete 需調整。
• Archive 等待 → 檢查歸檔目的地空間、增加 LOG_ARCHIVE_MAX_PROCESSES。
• DB_BLOCK_CHECKSUM=TRUE 增加 CPU 但保障一致性。

Java Pool

• 大型 Java 應用 ≥ 50M；一般預設 20M 足夠。

DB Writer 程序 (DBWn)

SELECT total_waits FROM v$system_event WHERE event='free buffer waits';

有等待 → 增加 DB_WRITER_PROCESSES 或改善 Buffer Cache/磁碟 I/O。