Oracle 10g 共享池完整指南：架構組件、大小設定、命中率監控與效能調優

共享池架構

共享池是 SGA 中負責 SQL/PLSQL 解析、編譯、執行的記憶體區域，包含三大子組件：

大小設定與查詢

• 控制參數：SHARED_POOL_SIZE (Bytes)
• 查詢三法：參數檔、SELECT NAME,VALUE FROM V$PARAMETER WHERE NAME LIKE '%SIZE'、SHOW PARAMETER SHARED_POOL_SIZE

命中率監控查詢

1. 資料字典快取命中率

-- 總計成功/失敗次數
SELECT SUM(GETS) "DICTIONARY_GETS",
       SUM(GETMISSES) "DICTIONARY_GETMISSES"
FROM V$ROWCACHE;

-- 詳細分類命中率
SELECT PARAMETER, GETS, GETMISSES,
       GETMISSES/(GETMISSES+GETS)*100 "MISS_RATIO",
       (1 - SUM(GETMISSES)/SUM(GETMISSES+GETS))*100 "HIT_RATIO"
FROM V$ROWCACHE
WHERE GETS+GETMISSES <> 0
GROUP BY PARAMETER, GETS, GETMISSES;

2. Library Cache 重載率（失敗率，應 < 1%）

SELECT SUM(PINS) "TOTAL_PINS",
       SUM(RELOADS) "TOTAL_RELOADS",
       SUM(RELOADS)/SUM(PINS)*100 "LIBRARY_CACHE_MISS_RATIO"
FROM V$LIBRARYCACHE;

• PINS：物件被釘住在記憶體中的次數
• RELOADS：物件被踢出後重新載入次數

調優建議

• 應用大量預存程序/套件時，需適度增大 SHARED_POOL_SIZE
• 監控 LIBRARY_CACHE_MISS_RATIO，超過 1% 考慮擴大共享池或檢查未使用綁定變數導致的硬解析過多
• 資料字典快取命中率低時，檢查是否頻繁 DDL 操作導致字典失效
• 10g 可啟用自動共享記憶體管理 (SGA_TARGET)，讓 Oracle 動態平衡各組件大小

SGA 總覽

SHOW SGA
SELECT * FROM V$SGA;

總結

共享池是 SQL 執行效能的核心。透過 V$ROWCACHE 與 V$LIBRARYCACHE 監控字典與 Library Cache 命中率，配合綁定變數使用、適度設定 SHARED_POOL_SIZE 或啟用 SGA_TARGET 自動管理，能有效降低硬解析、提升系統吞吐量。