1. 先搞清楚ORA-04031到底在说什么1.1 报错三个字段分别代表什么做DBA这些年只要系统一报ORA-04031我基本就能猜到接下来要面对什么一堆业务电话打过来、开发同事拿着报错截图问怎么回事、领导在OA里催处理进度。所以先别急着慌它其实是一个非常“直白”的错误——Oracle告诉你它在共享池Shared Pool里分配内存时失败了。完整报错长这样ORA-04031: unable to allocate 4096 bytes of shared memory (shared pool,SELECT /* INDEX(t) */ ...,sga heap(1,0),kglsim)拆开看这个错误分成三段信息无法分配的字节数比如这里的4096 bytes。它只是一个“当前这次分配请求”的大小不代表内存总量只差4KB很多时候是因为碎片化导致没有连续区域。内存池名称绝大多数情况下是“shared pool”偶尔也见“large pool”。后面讲到的排查思路主要围绕shared pool展开large pool的问题处理逻辑类似但更简单。括号内的原因注释这是最能说明问题的一段。常见的有kglsim搜索共享游标时内存分配失败、kglob加载对象时分配失败、kghu堆操作失败、kghalp大块分配失败。代码不同对应的根因方向也不同。如果你搜过网上的帖子会发现很多人直接甩一句“把shared_pool_size调大”。这种回答不能算错但十次里有六次解决不了根本问题甚至把SGA撑爆。正确做法是先分清楚是共享池总容量太小还是内存碎片化严重。两者症状一样处理方法完全不一样。1.2 共享池为什么会“满”还会“碎”先理解共享池里面装了什么。共享池的职责主要是三块库缓存Library Cache、字典缓存Dictionary Cache和内存块自由列表Free List。你执行的每一条SQL、每一个存储过程、每一次权限校验都要在这里留下“可复用的缓存对象”。打个比方共享池就像一个公共图书馆。SQL文本就是书的标题执行计划就是书的内容。读者会话要看这本书就去图书馆找找不到就现场用纸张打印一本放进去。问题是图书馆的总面积有限纸张消耗完就得把旧书扔掉腾地方。ORA-04031出现时往往是两类情况混在一起一是图书馆太小了总书架不够用。比如系统并发量大、SQL文本特别多、解析特别频繁shared_pool_size设小了怎么腾都腾不出空间。二是书架太零碎了。图书馆的书被一本本扔出去空出来的位置散落在各个角落。这时候即使空余空间总和足够大但单个连续书架不够容纳一本“大部头”书。Oracle分配内存时是按连续块Chunk找的找不到足够大的连续区域就直接报ORA-04031。典型的场景就是里面塞了大量需要几MB甚至十几MB的大对象比如超大的PL/SQL块或存储过程而可用空间恰好被拆碎了。另外要特别留意一个容易被忽视的变量——shared_pool_reserved_size保留池。Oracle会在共享池里划出一块“保留区”专门应对大对象分配。如果保留区没设置或者设置太小请求大块内存时就容易撞墙。很多生产环境默认没管这个参数出了ORA-04031只调shared_pool_size结果保留区还是那个小不点大对象照样分配失败。2. 诊断定位是没空间还是碎片化2.1 第一步先翻告警日志别急着调参遇到ORA-04031我的习惯是先去看alert_SID.log。告警日志里不光有错误本身还会把报错前一小段时间发生了什么也记录下来。比如某个DDL编译正在把相关对象所有游标都失效或者是某个外部程序提交了大量未绑定变量的SQL这些都能在日志前后文里找到线索。告警日志的路径根据diagnostic_dest和db_unique_name变化最简单的查看方式是sqlplus / as sysdba SQL show parameter diagnostic_dest;拿到路径后用tail -200看最近一段重点看报错时间点前后的会话状态。如果ORA-04031前后紧跟的是大量ORA-04031连环报错那基本可以断定内存已经被耗尽如果只偶发一两条更可能是碎片化或某个大对象请求失败。这里有个容易踩的坑不要一上来就FLUSH SHARED_POOL。清池子是应急手段它把整个库缓存全清空了所有SQL和对象缓存立刻失效全部会话在接下来几分钟内都在重新解析。如果业务高峰清池瞬间CPU会冲高系统反而更卡。下文会讲正确的应急顺序。2.2 三条关键SQL快速了解共享池现状进入数据库后我一般用以下几条SQL并行查看。第一条看共享池各组件占用情况第二条看碎片化程度第三条看保留区是否有分配失败记录。-- SQL 1共享池各组件当前大小 SELECT name, ROUND(bytes/1024/1024, 2) mb FROM v$sgastat WHERE pool shared pool ORDER BY 2 DESC;注意查询结果里有一个“free memory”它就是当前可用的空闲内存。如果free memory只有几十MB甚至十几MB说明共享池快满了。如果free memory总量看着还有两三百MB但依然报ORA-04031那八九成是碎片化或单块大内存请求。-- SQL 2查看空闲块按大小分组的分布情况 SELECT ksmchcls, COUNT(*) cnt, SUM(ksmchsiz) total_bytes FROM x$ksmsp WHERE ksmchcls LIKE free% GROUP BY ksmchcls;这个结果会比较直观如果“free”类块的单个chunk普遍只有几KB、几十KB而报错请求的是几MB那就说明连续空间不够。x$ksmsp这个内部视图需要足够权限一般DBA账号都能查但不要在生产库上频繁全表扫描压力大。-- SQL 3保留池分配失败次数 SELECT request_misses, request_failures, last_miss_time FROM v$shared_pool_reserved;request_failures如果一直是0说明保留区没有因为容量拒绝过大对象请求。如果这个值持续增长同时ORA-04031报错很频繁就需要考虑扩大shared_pool_reserved_size。最后用v$shared_pool_advice结合当前负载评估一下到底该给多大这是Oracle官方支持的内存评估建议视图-- SQL 4评估shared pool目标大小 SELECT shared_pool_size_for_estimate est_mb, estd_lc_size, estd_lc_memory_object_hits, estd_lc_time_saved FROM v$shared_pool_advice;看estd_lc_time_saved预计节省的解析时间随内存大小变化的曲线。一般选曲线趋于平缓的那个点再留20%余量就是比较合理的shared_pool_size预估。2.3 从业务侧找元凶高版本游标与未绑定变量内存调整只是治标真正的根因往往藏在库里。ORA-04031最经典的触发源头就是未绑定变量的SQL灌爆共享池。Oracle向共享池申请内存时需要把SQL文本、执行计划、游标状态放进库缓存。如果同一个业务逻辑每条SQL因为一个数字或一个日期不同而生成完全不同的文本库缓存里就会堆积成千上万条“长得差不多”的SQL。每条都占用内存很快把共享池挤满。排查方式很简单-- 查询高版本游标version_count越大说明越多的SQL无法共享 SELECT sql_id, version_count, executions, loads, SUBSTR(sql_text, 1, 60) sql_text FROM v$sqlarea WHERE version_count 20 ORDER BY version_count DESC;看到version_count特别高的SQL基本就是罪魁祸首。还有一个更深入的角度用v$sql_shared_cursor查看同一SQL为何不能被多个会话共享。这里记录了大量原因字段比如BIND_MISMATCH、OPTIMIZER_MISMATCH、AUTH_CHECK_MISMATCH等通过排除法找出系统把所有SQL当成“不同游标”的原因。另一个容易被忽略的场景是存储过程频繁失效重编译。如果某个业务表被反复TRUNCATE、重建或者DDL频繁变更引用这些对象的存储过程会进入INVALID状态下一次执行时全部需要重新加载到共享池。如果这个存储过程本身特别大又恰好在同一时刻被几百个会话并发调用大对象加载请求集中爆发同样会触发ORA-04031。这种可以通过查dba_objects里INVALID对象数量以及v$sqlarea里的invalidations字段来确认。3. 处理应急放行与根治手段3.1 应急方案清池与临时调参先明确一个原则ORA-04031出现后第一个动作是止损让业务先跑起来然后再慢慢查根因。止损手段按优先级排列如果报错不频繁、库里空闲内存偏少但还没完全耗尽优先直接修改参数扩大shared pool在10g以后用ALTER SYSTEM SET即可11g以上如果启用了SGA自动管理建议设置SGA_TARGET让Oracle自己平衡或者直接手动指定SHARED_POOL_SIZE作为下限值。如果报错非常密集业务几乎中断才考虑ALTER SYSTEM FLUSH SHARED_POOL应急。清完之后短期内确实能放行但需要在系统相对空闲的窗口做并提前告知业务方接下来几分钟可能有明显卡顿。如果连FLUSH SHARED_POOL都撑不住那就只能临时调大批参数比如把shared_pool直接给到6G、8G并观察内存是否够用。生产环境如果SGA设置已经贴近物理内存就得考虑砍掉一些其他组件的内存比如buffer cache。我这里特别强调一点FLUSH SHARED_POOL之后原来的所有游标都被清空大量SQL重新解析短时间内解析压力会极大甚至触发CPU过高。清完之后不要以为“问题解决了”这只是把地板上散落的文件扫到垃圾桶里过几小时垃圾又会重新堆满。清池之后紧接着要做的就是快速定位高版本游标、未绑定变量SQL和存储过程失效问题不然几个小时后同样报错会再次出现。3.2 正确调整shared_pool相关参数调参数之前要先确认数据库版本和当前内存管理模式。11g之后SGA自动管理用得多“记忆中的老方法”不一定全适用。-- 检查当前内存管理方式 SHOW PARAMETER sga_target; SHOW PARAMETER shared_pool_size; SHOW PARAMETER memory_target;如果memory_target和sga_target都非0说明用的是自动内存管理。这时直接ALTER SYSTEM SET shared_pool_sizexxG也是有效的Oracle会把它的值作为shared pool的下限值SGA_TARGET足够大时会自动扩展。如果只有sga_target非0可以设置shared_pool_size指定下限。如果都是0说明是手动管理调整后要重启实例才完全生效。以11g R2之后的系统为例常见的调整命令-- 注意SPFILE方式修改重启后依然生效 ALTER SYSTEM SET shared_pool_size4G SCOPESPFILE; ALTER SYSTEM SET shared_pool_reserved_size512M SCOPESPFILE;shared_pool_reserved_size这个参数容易被漏掉我建议按shared_pool的10%到25%来设。比如shared_pool给了4G保留区给512M是比较稳的组合。太小了特大型对象请求可能仍会失败太大了又挤压普通缓存空间得不偿失。不过要提醒一下调大参数的效果有一定滞后性。如果库里已经塞满了垃圾游标给再多内存也只是推迟报错时间。所以正确的顺序是先减垃圾再扩仓库。3.3 打不过就“钉住”关键大对象常驻共享池有类典型场景是系统里有个特别大的PL/SQL包比如几MB平时用得不多但每次执行都要重新加载加载时就需要在共享池里找一块很大而且连续的内存恰好当时没有就报ORA-04031。面对大对象有两个实用方案。一是拆分大对象把一个巨无霸存储过程拆成多个中小型存储过程降低单次加载所需内存块大小。二是使用DBMS_SHARED_POOL.KEEP把对象钉在共享池里让它不被LRU算法淘汰减少重复加载和碎片生成。-- 先把大对象keep进去SYS.STANDARD只是示例实际换成自己的包名 EXECUTE DBMS_SHARED_POOL.KEEP(SYS.STANDARD); -- 如果不知道完整对象名可以先查一下 SELECT owner, name, type, sharable_mem FROM v$db_object_cache WHERE sharable_mem 1048576 AND type IN (PACKAGE,PROCEDURE,FUNCTION) ORDER BY sharable_mem DESC;上面的SQL能帮你找出占用共享池超过1MB的数据库对象。对这些对象执行KEEP之后它们常驻共享池不会再被清出有利于减少碎片的产生。但要注意钉住的对象越多共享池可用空间就越少所以要挑真正频繁加载的大个头不能见一个钉一个。3.4 根治从SQL层面减少shared_pool压力这一节才是大部分系统真正的解药。ORA-04031的深度排查最终多数会落在SQL质量问题上。重点做三件事第一改写未绑定变量SQL。拿典型的三层架构系统来说很多代码是JDBC里直接拼字符串String sql SELECT * FROM orders WHERE order_id orderId;如果orderId变化很大这条SQL的文本就每条都不一样。正确写法是用PrepareStatement绑定变量String sql SELECT * FROM orders WHERE order_id ?;这个改动往往能减少库缓存里80%以上的垃圾游标。第二处理高版本游标。有些SQL即使文本完全相同也无法共享游标原因包括字符集不一致、会话参数不同、优化器模式不同等。优先找到version_count特别高的SQL然后固定它们的行为。比如用DBMS_STATS统一统计信息、设置OPTIMIZER_DYNAMIC_SAMPLING、或者给特定SQL加/* */提示并手工注册到SPM让所有会话走同一执行计划从而减少重复加载。第三谨慎使用cursor_sharing。这个参数在11g前经常被当作“一键救星”ALTER SYSTEM SET cursor_sharingFORCE SCOPEBOTH;它会自动把SQL里的字面量替换成系统绑定变量显著降低硬解析数量。但我要给个诚实的提醒它只是缓解手段副作用不小。强制替换后Oracle失去对绑定值分布的感知12c前绑定变量窥探失效尤为明显极容易导致本该走索引的SQL走了全表扫描。生产环境如果要用先做压测观察响应时间并且设置成FORCE后最好只在应急窗口使用问题排查完就改回EXACT。4. 长期预防与典型场景实录4.1 监控与告警体系搭建ORA-04031这种故障最怕的不是出一次而是反复出、出来之后业务中断半小时才被人发现。所以长期方案里监控优先级很高。可以搞一个定时巡检脚本每天把以下状态记录到表里-- 记录共享池空闲内存与保留池失败次数 INSERT INTO shared_pool_monitor(record_time, free_memory_mb, reserved_failures, version_count) SELECT SYSDATE, (SELECT ROUND(bytes/1024/1024,2) FROM v$sgastat WHERE poolshared pool AND namefree memory), (SELECT request_failures FROM v$shared_pool_reserved), (SELECT COUNT(*) FROM v$sqlarea WHERE version_count 10) FROM dual;然后用定时任务每天跑一次把free memory低于某个阈值或reserved_failures持续增长作为告警条件。很多公司会把这套数据接入Zabbix或Prometheus定时抓取再画趋势图。共享池空闲内存的趋势比单次峰值更有参考意义如果每天峰值时间都在往下走说明垃圾SQL还在持续堆积迟早要出事。另外一个预防重点是发布流程管控。大型发布或DDL变更前评估一下是否会导致大量对象失效重编译。比如一堆存储过程引用了同一个表发布时还要重建这个表那这些存储过程会全部INVALID。等业务并发一上来它们同时被加载就直接把共享池冲爆。遇到这种窗口提前规划好顺序、避开业务高峰并且准备一个低峰期“预热”计划。4.2 一个EBS环境WIP非标工单引发ORA-04031的真实排查记录这里分享一个我印象特别深的案例。某制造企业的Oracle EBS系统每个月末跑WIP模块的完工和发料请求时告警日志里准会出现几十条ORA-04031。车间反馈系统特别慢工单操作页面经常报错。当时第一反应也是看共享池v$sgastat查下来free memory确实只剩几MB于是把shared_pool_size从2G一路加到6G结果支撑了半个月下个月末又崩。后来用v$sqlarea按version_count排序发现大量SQL文本前面都是一样的后面跟着不同的工单号、装配件号、数量参数典型的就是不绑定变量的动态SQL拼接。进一步排查发现这个EBS环境的“非标工单”数量特别多。非标工单不关联物料清单和工艺路线处理逻辑更多依赖动态拼接的SQL来组装数据。车间月结期间大量并发提交非标工单的“完工”和“发料”操作每条SQL都带不同的参数库缓存里瞬间多出几万条相似的游标共享池就彻底扛不住了。这个问题的处理分了三个层面短期临时加大shared_pool_size并把一天中耗时最长的几个并发请求并发管理器线程错峰调度减少同时压在共享池上的压力。中期与EBS开发顾问合作把WIP接口表数据同步、工单处理中高频使用的SQL改为绑定变量。有些是标准功能不好改就在数据库层面用cursor_sharing做针对性测试只对几个特定应用账号设置。长期对DB层面做游标监控新增告警和报表把月末集中处理改为提前/分散处理后续几个月再没有出现ORA-04031。这个案例给我最大的启发是EBS系统里ORA-04031基本不是数据库单独的问题它反映的是应用层SQL特征和并发模型的问题。如果你只是DBA不要让排查止步于数据库参数要主动拉上应用顾问去查SQL生成逻辑。4.3 ORA-04031排查速查表处理完这么多轮故障我把经验整理成一个速查表方便遇到问题时快速对照。故障特征可能的根因快速处置根治方案free memory很低报错频繁共享池总容量不足调大shared_pool_size减少未绑定变量SQL压缩游标数量free memory尚可但报错字节数很大碎片化、连续大块不足增大shared_pool_reserved_size必要时临时FLUSH大对象拆分、关键对象KEEP入池报错注释带kglsim游标搜索时内存不足查询高版本游标kill对应会话SQL改写和统一执行计划报错期间有大量DDL/TRUNCATE对象失效引发批量重载重编译INVALID对象调整发布流程错峰运行报错集中在某一业务模块该模块SQL拼接严重临时分开并发请求将动态SQL改为绑定变量reserve request_failures持续增长保留池长期紧张调大shared_pool_reserved_size排查大对象和超大PL/SQL块还有一个容易被忽略的问题共享池组件直接分配到SGA之外的情况。有些环境把shared_pool_size设得特别大导致SGA总大小接近物理内存上限操作系统的分页成为新瓶颈。表面看ORA-04031不再出现了但系统整体变慢。所以调大内存的同时要留出足够OS缓存别把内存全吃干。排查时建议按这个顺序推进看告警日志 → 查v$sgastat和v$shared_pool_reserved→ 查高版本游标和未绑定变量SQL → 查对象失效情况 → 再决定是调参、清池还是改SQL。每走一步记下现象不要上来就改参数否则很容易把现场破坏后面分析起来更难。在我目前处理过的ORA-04031故障里真正靠调大内存一劳永逸的案例很少绝大多数还是应用侧游标泛滥、对象重载和并发模型的问题。如果你也遇到这个错误我建议花最多时间在SQL和对象层排查上调参只是应急而已。最后分享一个小习惯我每处理完一次ORA-04031都会把当时的告警日志片段、v$sqlarea查询结果和最终处置方案存到一个按日期命名的归档目录里。下次再有人来问“共享池又报04031了”翻出历史记录直接对比是不是同一个业务模块或同一批SQL复发排查速度会快很多。