Oracle Shared Pool 核心機制深度解析六部曲：Free List 演進、Library Cache 結構、Latch 競爭與實戰案例

系列一：Free List Bucket 演進（8i → 9i）

Shared Pool 透過 Free List 管理空閒記憶體塊，依大小分桶。8i 僅 11 個 Bucket（44~32780 位元組指數增長），導致小塊集中 Bucket 0，碎片極多（單 Bucket 超 2000 chunk 即判定碎片過多），搜尋耗時造成 Latch 爭用。9i 擴展為 255 個 Bucket：

• Bucket 0~199：大小以 4 位元組遞增（16~812）
• Bucket 200~249：以 64 位元組遞增（876~4012）
• Bucket 250~254：大步長（4096~65548+）

細緻分桶大幅降低單 Bucket chunk 數，提升分配效率，解決 8i 過大 Shared Pool 反而降低效能的悖論。

系列二：X$KSMSP 內部視圖監控碎片

X$KSMSP（Kernel Storage Memory Management SGA Heap）每列代表一個 Chunk。關鍵欄位：

• KSMCHCOM：註釋（用途）
• KSMCHSIZ：塊大小
• KSMCHCLS：類別——FREE（可自由分配）、RECR（可重建，如共用 SQL）、FREEABL（Session/Call 結束可釋）、PERM（永久）

啟動初期 ~2259 chunks，查詢 DBA_OBJECTS 後增至 2358，硬解析導致分配/分割產生碎片。可建立暫存表定期快照比對 CHUNK 與 SUM 變化，量化碎片演進。

系列三：Library Cache 結構與 Hash 表優化

Library Cache = Hash Table (Buckets) → 雙向鏈表 → Handle → Object (Dependency/Child/Authorization)。

• 8i：固定 512 Buckets，Bucket 內物件以 * 顯示計數，熱點 Bucket 鏈表極長。
• 9i：131072 Buckets，統計輸出改為「各鏈長度對應 Bucket 數」，如「長度 1 的 Bucket 有 217 個，長度 0 的有 130855 個」，極度稀疏，查找幾乎 O(1)。

事件 ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME LIBRARY_CACHE LEVEL 4/8/16/32' 可轉儲完整結構，Level 4 顯示 Handle/Object/Child/Dependency/Data Block（含 Heap Dump：Diana Tree、P-Code、Source Code、Shared Cursor Context）。

系列四：Child Cursor 共享與不共享根因

• 同樣 SQL 文本 → 同一 Handle/Hash/Address，但不同 Schema 查詢同名表產生不同 Child Cursor（Dependency 指向不同 Table Handle）。
• 大小寫敏感：SELECT * FROM SCOTT.DEPT vs SELECT * FROM SCOTT.dept 產生不同 Hash，導致無法共享。
• 解析流程：語法檢查（自右向左、自下而上） → Hash 計算 → Shared Pool 查找 → 語義分析（物件存在/權限/同義詞解析） → 優化器生成執行計劃。
• 規範化 SQL 撰寫（統一大小寫、使用繫結變數）是共享關鍵。

系列五：Library Cache Lock/Pin 等待事件實戰診斷

模擬案例：Session 1 執行含 DBMS_LOCK.SLEEP 的 Procedure 持有 Shared Pin，Session 2 GRANT 同一 Procedure 等待 Exclusive Pin → Library Cache Pin 等待。查詢鏈路完整呈現。

系列六：實戰案例——TIMED_STATISTICS 導致 7000+ Child Cursor 暴增

現象

簡訊群發系統周期性擁塞，V$SESSION_WAIT 大量 LATCH FREE，鎖定 SHARED POOL (latch#98) 與 LIBRARY CACHE (latch#99)。V$SQLAREA 發現單一 INSERT 陳述式（使用繫結變數）VERSION_COUNT = 7023。

根因

Metalink Bug 1210242：啟用 TIMED_STATISTICS=TRUE 時，特定 SQL 游標不共享。執行進度監控（_SQLEXEC_PROGRESSION_COST 預設 1000）引入額外 Row Source，導致成本/計劃變化產生大量 Child Cursor，佈滿同一 Bucket 鏈表，掃描競爭劇烈。

解決方案（二選一）

1. ALTER SYSTEM SET TIMED_STATISTICS=FALSE（動態生效） → Child Cursor 迅速收斂至 1~2 支。
2. 設定隱含參數 _SQLEXEC_PROGRESSION_COST=0 關閉進度監控。

驗證：關閉後 V$SQL 中 OPTIMIZER_COST > 1000 的 SQL 僅 5 筆，其中 4 筆 VERSION_COUNT > 10，問題徹底解決。

總結建議

• 9i+ 環境受惠於細緻 Free List 與巨大 Library Cache Hash 表，Shared Pool 可適度調大。
• 嚴格規範 SQL 寫法（大小寫、繫結變數），減少硬解析與 Child Cursor 暴增。
• 謹慎啟用 TIMED_STATISTICS 與進度監控，必要時關閉隱含參數。
• 定期監控 V$LATCH（SPIN_GETS）、V$SQLAREA.VERSION_COUNT、X$KSMSP 碎片趨勢。