Oracle Shared Pool调优指南

为什么 Shared Pool 必须优先调优？ 一次 Shared Pool Miss 的成本远高于 Data Buffer Miss：Data Buffer 只涉及数据页，而 Shared Pool 还要重新解析 SQL、加载数据字典、生成执行计划，CPU 和 Latch 争用瞬间飙升。 由于 Dictionary Cache 的对象在内存中驻留时间通常比 Library Cache 长，因此优化的核心就落在 Library Cache 上。

---

核心指标与判定标准

视图/指标	公式	健康阈值	影响范围
v$librarycache.gethitratio	Gets-hit / Gets	≥ 90 %	SQL 软解析命中率
v$librarycache.reloads/pins	Reloads / Pins	< 1 %	SQL 硬解析/重解析
v$rowcache.sum(getmisses/gets)	Dict Cache Miss	< 15 %	数据字典命中率
v$shared_pool_reserved	request_misses、free_memory	request_misses=0 且 free_memory>50%	保留池是否浪费

---

7 步调优路线图

1. 检查 SQL AREA 命中率 — 如果低于 90%，优先优化应用：减少动态 SQL、统一大小写、绑定变量
   SELECT namespace,
         gethitratio
   FROM   v$librarycache
   WHERE namespace = ‘SQL AREA’;
2. 计算 Reload / Pin 比率 SELECT SUM(pins)                                 AS executions,
         SUM(reloads)                              AS cache_misses,
         ROUND(SUM(reloads) * 100 / SUM(pins), 2)  AS reload_ratio_pct
   FROM   v$librarycache;• reload_ratio_pct ≥ 1% → 增大 shared_pool_size 或排查对象失效（truncate/alter 等 DDL）。
3. 评估 Shared Pool Reserved Size SELECT request_misses,
         free_space,
         ROUND(free_space * 100 / :shared_pool_reserved_size, 2) AS free_pct
   FROM   v$shared_pool_reserved;• request_misses = 0 且 free_pct > 50 % 可压缩保留池。 • 经验值：保留池占总池 10%，绝对不超过 50%。
4. 拆分巨型匿名 PL/SQL — 找出大于 500 字符的匿名块
   SELECT sql_id,
         LENGTH(sql_text) len,
         sql_text
   FROM   v$sqlarea
   WHERE command_type = 47
    AND LENGTH(sql_text) > 500;处理方案： A. 改成小包 + 存储过程； B. 若必须保留，用 dbms_shared_pool.keep 钉住。
5. 钉住常用大对象，防止碎片化 — 查找未钉住的大对象 (>10 KB)
   SELECT owner,
         name,
         type,
         sharable_mem
   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(‘HR.EMP_PKG’);
6. 查看 SQL 执行计划（9i+） — 利用 v$sql_plan 进行 Plan 分析
   SELECT *
   FROM   v$sql_plan
   WHERE sql_id = ‘b6usrg82hwsa3’;
7. 调优 Dictionary Cache SELECT ROUND(SUM(getmisses) * 100 / SUM(gets), 2) AS dict_miss_pct
   FROM   v$rowcache;
   ​
   — > 15 % 就增加 shared_pool_size

Q：shared_pool_size 到底设多大？ A：先按经验值 SGA 的 20 %～30 %，再通过上面脚本逐步收敛。超过 10 GB 要关注保留池和子池（_kghdsidx_count）细分。

Q：钉对象太多会不会撑爆？ A：只钉高频且体积大的包/过程，小对象让 LRU 自己打理。用 v$db_object_cache.kept='YES' 定期复查。

Q：应用已用绑定变量，reload 仍高？ A：排查 DDL 操作，尤其是在线 truncate partition、grant、comment 都会导致对象失效。

---