ORA-04031：一次共享池内存碎片化排查与解决

生产环境在 编译或执行大型存储过程 / 大型 SQL 包 时，SQL*Plus 抛出：

ORA-04031: unable to allocate 4128 bytes of shared memory

典型场景：

• 连续执行 ALTER PACKAGE xxx COMPILE BODY;
• 一次性加载 10 MB 以上的 PL/SQL 包
• 业务高峰期间，共享池命中率骤降，伴随大量 library cache pin / lock 等待

---

根因定位

ORA-04031 的本质只有两类：

分类	说明
容量不足	SHARED_POOL_SIZE 设置太小，无法容纳所有 SQL/PLSQL 对象
碎片化	总空闲空间足够，但 没有连续大块内存 满足当前请求

通过以下手段快速判断属于哪一类：

快速自检脚本

-- 1. 共享池总体使用情况
SELECT pool, name, bytes/1024/1024 MB
  FROM v$sgastat
 WHERE pool = 'shared pool'
   AND name IN ('free memory','library cache','sql area');
​
-- 2. 空闲块分布（碎片指标）
SELECT ksmchcls, ksmchsiz, COUNT(*)
  FROM x$ksmsp
 WHERE ksmchcls = 'free'
 GROUP BY ksmchcls, ksmchsiz
 ORDER BY 2;
​
-- 3. 大对象 TOP
SELECT owner, name, type, sharable_mem/1024/1024 MB
  FROM v$db_object_cache
 WHERE sharable_mem > 1*1024*1024
 ORDER BY sharable_mem DESC;

经验值： 如果 free memory 剩余 > 100 MB，但最大空闲块（MAX(ksmchsiz)）< 1 MB，基本可判定为 碎片化。

---

采集现场

为后续复现与根因分析，建议一次性打包以下信息：

-- 1. AWR/ASH 报告（覆盖故障时段）
@?/rdbms/admin/awrrpt.sql
@?/rdbms/admin/ashrpt.sql
​
-- 2. 共享池转储（轻量）
ALTER SYSTEM SET EVENTS 'immediate trace name heapdump level 2';
​
-- 3. 对象统计
SPOOL /tmp/sp_report.txt
SELECT * FROM v$shared_pool_reserved;
SELECT * FROM v$library_cache_memory;
SELECT * FROM v$sgainfo WHERE name LIKE '%Shared%';
SPOOL OFF

---

解决方案

应急止血

方案	命令	适用场景
Flush 共享池	ALTER SYSTEM FLUSH SHARED_POOL;	碎片化严重，业务可接受硬解析
手动 pin	EXEC dbms_shared_pool.keep('PKG_XXX','P');	关键大对象常驻，减少换入换出
重启实例	SHUTDOWN ABORT; STARTUP;	碎片无法缓解，且业务允许短暂中断

FLUSH SHARED_POOL` 会导致所有 SQL 重新硬解析，请谨慎在高峰期使用。

根治调优

1. 扩大共享池 ALTER SYSTEM SET SHARED_POOL_SIZE=2G SCOPE=BOTH;建议参考 AWR 中 Shared Pool Advisory，找到命中率拐点。
2. 减少碎片
   • 避免频繁创建/销毁临时 SQL，改用绑定变量
   • 批量部署时将大包拆分为多个小包
   • 使用 DBMS_SHARED_POOL.KEEP 固定常用对象
3. 参数微调 ALTER SYSTEM SET “_kghdsidx_count”=1 SCOPE=SPFILE;  — 减少子池，降低碎片
   ALTER SYSTEM SET SHARED_POOL_RESERVED_SIZE=200M;
4. 升级到 19c+ 19c 后引入 Memoptimize Pool，可彻底消除共享池碎片。

维度	经验值
空闲块 < 1 MB	碎片化
空闲块 > 1 MB 但总量 < 200 MB	容量不足
大包 > 10 MB	建议拆分或使用 KEEP

“ORA-04031 不是共享池太小，就是太空但太碎。”

---