Oracle SGA 深度調優：Shared Pool、Buffer Cache、Redo Log Buffer 與 Java Pool

Shared Pool 調優優先級最高

Cache Miss 成本遠高於 Buffer Cache，關鍵在 Library Cache。

關鍵指標與閾值

大物件保留策略

-- 找出未保留的大包裹/程序/函數
SELECT * FROM v$db_object_cache
WHERE sharable_mem > 10000
  AND type IN ('PACKAGE','PROCEDURE','FUNCTION','PACKAGE BODY')
  AND kept = 'NO';

-- 保留
EXEC dbms_shared_pool.keep('package_name');

超大匿名 PL/SQL 區塊處理

• 拆解為小區塊呼叫預存程序。
• 或納入 Shared Pool 保留：SELECT sql_text FROM v$sqlarea WHERE command_type=47 AND LENGTH(sql_text)>500;

Buffer Cache 調優

Granule 粒度

• SGA < 128M → 4M；否則 16M。調整大小需為 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%。提升手段：增大 Cache、多 Buffer Pool (KEEP/RECYCLE)、CACHE 表、排序/平行讀取專用 Cache。

自動段空間管理 (ASSM)

表空間建立時指定 SEGMENT SPACE MANAGEMENT AUTO，消除自由列表爭用。

Redo Log Buffer

• 參數 LOG_BUFFER (預設 ~500K)。
• 檢查 V$SESSION_WAIT 是否有 log buffer wait、V$SYSSTAT 的 redo buffer allocation retries (應 < 1% redo entries)。
• 等待類型：log file switch completion / checkpoint / archiving → 對應增大日誌檔、調整 Checkpoint、檢查歸檔磁碟空間與 ARCn 進程數。
• DB_BLOCK_CHECKSUM=TRUE 增加 CPU 開銷但保障資料一致性。

Java Pool

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

DBWn 進程調整

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

若等待顯著，考慮增加 DB_WRITER_PROCESSES 或使用非同步 I/O。