Oracle 資料庫物件深度解析：SGA 架構、命中率與 I/O 爭用優化

Oracle 實例架構回顧

每個 Oracle 實例由一組背景進程（DBWR, LGWR, SMON, PMON 等）與一個 SGA（System Global Area）記憶體區組成。背景進程自動讀寫資料檔，系統效能主要受兩大因素影響：SGA 分配效率與I/O 與鎖爭用。

一、SGA 核心元件與命中率優化

1. 共享池

包含庫快取（共享 SQL/PL/SQL 區）、資料字典快取與會話資訊（MTS 架構）。這些資訊高頻存取，命中率至關重要。

• 庫快取命中率：SELECT gethitratio FROM v$librarycache 應 > 90%；SELECT SUM(reloads)/SUM(pins) FROM v$librarycache 應 < 1%。
• 資料字典快取：SELECT SUM(getmisses)/SUM(gets) FROM v$rowcache 應 < 15%。

常見問題與對策：

• 大型匿名區塊導致 SQL 無法重用：查詢 SELECT * FROM v$sqlarea WHERE command_type=47 AND LENGTH(sql_text)>500 找出問題 SQL，改寫為預存程序。
• 核心 SQL 被擠出共享池：對高頻使用的程序/函數/套件執行 EXEC dbms_shared_pool.keep('object_name') 釘住記憶體。

2. 資料區塊緩衝區

存放熱門資料塊與修改後的髒塊，採 LRU 演算法淘汰。命中率查詢：

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（或 10g+ 的 db_cache_size）。小型常用編碼表可用 ALTER TABLE ... CACHE 強制駐留。

3. 重做日誌緩衝區

暫存 DML/DDL 的重做項。空間不足會導致用戶進程等待 log buffer space 事件，需適度調大 log_buffer 參數。

二、I/O 與資源爭用優化

1. 避免動態空間擴展

• 採用本地管理表空間（LMT），消除字典表空間爭用。
• 設計與試運行階段預估空間，預先分配足夠初始/下一擴展區，減少運行期動態分配開銷。

2. 監控低效 SQL 與索引缺失

資料量增長後，原本高效的 SQL 可能退化。需定期查詢 v$sql、v$sql_plan 找出全表掃描、索引失效的語句，透過建立索引、改寫 SQL、收集統計資訊修正。

3. 減少閂與鎖爭用

調整初始化參數如 db_block_lru_latches、dml_locks 等，依實際爭用熱點（查詢 v$latch, v$lock）微調，降低並發等待。

結語

掌握 SGA 內部運作機制與關鍵指標監控，是從「會用」進階到「會優」的分水嶺。持續觀測、量化分析、精準調整，方能榨乾硬體效能。