Oracle系統參數調優全攻略：SGA、Buffer Cache、Redo Log與競爭處理

一、SGA核心元件調優

1. Shared Pool (共用池) - 優先調優重點

Cache Miss在Shared Pool成本遠高於Buffer Cache。關鍵在Library Cache優化：

• Gethitratio > 90%：SELECT gethitratio FROM v$librarycache WHERE namespace='SQL AREA'; 否則檢查應用程式碼
• Reloads/Pins < 1%：SELECT SUM(reloads)/SUM(pins) FROM v$librarycache; 超標則增加shared_pool_size或檢查SQL物件合法性
• Reserved Size：設為Shared Pool 10%，上限50%。查V$SHARED_POOL_RESERVED，FREE_MEMORY > 50%時可壓縮
• 保留大型物件：DBMS_SHARED_POOL.KEEP('package_name'); 避免大型PL/SQL、Trigger、Sequence被踢出造成碎片
• Dictionary Cache：SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache; 應<15%，否則增大Shared Pool

2. Buffer Cache (緩衝區快取)

• Granule大小：SGA<128M則4M，否則16M。調整以Granule為單位
• 參考V$DB_CACHE_ADVICE：estd_physical_read_factor <= 1為宜
• Cache Hit Ratio > 90%：

  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、CACHE TABLE、建立排序/平行讀取專用Cache
• 表空間啟用自動空間管理 (ASSM) 減少自由列表競爭

3. 其他SGA元件

• Redo Log Buffer：LOG_BUFFER預設~500K。檢查V$SESSION_WAIT的log buffer wait、V$SYSSTAT的redo buffer allocation retries (應<1%)
• Java Pool：大型應用≥50M，一般20M足夠
• DBWn進程：SELECT total_waits FROM v$system_event WHERE event='free buffer waits'; 有等待則增加DBWn

二、關鍵檢查SQL速查表

原則：由共用池著手，再調整Buffer Cache，最後處理Redo/排序/競爭，層層遞進，避免過度調優。