PGA 超限的一次应急处置：扩容、清游标、杀会话
9 月 30 号这一天，接到反馈，业务反应查询有点慢。数据库显示acknowledge over PGA limit是最严重的等待事件。 这套库是 Oracle 19c 的 CDB，挂着 12 个 PDB，宿主机 24 核、94.4G 内存，SGA 已经占掉 58G。 --- ## 一、上午：AWR中最扎眼的是等待事件 AWR显示： |指标|值| | --- | --- | |DB Time|4,210.78 分钟（快照区间 60 分钟）| |会话数|393 → 539| |每秒 DB Time|69.9 秒| 一小时里累计 DB Time 四千多分钟，等于平均每秒有 70 个会话在忙。24 个核的机器干出这个数，说明大部分会话没在跑，是在等。 再看 Top 等待事件，第一行就把方向给定了： ``` acknowledge over PGA limit 3,616,348 次 172.2K 秒 68.2% cursor: mutex X 1,464,929 次 50.5K 秒 20.0% cursor: mutex S 451,284 次 1,446.9 秒 0.6% cursor: pin S wait on X 1,112 次 4,504.4 秒 1.8% resmgr:cpu quantum 4,563 次 2,326.9 秒 0.9% DB CPU 13.5K 秒 5.4% ``` 六成八的 DB Time 花在一个叫 `acknowledge over PGA limit` 的等待上。这个等待不属于 User I/O，也不属于 Concurrency，它被归类在 Scheduler 下面。含义是：进程申请的内存超过了 `pga_aggregate_limit`，Resource Manager 把会话挂起来排队，等有人把内存让出来。 换句话说，`PGA` 已经不是"紧张"，是溢出了。 拉到内存小节，数字对得上： |指标|Begin|End| | --- | --- | --- | |Host Mem|96,667.8 MB|96,667.8 MB| |SGA use|59,392.0 MB|59,392.0 MB| |PGA use|32,961.6 MB|40,483.8 MB| |SGA+PGA 占主机内存|95.54%|103.32%| 当时的参数是 `pga_aggregate_target=8G`、`pga_aggregate_limit=16G`。实际分配到 40.5G，硬限制 16G，超了 1.5 倍还多。 操作系统那边也印证了：`FREE_MEMORY_BYTES` 只剩 505 MB，`LOAD` 从 9 走到 10，`RSRC_MGR_CPU_WAIT_TIME` 累计 250,813。 ADDM 的结论跟我们的判断一致，它把三条 finding 按活跃会话占比列了出来： ``` Top SQL Statements AAS 93.07 75.14% PGA_AGGREGATE_LIMIT Throttling AAS 93.07 66.40% Shared Pool Latches AAS 93.07 25.08% ``` ``` 有意思的是这三条的平均活跃会话数都是 93.07。同一批会话同时满足三个条件，说明它们是被同一件事卡住的。 --- ## 二、20% 的 `cursor: mutex X` 是怎么来的 排在 PGA 后面的是 `cursor: mutex X`，占了 20%，五十多K 秒。这个等待通常指向 shared pool 里某个父游标下的子游标太多，会话遍历的时候抢互斥 latch。 顺着这条线往 Top SQL 里找，结果很集中： |指标|c1sj9us39v7nx|全库第二| | --- | --- | --- | |Elapsed Time|112,600.21 秒|20,664.08 秒| |占 DB Time|44.57%|8.18%| |Sharable Memory|326,189,368 B（311 MB）|55,363,512 B| 一条 SQL 吃掉 44.57% 的 DB Time，在 shared pool 里占了 311 MB，是第二名的六倍。 再看最底下的 `SQL ordered by Version Count`： ``` c1sj9us39v7nx version_count = 5,481 BSSC gh42bgwdn0by6 version_count = 926 OMC 5cu0x10yu88sw version_count = 569 OPM ``` ``` **五千四百八十一个子游标。** 第二名才 926。 具体的SQL这里不方便展示，是一条查用户角色权限的普通查询。 --- ## 三、下午：好了大半，没有好透 中间我们把 PGA 参数调了上去，也把这条 SQL 的父游标清了。到下午 15 点到 16 点这一小时再采样，DB Time 从 4,210.78 分钟降到 **681.86 分钟，降了 83.8%**。 先看结果如何，把两份报告的头号指标列在一起： |指标|上午|下午| | --- | --- | --- | |DB Time|4,210.78 分钟|681.86 分钟| |每秒 DB Time|69.9 秒|11.5 秒| |会话数|393 → 539|501 → 513| |每秒事务数|14.6|37.0| |每秒逻辑读|320,926.3 块|272,034.2 块| |每秒物理读|27,441.6 块|31,852.2 块| |LOAD|9 → 10|12 → 5| |RSRC_MGR_CPU_WAIT_TIME|250,813|153| 这里有个必须先讲清楚的前提：**下午的业务量是涨的。** 每秒事务数从 14.6 涨到 37.0，翻了两倍半；物理读也涨了 16%。在业务量涨上去的同时 DB Time 降了 83.8%，这样才谈得上是真改善，排除了"没人用所以变快"这种假象。 等待事件的变化更能说明问题： |等待事件|上午|下午| | --- | --- | --- | |cursor: mutex X|1,464,929 次 / 50.5K 秒 / 20.0%|完全消失| |cursor: mutex S|451,284 次 / 1,446.9 秒 / 0.6%|完全消失| |resmgr:cpu quantum|4,563 次 / 2,326.9 秒 / 0.9%|完全消失| |cursor: pin S wait on X|1,112 次 / 4,504.4 秒 / 1.8%|183 次 / 29.2 秒 / 0.1%| |DB CPU|13.5K 秒 / 5.4%|7,945.4 秒 / 19.4%| |db file sequential read|4,811.7 秒 / 1.9%|3,299.3 秒 / 8.1%| 三个 concurrency 类等待归零，配合 `RSRC_MGR_CPU_WAIT_TIME` 从 250,813 掉到 153（降了 99.9%），系统级数据也跟着变了——`LOAD` 从 12 掉到 5，CPU 资源管理器不再需要限流。 那条 SQL 的表现同样明显： |指标|上午|下午| | --- | --- | --- | |Elapsed Time|112,600.21 秒（第 1 名，44.57%）|992.09 秒（第 6 名，2.42%）| |Sharable Memory|326,189,368 B（榜首）|退出榜单| 榜首位置让给了 `6a0cn0u0ayk2f`（3,561 秒，8.70%），那是一条正常的高频小 SQL，45 万次执行、单次 0.01 秒，属于健康形态。 --- ## 四、但 `acknowledge over PGA limit` 还是第一名 这是下午那份报告里最刺眼的地方。 ||上午|下午| | --- | --- | --- | |acknowledge over PGA limit|3,616,348 次 / 172.2K 秒 / 68.2%|1,483,218 次 / 26.4K 秒 / 64.6%| 等待次数降了 59%，总时长从 172.2K 秒降到 26.4K 秒，降幅不小。但**占 DB Time 的比例几乎没动**，从 68.2% 到 64.6%，还是稳稳的第一。 ADDM 在下午那份报告里，依然把同一条 finding 排在最前面： ``` PGA_AGGREGATE_LIMIT Throttling AAS 12.80 66.40% PGA_AGGREGATE_LIMIT Throttling AAS 10.20 62.18% Top SQL Statements AAS 12.80 26.50% ``` ``` 两份报告、四个 ADDM task，`PGA_AGGREGATE_LIMIT Throttling` 的占比一直在 62% 到 72% 之间浮动。比例纹丝不动意味着：在下午这个采样区间里，内存依然是第一瓶颈，只是同时排队的人变少了。 下午的参数已经提到 `pga_aggregate_target=12G`、`pga_aggregate_limit=24G`。但内存小节给出的实际用量是： |指标|上午 End|下午 End| | --- | --- | --- | |PGA use|40,483.8 MB|33,130.6 MB| |SGA+PGA 占主机内存|103.32%|95.71%| **33 GB 的实际用量，对上 24 GB 的硬限制，还是超的。** 加上前面业务量涨了两倍半，能维持在 33 GB 不继续往上跳水，已经算是调度器勉强撑住了。 另外要提一句：下午这份报告的采样区间是 15:00 到 16:00，那时候会话还没清干净。真正彻底平稳，是后来把堵住的会话全部杀掉、应用重连之后的事。 --- ## 五、中间那条线：五千多个子游标是怎么查出来的 回到上午报告的 5,481。查这五千多个子游标的成因，前后绕了三次。 ### 5.1 第一次：以为没用绑定变量 看到五千多个子游标，第一反应是硬编码字面量。但 SQL 原文摆在上面，谓词写的是： ``` AND u.ID = :1 ``` 用了绑定变量，而且只有一个。这个判断当场作废。 ### 5.2 第二次：教科书里的 Bind Graduation，被打脸 既然用了绑定变量，最常见的解释是绑定变量长度毕业（Bind Graduation）——不同长度的绑定值各衍生一个子游标。 把 5,171 行 `v$sql_bind_capture` 拉出来做统计： |列|取值|行数|占比| | --- | --- | --- | --- | |POSITION|1|5,171|100%| |DATATYPE_STRING|NUMBER|5,171|100%| |MAX_LENGTH|22|5,171|100%| |CHARACTER_SID|852|5,171|100%| 四项全部零变异。两个假设一起排掉。 ### 5.3 第三次：信了 REASON，被 Y/N 列掀翻 `v$sql_shared_cursor` 里有一列 REASON，是给人看的 XML 汇总文本。导出来是这个分布： |REASON 里的 ID|reason 文本|子游标数|占比| | --- | --- | --- | --- | |3|Optimizer mismatch|2,966|57.4%| |39|Bind mismatch|2,205|42.6%| 跟着这个分布做了一堆分析：转移矩阵、游程检验、时序先后……得出"两个独立机制随机交替、乘法效应产生六千多个组合"的结论，看着相当自洽。 然后去查同一张表里的 Y/N 标志列： ``` SELECT optimizer_mismatch, bind_mismatch, bind_uacs_diff, bind_equiv_failure, COUNT(*) AS cnt FROM v$sql_shared_cursor WHERE sql_id = 'c1sj9us39v7nx' GROUP BY optimizer_mismatch, bind_mismatch, bind_uacs_diff, bind_equiv_failure ORDER BY cnt DESC; ``` 结果是： ``` N N N Y 5162 Y N N Y 9 ``` ``` `BIND_EQUIV_FAILURE='Y'` 覆盖了全部 5,171 个子游标，一条例外都没有。把两种判据放在一起对比： |判据|REASON 说|Y/N 列实际|偏差| | --- | --- | --- | --- | |Optimizer mismatch|2,966|9|差 329 倍| |Bind mismatch|2,205|0|根本不存在| |Bind Equiv Failure|0|5,171|完全颠倒| 最后一行最要命。正是因为 REASON 说 Bind Equiv Failure 计数为零，才在前面把 ACS 排除掉的。**REASON 把占 100% 的真凶整个漏报了。** ### 5.4 清完之后的意外收获：错配原因翻了过来 父游标清完之后，下午又查了一次同一张表。这次只有 36 行，做汇总统计： ``` OPTIMIZER_MISMATCH 35 BIND_MISMATCH 0 BIND_PEEK_MISMATCH 0 BIND_EQUIV_FAILURE 34 LANGUAGE_MISMATCH 0 EDITION_MISMATCH 0 ``` ``` 把前后对照起来看，变化相当明显： ||清理前|清理后| | --- | --- | --- | |BIND_EQUIV_FAILURE='Y'|5,171 / 5,171（100%）|34 / 36（94%）| |OPTIMIZER_MISMATCH='Y'|9 / 5,171（0.2%）|35 / 35（100%）| 清理之前 ACS 几乎包揽了全部；清理之后 OPTIMIZER_MISMATCH 升到了 100%。 逐行展开看更清楚。新批次的 child 编号是 0 到 36，其中： ``` child 0, 2 OM=Y BEF=N child 3~17, 19~30, 34 OM=Y BEF=Y child 31, 32, 33, 35, 36 OM=Y BEF=Y ROLL_INVALID=Y child 5516 OM=N BEF=Y ROLL_INVALID=Y ← 老批次残留 ``` ``` **这里有一个决定性的细节：child 0 和 child 2 是 ****`OPTIMIZER_MISMATCH='Y'**`** 而 ****`BIND_EQUIV_FAILURE='N'**`**。** 这说明光靠优化器环境差异，就足以铸造出一个新子游标，**ACS 并不是必要条件**。之前把 ACS 当主因、把 Optimizer mismatch 当次要命中，方向是反的。正确的因果顺序应该是： ``` 会话之间的优化器环境不一致（上游） → 铸造出一批多余 child → 这些 child 各自进入 bind-aware 状态 → ACS 再按选择性差异做乘数放大 → 从几十个涨到几千个 ``` ``` ACS 是放大器，不是发动机。真正该盯的是优化器环境为什么在不同会话之间会变。 这 36 行里还有两条线索。一是 child 31、32、33、35、36 带着 `ROLL_INVALID_MISMATCH`，这是统计信息失效留下的印记，说明期间有 `DBMS_STATS` 作业在跑，把刚建好的游标又作废了一遍。二是 child 编号 0 到 36 之间缺了 1 和 18，新批次刚生成就淘汰了两个，说明新建出来的 child 照样没人复用，只是渗漏的速度慢下来了。 至于 child 5516，它保留着老批次的签名（`OM=N` / `BEF=Y`），在计入 `version_count` 时被排除在外，所以会看到 36 行、`version_count` 却是 35 这个现象。 --- ## 六、实际上的处置过程 我同步展开了看看AI怎么回答。AI做着做着就开始说有bug要打补丁什么的。 我给他的边界很明确：不打补丁、不改优化器参数。不能有个什么事情就补丁，或许在有些公司可以。但是我们这里绝对不允许。 ### 6.1 第一步：把 PGA 加上去 这一步是对着占 68.2% 的那个等待去的，先解决"会话全被挂起"这个紧急状态： ``` ALTER SYSTEM SET pga_aggregate_target = 12G SCOPE=BOTH; -- 原 8G ALTER SYSTEM SET pga_aggregate_limit = 24G SCOPE=BOTH; -- 原 16G ``` 从 40.5 GB 的实际分配量看，16 GB 的 limit 确实是被击穿了的。这一步做完，DB Time 下来了，`cursor: mutex X` 跟着归零。但正如第四节所说，下午那份报告里 PGA 等待还是第一。 ### 6.2 第二步：清父游标 ``` SELECT address || ',' || hash_value AS cursor_key, version_count FROM v$sqlarea WHERE sql_id = 'c1sj9us39v7nx'; EXEC sys.dbms_shared_pool.purge('00000006645E4B18,110993053', 'C'); ``` 效果：`version_count` 从五千多条降到 **35**，降幅 99.3%。那个占着 311 MB 的游标也退出了 Sharable Memory 榜。 同批查出来的另外三条 SQL，量级都很正常，正好衬托出只有这一条异常： |sql_id|version_count| | --- | --- | |208qcat309zag|3| |2jb4jnvk2avr0|3| |9faby2nj5mw68|7| |c1sj9us39v7nx|35（清理前 5,000+）| 这里必须强调一句：**千万不要用 ****`ALTER SYSTEM FLUSH SHARED_POOL**`**。** 那会把全实例所有游标清干净，业务高峰期等同于自杀。用 `DBMS_SHARED_POOL.PURGE` 定向清这一条才对。 还有一点说明：下午那份 AWR 里 `SQL ordered by Version Count` 显示的还是 5,271，看着像"一小时又长回去了"。后来在 16:43 实测确认是 35，再结合新批次 child 编号是 0 到 36 连续排列，能判定那 5,271 是**老游标被标记失效后还没完全回收的残留计数**，不是重新涨起来的。 ### 6.3 第三步：杀会话 cleanup 之后仍没彻底恢复，把持有该游标的会话批量杀掉，应用重连，才真正平稳下来。 这一步不是多余。`DBMS_SHARED_POOL.PURGE` 在目标游标被会话持有时会**静默失败**——不报错，但也没效果。很多人 purge 完发现"没用"，基本都卡在这里。 另外，`pga_aggregate_limit` 的行为是优先"挂起+杀掉 PGA 用量最大的会话"，而不是直接让整个实例崩。所以当限制被击穿之后，会有一批会话陷入长时间的排队状态。不清掉它们，`pga_aggregate_limit` 调得再大也缓不过来——下午那份报告里 PGA 等待仍占 64.6%，很大程度就是这个原因。 --- ## 七、复盘：三件事各解决了什么 **第一步加 PGA**，让 `resmgr:cpu quantum` 消失、`RSRC_MGR_CPU_WAIT_TIME` 降 99.9%、`LOAD` 从 12 掉到 5。这一步解决的是"CPU 资源管理器在限流"，代价是内存水位维持在 33 GB 高位。它止住了血，但没有消除 `acknowledge over PGA limit`。 **第二步清父游标**，让三个 concurrency 类等待归零，那条 SQL 从占 44.57% 掉到 2.42%。这一步解决的是 shared pool 里的遍历竞争和 311 MB 的内存占用。 **第三步杀会话**，才是让 PGA 等待彻底退下去的最后一块拼图。 三者有先后，缺了任何一步都不完整。尤其是第三步，容易被当成"前两步没做干净"的补救动作，实际上它是必须的。 ---
