1. 引言当 dbms_shared_pool.purge 遇上清不掉的游标前段时间线上一个核心交易库出了个怪现象。某个 SQL 因为执行计划走偏导致响应时间从 10ms 飙到 3 秒DBA 团队按常规思路打算把这条 SQL 的游标从共享池里 purge 掉让它重新硬解析生成新的执行计划。命令确实执行成功了dbms_shared_pool.purge返回值也显示正常可离谱的事情来了应用侧通过 JDBC 拿到的那个 cursor照旧在执行旧的执行计划怎么都清不掉。当时团队里几个同事围着这个问题查了半天最后发现根因涉及 Oracle 内部对 cursor 的引用计数机制、客户端游标状态与共享池对象生命周期的耦合关系还包括purge本身的作用边界。这篇文章就围绕这个场景展开把dbms_shared_pool.purge在 cursor 使用期间清不掉的原因、适用边界、以及正确清理共享池对象的方法一次讲透。我尽量用通俗的方式把 Oracle 的库缓存Library Cache管理机制讲清楚同时给出可以直接复现和参考的实操步骤。如果你是 Oracle DBA、性能优化工程师、或者负责核心系统 SQL 治理的开发人员这篇内容应该能帮你在下次遇到类似问题时少走弯路。开门见山说结论dbms_shared_pool.purge清不掉 cursor不是命令写错了也不是权限不够而是 Oracle 对正在被会话引用的游标对象设计了保护机制。客户端持有的 cursor 不关闭共享池里对应的父游标、子游标就可能被pin住purge操作对着一个被 pin 的堆对象执行Oracle 默认只做标记删除不会真正把内存释放掉于是表现为清理不生效。2. 游标生命周期与 purge 的清理边界2.1 共享 SQL 区域和游标状态流转要理解为什么 purge 清不掉正在使用的 cursor得先搞清楚 Oracle 里游标的存储和生命周期。当一个 SQL 第一次被提交到数据库时Oracle 会经历硬解析Hard Parse过程在共享池的库缓存Library Cache中分配内存对象父游标Parent Cursor和子游标Child Cursor。父游标以 SQL 文本的哈希值为键存储子游标则保存了该 SQL 对应的执行计划、解析树、绑定变量定义等具体执行所需的全部元数据。客户端会话执行 SQL 时会申请一个客户端游标Session Cursor它是一块私有的运行时内存区域对应服务端的私有 SQL 区Private SQL Area。这个客户端游标在自己的生命周期里会在以下状态间流转OPEN游标已打开解析完成处于可执行状态。BOUND绑定变量已传入游标处于可执行状态。EXECUTED游标已执行正在读取结果集。FETCHED部分结果集已返回给客户端。CLOSED游标关闭客户端游标资源释放。注意一点客户端游标关闭并不意味着共享池里的父游标/子游标立即被清除它们只是从被引用状态变成可淘汰状态。共享池中的游标对象是否被清理取决于 LRU 算法、共享池空闲空间、以及是否有会话仍持有对该游标的引用。2.2 purge 操作的底层原理dbms_shared_pool.purge是一个内部工具包Oracle 官方最初提供它的本意是在不重启实例的情况下手工将某个指定对象从共享池中驱逐出去。它的实现本质是对库缓存中某个对象的 Heap 执行内存释放操作。这个包的使用姿势很典型先在v$db_object_cache或v$sqlarea里查到目标 SQL 的 address 和 hash_value然后调用 purge。但这里的关键机制是purge 的真正含义是断开共享池对象与库缓存管理结构的关联它不会强制回收正在被 pin 住的内存对象。Oracle 的库缓存管理结构里每个游标对象都有对应的引用计数Reference Count / Lock Count。SQL 执行期间执行游标的会话会对该 child cursor 持有锁Lock或固定引用Pin。purge执行成功后该对象会从正常的 LRU 链中摘除对应 SQL 的v$sqlarea记录会消失但如果此时有会话正在执行或者持有该游标实际的内存堆并不会被释放正在执行的 SQL 照常运行。这里的底层逻辑有点像操作系统里的文件删除操作一个文件被进程打开着你执行删除命令目录项被移除但进程仍然可以通过已打开的文件描述符继续读写直到进程关闭文件描述符文件占用的磁盘块才真正释放。Oracle 的 purge 对 cursor 的处理同理purge 删的是目录项不是正在被打开的文件内容。2.3 为什么正在使用时清不掉把上面两点合在一起就能解释最开始的线上故障了。那条 SQL 走偏执行计划后应用侧连接池中已经有很多会话执行过这条 SQL。由于 Oracle 的游标共享特性这些会话在执行时使用的是同一个子游标对象。此时调用 purge 清理这条 SQL 的共享游标会发生这样的过程purge 将该子游标从 library cache 的 bucket 链和 LRU 链中摘除。子游标对应的 parent cursor 标记被删除。但因为连接池里的会话仍然持有指向该子游标的引用这些引用计数没有归零所以内存堆不会释放。已经持有该游标的会话继续使用旧游标执行 SQL表现为新执行计划不生效。新到达的会话由于在库缓存中找不到该 SQL 的记录会触发硬解析生成新的子游标——但旧会话仍然占用旧的执行计划。最终的现象就是同一个 SQL 在 AWR 里出现两个版本一部分会话走新计划一部分会话走旧计划。这个时候如果不重启应用或刷新连接池旧计划的 SQL 会一直存在直到持有旧游标的会话关闭。2.4 purge 的适用边界那是不是说 purge 这个工具就是废物没用不是。它针对的是没有被 pin 住的空闲游标对象。典型场景是某个 SQL 已经不再被任何会话使用但因为共享池内存压力不够或者因为 SQL 版本过多导致 library cache 中残留了大量废弃对象你想快速把它们清掉腾出内存空间。这时候 purge 会非常高效效果立竿见影。还有一种场景某个存储过程、函数、包对象因为代码变更需要重新编译你可以把旧版本的对象从共享池中 purge 掉触发下次调用时重新加载新版本。但如果目标是让运行中的业务立即切换到新的执行计划purge 不是正确手段。正确处理方式应该是刷新游标alter system flush shared_pool太重不建议、使用 SQL Plan ManagementSPM进行计划基线切换、或者从应用侧断开并重建会话。3. 实操踩坑实录从清理命令到发现问题全过程3.1 首次清理操作和现象观察我把当时的排查过程还原出来给各位做个参考。那个走偏计划的 SQL 文本里有一段非常长的 IN 列表绑定变量数量有 200 多个SQL_ID 是0f7s2k9x1ax4p。当时我们通过下面的方式确认了它在共享池中的位置select address, hash_value, sql_id, executions, parse_calls, loads, invalidations, version_count from v$sqlarea where sql_id 0f7s2k9x1ax4p;返回结果显示该 SQL 在库缓存中存在了很长时间loads为 1invalidations为 0表示从第一次硬解析后一直没有失效过。执行计划是 3 天前生成的对应一个走索引的短期行为后来因为数据分布变化这个计划已经明显变差。接下来我们按标准流程执行了清理begin sys.dbms_shared_pool.purge(0000000B6C45A8E0, 2394826149, C); end; /注意这里的格式第一个参数是 address 加逗号加 hash_value第二个参数C表示清理的是 cursor 对象。执行过程中没有报任何错误v$sqlarea里也确实查不到这条 SQL 了。重点来了但应用监控平台显示这条 SQL 的平均响应时间没有降下来新的执行计划也没有出现。我再用同一个 SQL_ID 去查v$sql时发现一条相同 SQL 文本的新记录已经在库缓存里了loads为 1但executions很小还没怎么执行。AWR 报告里的 Top SQL 同时出现了两个 SQL_ID 不同、但 SQL 文本几乎完全一致的记录。3.2 定位残留游标的排查手段这时候我们需要找出来到底是谁还在持有着旧游标。Oracle 里有两个视图派上用场v$open_cursor和v$session。select sid, user_name, sql_id, sql_text, cursor_type, status from v$open_cursor where sql_id 0f7s2k9x1ax4p order by sid;这个视图记录的是当前所有会话已经打开的客户端游标信息。注意v$open_cursor展示的是游标是否处于打开状态不是共享池对象是否存在。如果这条 SQL 还在v$open_cursor里说明有会话还没有关闭这个游标它就是旧计划残留的直接原因。我们的查询结果证实了猜测有 20 多个 session 的open cursor列表里都含这个 SQL_ID状态都是OPEN说明连接池里的这些长连接在之前的业务处理中执行过这条 SQL 后游标一直没被关闭Oracle 的会话游标缓存机制会导致游标被缓存在会话里不会立即 close。这里展开讲一下Java 应用使用 JDBC 时如果开启了oracle.jdbc.implicitStatementCacheSize或者使用连接池如 HikariCP、Druid一个物理连接上通常会缓存多条 SQL 的游标游标不会在使用完后立刻关闭而是被保存在连接会话的缓存里等下次相同 SQL 再过来时直接复用。这就是为什么 purge 之后旧游标仍然赖在共享池里不走的直接原因。3.3 最终解决路径确认问题根因后我们采取了两个动作应用侧操作通过连接池管理工具Druid 的removeAbandoned机制和 HikariCP 的connectionTestQuery配合把存量连接全部重建。这个过程本质上就是让所有会话关闭释放游标缓存切断对旧子游标的引用。数据库侧操作等服务端所有引用计数归零后再次确认v$sqlarea中旧 SQL 已不存在然后通过dbms_shared_pool.purge清理新生成的错误执行计划对应的游标如果有的话再通过 SPM 固定新的正确执行计划。切换完成后所有连接重新执行那条 SQL 时由于旧子游标已经被 purge只能硬解析生成的新子游标引用了我们通过 SPM 固定好的执行计划。响应时间恢复正常不再出现新旧计划并存的混乱局面。4. 核心代码实现细节与参数解析4.1 一个完整的游标诊断脚本为了让大家将来排查类似问题时效率更高我把这次实战中用的诊断脚本整理成了一个完整版本。它可以输出指定 SQL_ID 相关的所有父游标、子游标、打开游标的会话信息、以及游标的引用状态-- 1. 查看指定 SQL 在库缓存中的父游标和子游标情况 select sql_id, child_number, plan_hash_value, executions, parse_calls, loads, invalidations, is_obsolete, is_shareable, last_load_time, address, hash_value from v$sql where sql_id sql_id order by child_number; -- 2. 查看打开游标的会话重点谁在持有游标 select s.sid, s.serial#, s.username, s.program, s.module, c.sql_id, c.cursor_type, c.status, c.sql_text from v$open_cursor c join v$session s on c.sid s.sid where c.sql_id sql_id order by s.sid; -- 3. 查看游标对应的执行计划状态 select sql_id, plan_hash_value, sql_plan_baseline, sql_profile, outline_category from v$sql where sql_id sql_id; -- 4. 查看相关 cursor 对象的 heap 信息 select kglnaobj as object_name, kglobt03 as heap_size, kglobt04 as heap_used, kglobt05 as heap_allocated from x$kglob where kglnaobj like %sql_fragment% order by kglobt04 desc;4.2 参数选择与使用陷阱在使用上述脚本和 purge 命令时有几个细节需要注意v$sqlarea和v$sql的区别。v$sqlarea是按 SQL 文本聚合的一条 SQL 文本只显示一行version_count表示子游标数量。v$sql是每个子游标一行。用v$sqlarea查到的address和hash_value是父游标的地址和哈希值这样调用 purge 时会连同所有子游标一起标记删除。purge 的第二个参数大小写敏感。C表示 cursorP表示 procedure/function/packageT表示 table 类型对象。如果你传小写的cOracle 不会报错但可能无法正确识别类型导致清理失败。address 和 hash_value 的获取格式。使用dbms_shared_pool.purge时第一个参数是address , hash_value中间不要加空格。address 不带0x前缀。如下-- 从 v$sqlarea 查询 select address, hash_value from v$sqlarea where sql_id 0f7s2k9x1ax4p; -- 假设 address 0000000B6C45A8E0, hash_value 2394826149 -- 那么 purge 命令写法为 exec sys.dbms_shared_pool.purge(0000000B6C45A8E0, 2394826149, C);警惕is_obsolete标记。子游标因为某些原因如alter system flush shared_pool、DDL 导致游标失效、绑定变量长度变化导致新子游标生成被标记为 obsolete 后Oracle 会在未来某个时间点自动清理它。但这种游标仍然可能占用大量共享池内存特别是在 OLTP 系统里频繁出现子游标版本数膨胀时。这种情况下 use purge 手动清理很合适。is_shareable字段的参考价值。is_shareable N表示该游标可以被其它 SQL 共享通常意味着它已经被标记为不可复用但还是占着空间。这类游标用 purge 清理非常合适。4.3 如何确认 purge 真正生效purge 操作执行成功但引用计数不为零时结果是残留。怎么判断是否真正清理干净我总结了两个参考指标指标一v$sqlarea中是否已经查不到记录。正常情况 purge 成功后v$sqlarea 里对应的行会消失。如果没过多久又出现相同 SQL_ID说明有新会话重新硬解析了这条 SQL产生了一个全新的游标——这是正常的。指标二x$kglob中是否还存在对象。更底层的方式是查x$kglob表如果对象已经不在这个内部结构里说明库缓存层面的管理结构已经被移除。但注意这只代表管理结构被清理不代表内存堆已经归还。select kglhdadr, kglnaobj, kglobt03, kglobt04, kglobt05 from x$kglob where kglnaobj select * from t where id :1;如果结果为空说明这个对象已经从库缓存的管理结构中完全摘除。5. 现场遇到的问题和排查技巧总结5.1 典型问题速查表结合我自己的经验以及团队里其他 DBA 遇到过的场景把dbms_shared_pool.purge相关的典型问题整理成一张表现象表现根因定位解决思路purge 执行成功但 SQL 仍出现在 v$sql/v$sqlarea有会话仍然 pin 住该游标引用计数不为零找到持有游标的会话关闭游标或重建连接purge 后 SQL 响应时间没有变化游标确实被清理但新硬解析生成的执行计划仍然是旧的使用 SPM/SQL Profile 固定正确执行计划后再 purgepurge 报错ORA-20000对象类型错误或权限不足检查第二个参数是否正确确认是否有SYS权限purge 报错ORA-06550语法格式问题检查 address 和 hash_value 的格式不要加0x不要有空格相同 SQL 突然大量新增子游标purge 导致游标失效同时应用侧并发执行触发硬解析确认 purge 后在业务低峰期操作同时检查游标共享参数v$sql 中出现大量is_obsoleteY的游标频繁 DDL、绑定变量长度变化或 cursor_sharing 设置不合理结合具体触发原因优化必要时使用 dbms_shared_pool.purge 清理应用侧 ORA-01002: fetch out of sequence游标被 purge 后应用继续 fetch 已失效游标应用侧需要做异常捕获和重试逻辑数据库侧避免对正在使用中的 cursor 做 purge5.2 那个最容易踩的坑purge 和 flush shared_pool 混用有些 DBA 在处理游标问题时嫌dbms_shared_pool.purge麻烦直接一步到位执行alter system flush shared_pool。这个操作会把共享池里所有可清空的对象全部清除副作用极大所有 SQL 都需要重新硬解析短时间内库缓存命中率暴跌CPU 飙高。正在执行的 SQL 不受影响但新 SQL 全部走硬解析路径。大量并发硬解析可能导致 library cache 锁竞争加剧甚至引发ORA-04031。我见过某次故障处理中有人先 flush shared_pool 再尝试固定执行计划结果共享池在高峰期被冲垮系统更不稳定。正确做法是能用dbms_shared_pool.purge精确清理的不要用 flush。如果为了安全要 flush也应该在业务低峰期或者变更窗口内操作。还有一个经常被忽略的细节alter system flush shared_pool不会清理正在使用的游标。已经打开的游标不会立即失效要等游标关闭后才会被清理。所以对于连接池里持有着旧游标的会话flush 操作也同样无能为力最终还是得靠应用侧重建连接或主动关闭游标。5.3 连接池游标缓存带来的隐形负担这里再多说一句很多系统设计时没有考虑到连接池的游标缓存问题。默认情况下JDBC 驱动对每个连接会有一个隐式游标缓存Implicit Statement Caching大小由oracle.jdbc.implicitStatementCacheSize决定。每个物理连接上缓存的游标数量越多持有共享池对象的引用就越久。一个普遍的调优建议是不要盲目调大连接的游标缓存大小。对于 SQL 种类特别多比如拼接条件巨多的系统游标缓存命中率往往不高缓存了反而占用服务端游标资源。v$open_cursor中cursor_type为SESSION_CACHED的游标就属于这类被缓存的游标。如果你在排查 SQL 游标残留问题时发现v$open_cursor里有大量SESSION_CACHED状态为OPEN的游标优先考虑从连接池层面优化例如调小连接池的最大连接数减少物理连接总数。调小implicitStatementCacheSize或关闭隐式游标缓存。使用连接池的定期连接重建功能让旧游标周期性释放。6. 使用 dbms_shared_pool.purge 的几个实操经验6.1 安全 purge 的最佳实践顺序根据多次实战经验我总结了一套安全清理共享池对象的标准操作顺序第一步确认清理目标。先用v$sqlarea核对 SQL_ID、SQL 文本、version_count、loads、invalidations 信息确认它确实是你想清理的对象。注意如果一个游标的loads一直为 1而executions很大说明它一直被复用非常健康如果它执行计划有问题重点不是清理游标而是更新统计信息、加 hint、或通过 SPM 修正计划。第二步评估残留风险。查询v$open_cursor确认当前有多少个会话打开了该游标评估影响范围。如果影响面很大几十个活跃会话都在用建议先和应用团队沟通规划连接重建窗口。第三步执行 purge。-- 先找到目标 select address, hash_value, sql_id from v$sqlarea where sql_id sql_id; -- 执行清理 exec sys.dbms_shared_pool.purge(address, hash_value, C);第四步确认清理结果。分别查询v$sqlarea、v$sql、x$kglob、v$open_cursor确认清理结果判断是否还有会话在持有旧游标。第五步监控系统状态。purge 完成后的 15 分钟内重点关注v$sysstat中parse count (hard)、library cache hit ratio、shared pool free memory三个指标。如果硬解析数量急剧上升且共享池内存持续紧张考虑是否有并发的 SQL 风暴在触发。6.2 和 SPM 配合的正确姿势我强烈建议把dbms_shared_pool.purge和 SQL Plan Management 结合起来使用而不是孤立地清理游标。当一条 SQL 的执行计划因为统计信息变化或者其他原因走偏时正确做法是先从 AWR 或 dba_hist_sqlstat 里找到过去正常时期的 plan_hash_value。使用dbms_sqltune.create_sql_plan_baseline手动创建基线。启用 SPM 演进将正确的执行计划设置为首选计划。再做游标清理purge 掉当前走偏的游标让新硬解析直接使用基线计划。这样就能实现精准的 SQL 级性能修复而不影响其他 SQL 的共享池命中。这里有一个实操小技巧如果目标 SQL 已经在连接池中被大量会话持有purge 后短时间内新旧子游标可能并存这时可以利用 SPM 的自动演进功能把正确计划的基线标记为 ACCEPTED这样即使旧游标还存在新执行的 SQL 也会优先选择正确的计划。6.3 关于 Oracle 19c 和 21c 的行为差异最后提醒一下不同版本下 purge 对 cursor 的处理行为有细微差异。在 Oracle 19c 及之前版本中dbms_shared_pool.purge对正在执行的游标基本就是标记删除处理。但在 Oracle 21c 及之后的版本中Oracle 对库缓存做了不少重构引入了一些新的内存管理特性purge 的行为对引用计数的敏感度变得更高某些场景下甚至需要对象完全空闲才能成功清理。另外Oracle RAC 环境下需要注意dbms_shared_pool.purge只作用于当前节点不会广播到其他节点。如果你想在所有节点上都清理同一个游标需要分别在每个实例上执行一次。这个坑我见过好几次在单实例上清理完后发现其他节点的 SQL 还在误以为 purge 失效了。-- RAC 环境下查询各个节点的游标情况 select inst_id, sql_id, child_number, executions, loads, parse_calls from gv$sql where sql_id sql_id order by inst_id, child_number;7. 写在最后的个人体会从那次线上故障到现在我在处理游标清理问题上也积累了不少新的认识。最想和大家分享的一点是不要把一个工具神化也不要因为它的一次失灵就否定它。dbms_shared_pool.purge是一个精准的手术刀但它切的是静态的对象不是动态的连接。当业务系统通过连接池大量复用时游标的生命周期早就不纯粹由数据库侧控制了它和应用侧的长连接绑定在一起。所以如果你问我以后遇到执行计划走偏会怎么处理我会直说先上 SPM 固定正确计划再考虑清理游标。如果应用侧连接池支持优雅重启配合一个低峰期的连接重建多数游标问题都能在十几分钟内解决。dbms_shared_pool.purge更适合的是那种确定已经无人使用、但还霸占着共享池空间的死对象这种场景下它是当之无愧的一把好手。
Oracle游标清理实战：dbms_shared_pool.purge为何清不掉正在使用的游标？
1. 引言当 dbms_shared_pool.purge 遇上清不掉的游标前段时间线上一个核心交易库出了个怪现象。某个 SQL 因为执行计划走偏导致响应时间从 10ms 飙到 3 秒DBA 团队按常规思路打算把这条 SQL 的游标从共享池里 purge 掉让它重新硬解析生成新的执行计划。命令确实执行成功了dbms_shared_pool.purge返回值也显示正常可离谱的事情来了应用侧通过 JDBC 拿到的那个 cursor照旧在执行旧的执行计划怎么都清不掉。当时团队里几个同事围着这个问题查了半天最后发现根因涉及 Oracle 内部对 cursor 的引用计数机制、客户端游标状态与共享池对象生命周期的耦合关系还包括purge本身的作用边界。这篇文章就围绕这个场景展开把dbms_shared_pool.purge在 cursor 使用期间清不掉的原因、适用边界、以及正确清理共享池对象的方法一次讲透。我尽量用通俗的方式把 Oracle 的库缓存Library Cache管理机制讲清楚同时给出可以直接复现和参考的实操步骤。如果你是 Oracle DBA、性能优化工程师、或者负责核心系统 SQL 治理的开发人员这篇内容应该能帮你在下次遇到类似问题时少走弯路。开门见山说结论dbms_shared_pool.purge清不掉 cursor不是命令写错了也不是权限不够而是 Oracle 对正在被会话引用的游标对象设计了保护机制。客户端持有的 cursor 不关闭共享池里对应的父游标、子游标就可能被pin住purge操作对着一个被 pin 的堆对象执行Oracle 默认只做标记删除不会真正把内存释放掉于是表现为清理不生效。2. 游标生命周期与 purge 的清理边界2.1 共享 SQL 区域和游标状态流转要理解为什么 purge 清不掉正在使用的 cursor得先搞清楚 Oracle 里游标的存储和生命周期。当一个 SQL 第一次被提交到数据库时Oracle 会经历硬解析Hard Parse过程在共享池的库缓存Library Cache中分配内存对象父游标Parent Cursor和子游标Child Cursor。父游标以 SQL 文本的哈希值为键存储子游标则保存了该 SQL 对应的执行计划、解析树、绑定变量定义等具体执行所需的全部元数据。客户端会话执行 SQL 时会申请一个客户端游标Session Cursor它是一块私有的运行时内存区域对应服务端的私有 SQL 区Private SQL Area。这个客户端游标在自己的生命周期里会在以下状态间流转OPEN游标已打开解析完成处于可执行状态。BOUND绑定变量已传入游标处于可执行状态。EXECUTED游标已执行正在读取结果集。FETCHED部分结果集已返回给客户端。CLOSED游标关闭客户端游标资源释放。注意一点客户端游标关闭并不意味着共享池里的父游标/子游标立即被清除它们只是从被引用状态变成可淘汰状态。共享池中的游标对象是否被清理取决于 LRU 算法、共享池空闲空间、以及是否有会话仍持有对该游标的引用。2.2 purge 操作的底层原理dbms_shared_pool.purge是一个内部工具包Oracle 官方最初提供它的本意是在不重启实例的情况下手工将某个指定对象从共享池中驱逐出去。它的实现本质是对库缓存中某个对象的 Heap 执行内存释放操作。这个包的使用姿势很典型先在v$db_object_cache或v$sqlarea里查到目标 SQL 的 address 和 hash_value然后调用 purge。但这里的关键机制是purge 的真正含义是断开共享池对象与库缓存管理结构的关联它不会强制回收正在被 pin 住的内存对象。Oracle 的库缓存管理结构里每个游标对象都有对应的引用计数Reference Count / Lock Count。SQL 执行期间执行游标的会话会对该 child cursor 持有锁Lock或固定引用Pin。purge执行成功后该对象会从正常的 LRU 链中摘除对应 SQL 的v$sqlarea记录会消失但如果此时有会话正在执行或者持有该游标实际的内存堆并不会被释放正在执行的 SQL 照常运行。这里的底层逻辑有点像操作系统里的文件删除操作一个文件被进程打开着你执行删除命令目录项被移除但进程仍然可以通过已打开的文件描述符继续读写直到进程关闭文件描述符文件占用的磁盘块才真正释放。Oracle 的 purge 对 cursor 的处理同理purge 删的是目录项不是正在被打开的文件内容。2.3 为什么正在使用时清不掉把上面两点合在一起就能解释最开始的线上故障了。那条 SQL 走偏执行计划后应用侧连接池中已经有很多会话执行过这条 SQL。由于 Oracle 的游标共享特性这些会话在执行时使用的是同一个子游标对象。此时调用 purge 清理这条 SQL 的共享游标会发生这样的过程purge 将该子游标从 library cache 的 bucket 链和 LRU 链中摘除。子游标对应的 parent cursor 标记被删除。但因为连接池里的会话仍然持有指向该子游标的引用这些引用计数没有归零所以内存堆不会释放。已经持有该游标的会话继续使用旧游标执行 SQL表现为新执行计划不生效。新到达的会话由于在库缓存中找不到该 SQL 的记录会触发硬解析生成新的子游标——但旧会话仍然占用旧的执行计划。最终的现象就是同一个 SQL 在 AWR 里出现两个版本一部分会话走新计划一部分会话走旧计划。这个时候如果不重启应用或刷新连接池旧计划的 SQL 会一直存在直到持有旧游标的会话关闭。2.4 purge 的适用边界那是不是说 purge 这个工具就是废物没用不是。它针对的是没有被 pin 住的空闲游标对象。典型场景是某个 SQL 已经不再被任何会话使用但因为共享池内存压力不够或者因为 SQL 版本过多导致 library cache 中残留了大量废弃对象你想快速把它们清掉腾出内存空间。这时候 purge 会非常高效效果立竿见影。还有一种场景某个存储过程、函数、包对象因为代码变更需要重新编译你可以把旧版本的对象从共享池中 purge 掉触发下次调用时重新加载新版本。但如果目标是让运行中的业务立即切换到新的执行计划purge 不是正确手段。正确处理方式应该是刷新游标alter system flush shared_pool太重不建议、使用 SQL Plan ManagementSPM进行计划基线切换、或者从应用侧断开并重建会话。3. 实操踩坑实录从清理命令到发现问题全过程3.1 首次清理操作和现象观察我把当时的排查过程还原出来给各位做个参考。那个走偏计划的 SQL 文本里有一段非常长的 IN 列表绑定变量数量有 200 多个SQL_ID 是0f7s2k9x1ax4p。当时我们通过下面的方式确认了它在共享池中的位置select address, hash_value, sql_id, executions, parse_calls, loads, invalidations, version_count from v$sqlarea where sql_id 0f7s2k9x1ax4p;返回结果显示该 SQL 在库缓存中存在了很长时间loads为 1invalidations为 0表示从第一次硬解析后一直没有失效过。执行计划是 3 天前生成的对应一个走索引的短期行为后来因为数据分布变化这个计划已经明显变差。接下来我们按标准流程执行了清理begin sys.dbms_shared_pool.purge(0000000B6C45A8E0, 2394826149, C); end; /注意这里的格式第一个参数是 address 加逗号加 hash_value第二个参数C表示清理的是 cursor 对象。执行过程中没有报任何错误v$sqlarea里也确实查不到这条 SQL 了。重点来了但应用监控平台显示这条 SQL 的平均响应时间没有降下来新的执行计划也没有出现。我再用同一个 SQL_ID 去查v$sql时发现一条相同 SQL 文本的新记录已经在库缓存里了loads为 1但executions很小还没怎么执行。AWR 报告里的 Top SQL 同时出现了两个 SQL_ID 不同、但 SQL 文本几乎完全一致的记录。3.2 定位残留游标的排查手段这时候我们需要找出来到底是谁还在持有着旧游标。Oracle 里有两个视图派上用场v$open_cursor和v$session。select sid, user_name, sql_id, sql_text, cursor_type, status from v$open_cursor where sql_id 0f7s2k9x1ax4p order by sid;这个视图记录的是当前所有会话已经打开的客户端游标信息。注意v$open_cursor展示的是游标是否处于打开状态不是共享池对象是否存在。如果这条 SQL 还在v$open_cursor里说明有会话还没有关闭这个游标它就是旧计划残留的直接原因。我们的查询结果证实了猜测有 20 多个 session 的open cursor列表里都含这个 SQL_ID状态都是OPEN说明连接池里的这些长连接在之前的业务处理中执行过这条 SQL 后游标一直没被关闭Oracle 的会话游标缓存机制会导致游标被缓存在会话里不会立即 close。这里展开讲一下Java 应用使用 JDBC 时如果开启了oracle.jdbc.implicitStatementCacheSize或者使用连接池如 HikariCP、Druid一个物理连接上通常会缓存多条 SQL 的游标游标不会在使用完后立刻关闭而是被保存在连接会话的缓存里等下次相同 SQL 再过来时直接复用。这就是为什么 purge 之后旧游标仍然赖在共享池里不走的直接原因。3.3 最终解决路径确认问题根因后我们采取了两个动作应用侧操作通过连接池管理工具Druid 的removeAbandoned机制和 HikariCP 的connectionTestQuery配合把存量连接全部重建。这个过程本质上就是让所有会话关闭释放游标缓存切断对旧子游标的引用。数据库侧操作等服务端所有引用计数归零后再次确认v$sqlarea中旧 SQL 已不存在然后通过dbms_shared_pool.purge清理新生成的错误执行计划对应的游标如果有的话再通过 SPM 固定新的正确执行计划。切换完成后所有连接重新执行那条 SQL 时由于旧子游标已经被 purge只能硬解析生成的新子游标引用了我们通过 SPM 固定好的执行计划。响应时间恢复正常不再出现新旧计划并存的混乱局面。4. 核心代码实现细节与参数解析4.1 一个完整的游标诊断脚本为了让大家将来排查类似问题时效率更高我把这次实战中用的诊断脚本整理成了一个完整版本。它可以输出指定 SQL_ID 相关的所有父游标、子游标、打开游标的会话信息、以及游标的引用状态-- 1. 查看指定 SQL 在库缓存中的父游标和子游标情况 select sql_id, child_number, plan_hash_value, executions, parse_calls, loads, invalidations, is_obsolete, is_shareable, last_load_time, address, hash_value from v$sql where sql_id sql_id order by child_number; -- 2. 查看打开游标的会话重点谁在持有游标 select s.sid, s.serial#, s.username, s.program, s.module, c.sql_id, c.cursor_type, c.status, c.sql_text from v$open_cursor c join v$session s on c.sid s.sid where c.sql_id sql_id order by s.sid; -- 3. 查看游标对应的执行计划状态 select sql_id, plan_hash_value, sql_plan_baseline, sql_profile, outline_category from v$sql where sql_id sql_id; -- 4. 查看相关 cursor 对象的 heap 信息 select kglnaobj as object_name, kglobt03 as heap_size, kglobt04 as heap_used, kglobt05 as heap_allocated from x$kglob where kglnaobj like %sql_fragment% order by kglobt04 desc;4.2 参数选择与使用陷阱在使用上述脚本和 purge 命令时有几个细节需要注意v$sqlarea和v$sql的区别。v$sqlarea是按 SQL 文本聚合的一条 SQL 文本只显示一行version_count表示子游标数量。v$sql是每个子游标一行。用v$sqlarea查到的address和hash_value是父游标的地址和哈希值这样调用 purge 时会连同所有子游标一起标记删除。purge 的第二个参数大小写敏感。C表示 cursorP表示 procedure/function/packageT表示 table 类型对象。如果你传小写的cOracle 不会报错但可能无法正确识别类型导致清理失败。address 和 hash_value 的获取格式。使用dbms_shared_pool.purge时第一个参数是address , hash_value中间不要加空格。address 不带0x前缀。如下-- 从 v$sqlarea 查询 select address, hash_value from v$sqlarea where sql_id 0f7s2k9x1ax4p; -- 假设 address 0000000B6C45A8E0, hash_value 2394826149 -- 那么 purge 命令写法为 exec sys.dbms_shared_pool.purge(0000000B6C45A8E0, 2394826149, C);警惕is_obsolete标记。子游标因为某些原因如alter system flush shared_pool、DDL 导致游标失效、绑定变量长度变化导致新子游标生成被标记为 obsolete 后Oracle 会在未来某个时间点自动清理它。但这种游标仍然可能占用大量共享池内存特别是在 OLTP 系统里频繁出现子游标版本数膨胀时。这种情况下 use purge 手动清理很合适。is_shareable字段的参考价值。is_shareable N表示该游标可以被其它 SQL 共享通常意味着它已经被标记为不可复用但还是占着空间。这类游标用 purge 清理非常合适。4.3 如何确认 purge 真正生效purge 操作执行成功但引用计数不为零时结果是残留。怎么判断是否真正清理干净我总结了两个参考指标指标一v$sqlarea中是否已经查不到记录。正常情况 purge 成功后v$sqlarea 里对应的行会消失。如果没过多久又出现相同 SQL_ID说明有新会话重新硬解析了这条 SQL产生了一个全新的游标——这是正常的。指标二x$kglob中是否还存在对象。更底层的方式是查x$kglob表如果对象已经不在这个内部结构里说明库缓存层面的管理结构已经被移除。但注意这只代表管理结构被清理不代表内存堆已经归还。select kglhdadr, kglnaobj, kglobt03, kglobt04, kglobt05 from x$kglob where kglnaobj select * from t where id :1;如果结果为空说明这个对象已经从库缓存的管理结构中完全摘除。5. 现场遇到的问题和排查技巧总结5.1 典型问题速查表结合我自己的经验以及团队里其他 DBA 遇到过的场景把dbms_shared_pool.purge相关的典型问题整理成一张表现象表现根因定位解决思路purge 执行成功但 SQL 仍出现在 v$sql/v$sqlarea有会话仍然 pin 住该游标引用计数不为零找到持有游标的会话关闭游标或重建连接purge 后 SQL 响应时间没有变化游标确实被清理但新硬解析生成的执行计划仍然是旧的使用 SPM/SQL Profile 固定正确执行计划后再 purgepurge 报错ORA-20000对象类型错误或权限不足检查第二个参数是否正确确认是否有SYS权限purge 报错ORA-06550语法格式问题检查 address 和 hash_value 的格式不要加0x不要有空格相同 SQL 突然大量新增子游标purge 导致游标失效同时应用侧并发执行触发硬解析确认 purge 后在业务低峰期操作同时检查游标共享参数v$sql 中出现大量is_obsoleteY的游标频繁 DDL、绑定变量长度变化或 cursor_sharing 设置不合理结合具体触发原因优化必要时使用 dbms_shared_pool.purge 清理应用侧 ORA-01002: fetch out of sequence游标被 purge 后应用继续 fetch 已失效游标应用侧需要做异常捕获和重试逻辑数据库侧避免对正在使用中的 cursor 做 purge5.2 那个最容易踩的坑purge 和 flush shared_pool 混用有些 DBA 在处理游标问题时嫌dbms_shared_pool.purge麻烦直接一步到位执行alter system flush shared_pool。这个操作会把共享池里所有可清空的对象全部清除副作用极大所有 SQL 都需要重新硬解析短时间内库缓存命中率暴跌CPU 飙高。正在执行的 SQL 不受影响但新 SQL 全部走硬解析路径。大量并发硬解析可能导致 library cache 锁竞争加剧甚至引发ORA-04031。我见过某次故障处理中有人先 flush shared_pool 再尝试固定执行计划结果共享池在高峰期被冲垮系统更不稳定。正确做法是能用dbms_shared_pool.purge精确清理的不要用 flush。如果为了安全要 flush也应该在业务低峰期或者变更窗口内操作。还有一个经常被忽略的细节alter system flush shared_pool不会清理正在使用的游标。已经打开的游标不会立即失效要等游标关闭后才会被清理。所以对于连接池里持有着旧游标的会话flush 操作也同样无能为力最终还是得靠应用侧重建连接或主动关闭游标。5.3 连接池游标缓存带来的隐形负担这里再多说一句很多系统设计时没有考虑到连接池的游标缓存问题。默认情况下JDBC 驱动对每个连接会有一个隐式游标缓存Implicit Statement Caching大小由oracle.jdbc.implicitStatementCacheSize决定。每个物理连接上缓存的游标数量越多持有共享池对象的引用就越久。一个普遍的调优建议是不要盲目调大连接的游标缓存大小。对于 SQL 种类特别多比如拼接条件巨多的系统游标缓存命中率往往不高缓存了反而占用服务端游标资源。v$open_cursor中cursor_type为SESSION_CACHED的游标就属于这类被缓存的游标。如果你在排查 SQL 游标残留问题时发现v$open_cursor里有大量SESSION_CACHED状态为OPEN的游标优先考虑从连接池层面优化例如调小连接池的最大连接数减少物理连接总数。调小implicitStatementCacheSize或关闭隐式游标缓存。使用连接池的定期连接重建功能让旧游标周期性释放。6. 使用 dbms_shared_pool.purge 的几个实操经验6.1 安全 purge 的最佳实践顺序根据多次实战经验我总结了一套安全清理共享池对象的标准操作顺序第一步确认清理目标。先用v$sqlarea核对 SQL_ID、SQL 文本、version_count、loads、invalidations 信息确认它确实是你想清理的对象。注意如果一个游标的loads一直为 1而executions很大说明它一直被复用非常健康如果它执行计划有问题重点不是清理游标而是更新统计信息、加 hint、或通过 SPM 修正计划。第二步评估残留风险。查询v$open_cursor确认当前有多少个会话打开了该游标评估影响范围。如果影响面很大几十个活跃会话都在用建议先和应用团队沟通规划连接重建窗口。第三步执行 purge。-- 先找到目标 select address, hash_value, sql_id from v$sqlarea where sql_id sql_id; -- 执行清理 exec sys.dbms_shared_pool.purge(address, hash_value, C);第四步确认清理结果。分别查询v$sqlarea、v$sql、x$kglob、v$open_cursor确认清理结果判断是否还有会话在持有旧游标。第五步监控系统状态。purge 完成后的 15 分钟内重点关注v$sysstat中parse count (hard)、library cache hit ratio、shared pool free memory三个指标。如果硬解析数量急剧上升且共享池内存持续紧张考虑是否有并发的 SQL 风暴在触发。6.2 和 SPM 配合的正确姿势我强烈建议把dbms_shared_pool.purge和 SQL Plan Management 结合起来使用而不是孤立地清理游标。当一条 SQL 的执行计划因为统计信息变化或者其他原因走偏时正确做法是先从 AWR 或 dba_hist_sqlstat 里找到过去正常时期的 plan_hash_value。使用dbms_sqltune.create_sql_plan_baseline手动创建基线。启用 SPM 演进将正确的执行计划设置为首选计划。再做游标清理purge 掉当前走偏的游标让新硬解析直接使用基线计划。这样就能实现精准的 SQL 级性能修复而不影响其他 SQL 的共享池命中。这里有一个实操小技巧如果目标 SQL 已经在连接池中被大量会话持有purge 后短时间内新旧子游标可能并存这时可以利用 SPM 的自动演进功能把正确计划的基线标记为 ACCEPTED这样即使旧游标还存在新执行的 SQL 也会优先选择正确的计划。6.3 关于 Oracle 19c 和 21c 的行为差异最后提醒一下不同版本下 purge 对 cursor 的处理行为有细微差异。在 Oracle 19c 及之前版本中dbms_shared_pool.purge对正在执行的游标基本就是标记删除处理。但在 Oracle 21c 及之后的版本中Oracle 对库缓存做了不少重构引入了一些新的内存管理特性purge 的行为对引用计数的敏感度变得更高某些场景下甚至需要对象完全空闲才能成功清理。另外Oracle RAC 环境下需要注意dbms_shared_pool.purge只作用于当前节点不会广播到其他节点。如果你想在所有节点上都清理同一个游标需要分别在每个实例上执行一次。这个坑我见过好几次在单实例上清理完后发现其他节点的 SQL 还在误以为 purge 失效了。-- RAC 环境下查询各个节点的游标情况 select inst_id, sql_id, child_number, executions, loads, parse_calls from gv$sql where sql_id sql_id order by inst_id, child_number;7. 写在最后的个人体会从那次线上故障到现在我在处理游标清理问题上也积累了不少新的认识。最想和大家分享的一点是不要把一个工具神化也不要因为它的一次失灵就否定它。dbms_shared_pool.purge是一个精准的手术刀但它切的是静态的对象不是动态的连接。当业务系统通过连接池大量复用时游标的生命周期早就不纯粹由数据库侧控制了它和应用侧的长连接绑定在一起。所以如果你问我以后遇到执行计划走偏会怎么处理我会直说先上 SPM 固定正确计划再考虑清理游标。如果应用侧连接池支持优雅重启配合一个低峰期的连接重建多数游标问题都能在十几分钟内解决。dbms_shared_pool.purge更适合的是那种确定已经无人使用、但还霸占着共享池空间的死对象这种场景下它是当之无愧的一把好手。
![[数字安全]风险评估准备:决定资讯安全成败的关键基石](http://pic.xiahunao.cn/yaotu/[数字安全]风险评估准备:决定资讯安全成败的关键基石)
