简介一份面向 DB2 数据库管理员与性能优化人员的实战案例分析文档来自某银行生产环境真实故障SQL 执行明显变慢、ACTIVE SESSION 异常升高常规 CPU、内存、I/O、锁等待检查均未发现问题最终定位为系统临时表空间 TEMPSPACE1 膨胀至 10GB 所致。资源围绕问题定位、原因剖析与解决思路展开详细讲解 db2top 会话监控、LATCH 竞争分析、堆栈收集以及 db2trc 暂停实例等诊断手段并给出优化 SQL、调整参数、清理临时对象等处置建议。文档以 1 个 doc 文件呈现压缩包约 607KB内容精炼、步骤完整适合需要排查 DB2 临时表空间性能问题的中高级 DBA 参考。已有 5899 人学习下载。1. ACTIVE SESSION 飙到 50 的那一刻一次被 TEMPSPACE1 坑惨的 DB2 性能问题DB2 系统临时表空间过大引发的性能问题听起来像冷门话题真正碰上能把人折腾到怀疑人生。某银行核心系统某天开始频繁报慢监控里 ACTIVE SESSION 数量突破 50多条 SQL 执行时间从毫秒级拉到好几秒。按常规套路检查 CPU、内存、I/O、锁等待、缓冲池命中率全部正常慢 SQL 也抓了索引也建了长语句也优化了性能问题纹丝不动。最后发现元凶竟是平时没人注意的 TEMPSPACE1——系统临时表空间涨到 10G遗留的 TDA 文件让表空间快照慢到离谱间接把查询全部堵死。本文复盘完整定位过程、踩过的坑、以及最终用 deactivate/activate 收场的方案对正在排查 DB2 性能问题的 DBA 有参考价值。2. 现象确认与快速排除法常规指标全部正常问题到底在哪第一次收到业务反馈的时候我其实抱着一丝侥幸心理——业务人员的感知有时候是主观的也许只是某个应用版本发布后的偶发波动。但当数据库监控开始频繁捕捉到 ACTIVE SESSION 数量达到 50 以上的时候这个问题就不能再拖了。作为运维 DBA看到这个指标的那一刻基本可以断定数据库自身出问题了且 SQL 执行时间在变长。2.1 ACTIVE SESSION 高不代表一切状态分布才是关键在 DB2 的监控体系里ACTIVE SESSION 数量即 db2top 中的 ActSess 指标是最直观的负载信号。它统计的是数据库当前正在执行操作的会话数也就是正在处理请求的 application 数量。用 db2top 连上数据库之后按 d 进入数据库概览页面默认每 2 秒刷新一次可以很直观地看到当前的 ActSess 数值。这个数值也可以通过 db2 LIST APPLICATIONS SHOW DETAIL 的结果手工统计得到但 db2top 更省事。需要说明的是ACTIVE SESSION 并不等于全部连接数。一个事务TRANSACTION内执行完一条语句、下一条还没开始的间隙application 的状态是 UOW Waiting不计入 ActSess而处于 Compiling、UOW Executing、Lock Waiting、Commit Active 以及 WLM 并发限制下的 Queued 状态的会话都会计入。所以看到 ActSess 高第一步不是慌而是要知道这些会话到底卡在什么状态上。在 db2top 界面按 l小写 L进入 SESSIONS 页面逐条查看状态本次看到的全部是 UOW Executing——说明应用正卡在语句执行上而不是在等锁或者提交。db2top 交互指令功能d数据库概览页查看 ActSess、HitRatio、I/O 时间等核心指标lSESSIONS 页逐条查看活动会话状态D动态 SQL 缓存页按执行时间、次数等维度排序查看z在动态 SQL 页内按指定列排序例如按 Avg ExecTime 排序这个系统的 ActSess 平时基本在 10 以内偶尔冲到 30而这几天频繁超过 30峰值甚至到 50。OLTP 系统的 ActSess 长期在 10 以内属于正常区间二三十算比较繁忙持续更高就要考虑扩容了。但一个平时只有几个活跃会话的系统突然频繁到几十个无非两种可能一是业务访问量激增二是 SQL 执行时间变长。和业务系统负责人确认过访问量没有变化那就只剩第二种可能。2.2 快速排除法CPU、内存、I/O、锁等待、命中率逐个过定位思路确定后我按常规顺序把能一眼看到的问题先排除掉。检查结果如下CPU 利用率在正常范围排除 CPU 瓶颈。操作系统内存使用量正常排除内存压力。db2top 里四个 I/O 指标AvgPRdTime、AvgDRdTime、AvgPWrTime、AvgDWrTime没有异常拉高排除物理 I/O 瓶颈。缓冲池命中率HitRatio正常排除读路径问题。没有 LOCK WAIT排除锁竞争导致的等待。唯一引起注意的亮点是 Deadlocks 累计值到了 218以及 SortOvf排序溢出为 10。但死锁计数本身是累计值不会直接拖慢性能排序溢出略高考虑到应用的 SQL 确实有较多排序操作也不是主要嫌疑。这一轮快速排除法跑完常规的资源瓶颈全部排除问题指向了更隐蔽的路径。看到这里一个经常被忽略的点值得单独提出来确认是数据库自身问题之后网络故障的可能性就该从排查清单里划掉了。很多新手在内存、CPU 都正常的时候会跑去查网络方向就偏了。2.3 用 MON_CURRENT_SQL 抓当前正在执行的语句排除掉外部因素后接下来就是抓执行时间长的 SQL。我用的是 MON_CURRENT_SQL 管理视图它可以直接看到当前正在执行的动态 SQL 和静态 SQL并按已运行时间排序db2 SELECT COORD_MEMBER MEMBER, VARCHAR(SESSION_AUTH_ID,10) USER, VARCHAR(CLIENT_APPLNAME,10) APPNAME, ELAPSED_TIME_SEC ELAPSEDTIME, VARCHAR(ACTIVITY_STATE,10) STATE, TOTAL_CPU_TIME, ROWS_READ, ROWS_RETURNED, QUERY_COST_ESTIMATE, DIRECT_READS, DIRECT_WRITES, VARCHAR(STMT_TEXT,2000) as STMT_TEXT FROM SYSIBMADM.MON_CURRENT_SQL ORDER BY ELAPSED_TIME_SEC DESC这个查询的关键在于 ORDER BY ELAPSED_TIME_SEC DESC把已运行时间最长的语句排在最前面。注意 ELAPSED_TIME 是语句从开始到抓取时刻的运行时长不是完整执行时间因为抓的时候语句还没跑完。本次执行时抓到了接近 30 条正在运行的语句也就是当时的 ACTIVE SESSION 数量接近 30其中不少语句的已运行时间已经数秒。由于这类抓取是瞬时的一次可能错过峰值要多执行几次或者写个循环连续抓才能捕捉到 ACTIVE SESSION 数量高的时候到底在跑什么。另外从这条 SQL 的字段里可以顺带看到 TOTAL_CPU_TIME、ROWS_READ、ROWS_RETURNED 等信息如果某些语句 CPU 时间占比很高说明消耗在计算上如果 ROWS_READ 巨大而 ROWS_RETURNED 很小多半有全表扫描或者过滤条件失效。这些信息可以给后续的优化方向提供线索。3. 慢 SQL 的时间都花在哪了MON_GET_PKG_CACHE_STMT 揭开等待真相抓到了慢语句下一步自然是分析时间花在哪。这里只靠 MON_CURRENT_SQL 不够因为它展示的是瞬间状态缺少完整的执行统计。我改用 MON_GET_PKG_CACHE_STMT 表函数把语句执行过程中的等待时间、读时间、锁时间、日志写等待、排序时间全部算出来看百分比。3.1 用 MON_GET_PKG_CACHE_STMT 计算各项等待占比MON_GET_PKG_CACHE_STMT 返回的是 PACKAGE CACHE 中语句的累积执行信息包含非常细的时间分布。直接查询时字段太多表函数返回的结果集很宽我一般按比例聚合之后看db2 w SELECT MEMBER, NUM_EXECUTIONS, STMT_EXEC_TIME, decimal((TOTAL_ACT_WAIT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_wait, decimal((POOL_READ_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_read, decimal((LOCK_WAIT_TIME/double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_lock, decimal((LOG_DISK_WAIT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_log, decimal((TOTAL_SECTION_SORT_TIME / double(STMT_EXEC_TIME)) * 100, 5, 2) as pct_sort, CAST(FLOAT(STMT_EXEC_TIME)/FLOAT(NUM_EXECUTIONS) as decimal(10,3)) as AVG_EXEC_TIME, VARCHAR(STMT_TEXT,500) AS STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT(NULL,NULL,NULL,-2)) WHERE NUM_EXECUTIONS 1 and STMT_EXEC_TIME 0这里说明一下各个参数和字段的含义NUM_EXECUTIONS 1 是为了排除只执行过一次的语句避免把偶然的编译开销算进平均执行时间。STMT_EXEC_TIME 0 是排除没有执行时间的语句。TOTAL_ACT_WAIT_TIME / STMT_EXEC_TIME 算出的 pct_wait 是所有活动等待占总执行时间的百分比这是判断语句是「在等」还是「在算」的关键。POOL_READ_TIME 是缓冲池读等待LOCK_WAIT_TIME 是锁等待LOG_DISK_WAIT_TIME 是写日志等待TOTAL_SECTION_SORT_TIME 是段排序时间。MON_GET_PKG_CACHE_STMT(NULL, NULL, NULL, -2) 中-2 表示当前成员上的数据库级别缓存。字段含义本次结果pct_wait活动等待占总执行时间百分比5 条 with 语句超过 95%pct_read缓冲池读等待占比正常pct_lock锁等待占比正常pct_log写日志等待占比正常pct_sort排序等待占比正常结果一出来问题就清楚了平均执行时间在 2 到 6 秒的语句里有 5 条 with 开头的语句 PCT_WAIT 超过 95%也就是说几乎全部时间都花在等待上但等待的不是读、不是锁、不是日志、也不是排序。如果想进一步确认等待类型在 SELECT 里增加 TOTAL_EXTENDED_LATCH_WAIT_TIME 字段就能看到 LATCH 等待时间——正是这些语句的等待大头。3.2 一句话判断等的是 LATCH 而不是锁DB2 的 LATCH 和锁LOCK是两回事。锁是应用层可见的并发控制机制有锁等待时能在监控里看到LATCH 则是 DB2 内部为了保证多线程访问共享内存结构一致性而使用的轻量锁完全在引擎内部用户文档里基本找不到完整的说明。例如内存里的 PREFETCH QUEUE用户请求的 EDU 往里添加预取任务PREFETCHER EDU 从里面读取任务双方并发访问时就必须用 LATCH 保护。从监控的角度说锁等待有专门的监控元素而 LATCH 等待只能通过 db2pd -latches 或者 TOTAL_EXTENDED_LATCH_WAIT_TIME 这类监控元素看到。在之前的快速排除法里我已经用 db2pd -latches 检查过一次没有异常但这回扩大了检查窗口用 db2pd -latches -rep 2 每隔 2 秒持续输出终于发现了问题每隔一段时间就会出现一波 LATCH 等待一个 EDU ID 11665 作为 Holder 拿着一个叫 pool_table_latch 的 LATCH旁边有 23 个 EDU 在等它。一个 Holder 阻塞 23 个 Waiter这意味着当时至少有 24 个会话被这一把内部 LATCH 卡住。ACTIVE SESSION 高到 50 的谜底到这里已经浮出水面了一半。3.3 别急着优化 SQL先确认是不是 SQL 本身慢看到慢 SQL 的第一反应是优化我也这么干了。先快速建索引解决了几条有全表扫描的语句又梳理了几条逻辑复杂的长 SQL。但做着做着突然意识到一个问题这些逻辑复杂的长 SQL 不是最近才上线的应用没有变更数据库也没有变更为什么以前不慢现在慢这就是一个很容易踩的坑——问题出现在 SQL 上不代表根因就在 SQL 上。执行计划、索引、统计信息都可以造成执行时间变长但如果没有相关的变更就应该怀疑是环境因素发生了变化。带着这个疑问回头检查才注意到 LIST TABLESPACES SHOW DETAIL 这款本身应该毫秒级返回的命令居然要好几秒才能跑完。表空间相关操作变慢这在当时并没有立刻和性能问题联系到一起但后来证明这正是整个事件的钥匙孔。4. 用 STACK 逼出真相Holder 在快照表空间Waiter 在创建临时表LATCH 等待是瞬时的等几秒就消失单纯靠 db2pd -latches 很难抓到 Holder 和 Waiter 当下正在执行什么操作。DB2 又没有文档能查到 pool_table_latch 的具体语义这个 LATCH 名字看似眼熟字面意思却给不了任何线索。到了这一步常规手段已经到头了只能用收集 STACK 的办法硬看线程在跑什么函数。4.1 db2pd -stack all按时间窗口持续抓取线程栈db2pd 提供收集 DB2 运行 STACK 的能力原本常用于分析 HANG 问题但同样可以用来辅助 LATCH WAIT 分析因为收集 STACK 的同时会带上每个 EDU 当时的 LATCH 持有和等待信息。由于 LATCH WAIT 不是持续存在而是隔一段时间冒出来一次所以需要按时间窗口连续抓取cd /tmp mkdir stack0531 db2pd -stack all dumpdir/tmp/stack0531 -rep 3 10这里的关键参数dumpdir/tmp/stack0531 把结果写到指定目录默认会写到 DIAGPATH指定目录便于整理和回收。-rep 3 10 表示每隔 3 秒收集一次连续收集 10 次一共 30 秒的窗口。为什么用 3 秒而不是更长因为 STACK 收集对资源有明显消耗窗口太长会拖慢数据库窗口太短又可能漏掉瞬态的 LATCH 事件3 秒间隔是比较折中的选择。收集完第一轮先确认有没有抓到 pool_table_latchcd /tmp/stack0531 grep pool_table_latch *stack.txt如果 grep 结果为空就继续下一轮收集。生产环境如果比较繁忙收集 STACK 会明显消耗 CPU所以这个操作必须谨慎尽量避开业务高峰。抓到之后结果里会区分两类Holding Latch type 和 Waiting on latch type分别对应持锁方和等待方。4.2 从函数名读出操作类型SNAPSHOT vs 创建内部临时表拿到带 LATCH 信息的 STACK 文件后进入对应的 .stack.txt 看调用栈。函数名虽然怪但拼读一下还是能猜出大意。Holder EDU 的 STACK 里有这样一组连续的调用sqlbSnapshotTablespace - sqlbMonSMSGetContainerPages一看就知道这个 EDU 正在对表空间做 SNAPSHOT进一步是在获取 SMS 表空间容器的数据页信息。也就是说有人在定期对表空间做监控快照而这个快照操作本身没毛病但因为 TEMPSPACE1 太大取数据页的操作变得极其缓慢长时间持有 pool_table_latch——这就是阻塞的根源之一。Waiter EDU 的 STACK 则完全相反sqldTableCreateTemp - sqldInternalCreateTable - sqlbGetSysTempDatapool配合 sqldEvalDataPredevaluate predicate和 sqlriFetch、sqlrita 这类函数可以确认这串调用是在执行一条查询 SQL并且需要创建内部临时表使用系统临时表空间。查询要建内部临时表就要往系统临时表空间的数据池里分配空间而这一步又需要拿 pool_table_latch——恰恰被表空间快照操作捏在手里。于是 23 个等 LATCH 的 EDU 就这么被一个 SNAPSHOT 操作集体堵住了。到这里整个因果链已经完整闭合数据库监控代理定时采集表空间信息 → 对 SMS 表空间做 SNAPSHOT → TEMPSPACE1 有 10G 的遗留临时文件导致快照极慢 → 长时间持有 pool_table_latch → 需要创建内部临时表的查询全部被堵 → ACTIVE SESSION 飙升、业务响应变慢。链路里的每一个环节单独看都没什么异常串起来就是一个隐蔽的连锁故障。4.3 analyzestack把几十份 STACK 交给脚本自动汇总手工翻 STACK 文件效率很低几十份文件逐个看非常费劲。DB2 其实自带一个 Perl 脚本 analyzestack藏在 sqllib/pd 目录下功能是把指定目录下所有 .stack.txt 文件里重复的 STACK 归并统计还能做 LATCH 分析。这脚本原本是 IBM DB2 支持部门的内部工具后来放进产品里但官方文档一直没怎么宣传脚本开头的声明也写明「as-is」不提供官方支持。perl ~/sqllib/pd/analyzestack -d 50 -i /tmp/stack -l-d 50 指定对比 STACK 时比较的函数个数数值越大匹配越严格。-i /tmp/stack 指定 .stack.txt 所在目录。-l 开启 LATCH 分析模式。执行完会生成 LatchAnalysis.out 和 StackAnalysis.out 两个文件。如果 LATCH 分析模式检测到 Holder-Waiter 关系会打印出对应的 EDU 和调用栈如果没检测到也会把 Holder 和 Waiter 信息列出来供人工核对。这个脚本的具体用法没有官方文档只能靠 -help 自行探索但在这个场景下它把「手动 grep 几十个文件」变成了一条命令的事效率提升非常明显。5. 避坑与常见问题TEMPSPACE1 暴涨后的五个典型坑在正式讲解决办法之前把这次踩到和后续复盘总结的坑集中列出来。每一条都是真实发生过的现象照着现象对号入座能省不少弯路。5.1 现象LIST TABLESPACES SHOW DETAIL 突然要好几秒现象一条平时毫秒级返回的表空间查看命令执行时间拉长到数秒而且 TEMPSPACE1 的 Total pages 显示 354661按 32KB 页大小算正好约 10G。原因TEMPSPACE1 是 SMS系统管理表空间其容器里堆积了大量的 TDA 临时文件。表空间信息查看操作要获取每个容器的数据页信息文件越多越慢进而把持有 pool_table_latch 的时间拉长。解决在维护窗口对数据库执行 deactivate/activate让临时表空间重新初始化遗留的 TDA 文件被清理表空间大小回到正常水平命令恢复毫秒级。5.2 现象TDA 文件遗留占满文件系统现象查看表空间容器目录例如 T0000001/C0000003.TMP 下的文件发现大量 SQL0xxxx.MEMBER0000.TDA 文件每个 1MB 左右数量成百上千磁盘空间被占满。原因某时间点 ACTIVE SESSION 瞬间冲到 800 多个大量 SQL 并发使用临时表空间数据库扛住了但临时表文件没有被正常清理回收遗留成 TDA 文件。IBM Technote 里有过说明这类 TDA 文件不被自动删除、占满文件系统是「Work as Design」。解决同 5.1deactivate/activate 数据库后 TDA 文件被释放。注意这个释放动作不会立刻回收物理文件而是让表空间回到初始状态空间统计恢复正常。5.3 现象重启实例没用必须 deactivate/activate现象有人先尝试重启数据库实例来清理临时表空间结果重启后 TEMPSPACE1 依旧 10G问题没有解决。原因重启实例不会重新初始化数据库内部的临时表空间元数据遗留的 TDA 文件与表空间控制信息仍然指向原位置deactivate 数据库再 activate 才会触发临时表空间的重建。解决在维护窗口执行 db2 deactivate db 然后 db2 activate db 确认 TEMPSPACE1 空间回落后再开放业务。生产环境操作前务必确认没有长事务和未提交的会话。5.4 现象ACTIVE SESSION 一度冲到 800是隐患的起点现象回看监控历史发现某个时间点 ACTIVE SESSION 数量瞬间达到 800 多个大量 SQL 同时使用了临时表空间之后 TEMPSPACE1 从 0 暴涨到 10G。原因突发高并发导致临时表空间被大量分配语句执行完后SMS 表空间中的 TDA 文件没有按预期回收遗留堆积。这类遗留问题平时没有症状直到某天监控快照把它放大成性能瓶颈。解决这类隐患很难提前发现只能靠定期查看表空间历史曲线。TEMPSPACE1 的正常值应该非常小一旦发现持续增长或者异常暴涨尽早安排维护窗口处理不要等问题暴露。5.5 现象db2trc 可以「暂停」实例做定向验证现象为了验证「表空间信息查看操作会持有 pool_table_latch」这一结论可以用 db2trc 设置断点条件让 DB2 在进入特定函数时挂起为人工介入争取时间。原因db2trc 不仅能记录函数调用还能配合 -suspendTrace 在命中指定函数的入口时暂停 trace等于给了一个定向观察的机会。解决先 db2 CONNECT TO mydb再执行 db2trc on -crash DB2.SQLB.sqlbSMSGetContainerPages.entry -suspendTrace然后执行 LIST TABLESPACES SHOW DETAIL 触发 SNAPSHOT 路径观察实例是否被挂起。验证完毕用 db2trc off 关闭并清理。这个实验只在空闲环境做生产环境切勿尝试。6. 快速定位这类问题一套顺手且不白忙的排查顺序这次案例最有价值的不是某个命令而是整套排查顺序。以后再遇到 ACTIVE SESSION 从个位数突然飙到几十的情况我会按下面的顺序走一遍第一步先用 db2top 确认 ActSess 数值和状态分布。数值高但状态是 UOW Waiting 或者 Lock Waiting和全是 UOW Executing处理方向完全不同。顺手把 CPU、内存、I/O 指标过一眼排除资源瓶颈。第二步用 MON_CURRENT_SQL 连续抓几次当前正在执行的 SQL。注意 MON_CURRENT_SQL 是瞬时视图抓不到峰值就多抓几次配合 ELAPSED_TIME 排序把候选语句池捞出来。第三步用 MON_GET_PKG_CACHE_STMT 算所有候选语句的时间分布。重点看 PCT_WAIT如果等待占比超过 90%再逐项看是 LOCK_WAIT、POOL_READ 还是 LATCH 等待。这一步能告诉你慢 SQL 到底是在「等」还是在「算」。第四步如果怀疑 LATCH用 db2pd -latches -rep 2 持续观察。看到异常 LATCH 后立刻用 db2pd -stack all dumpdir... -rep 3 10 收集线程栈并配合 analyzestack 汇总从函数名读出 Holder 和 Waiter 的操作类型。第五步回头检查表空间大小。LIST TABLESPACES SHOW DETAIL 本身就慢往往意味着 SMS 表空间容器里有大量文件。把 TEMPSPACE1 的 Total pages 和容器目录下的 TDA 文件数量对照起来看基本就能锁定根因。第六步处理与验证。维护窗口内 deactivate/activate确认 ACTIVE SESSION 回落到个位数、LIST TABLESPACES SHOW DETAIL 恢复毫秒级监控一到两天无复发即算闭环。从那以后我每次遇到 DB2 性能问题第一反应不再是急着优化 SQL而是先看系统临时表空间和表空间快照路径是否健康。毕竟这次翻车的教训太深刻——一个没人关心的 10G 临时表空间能让整库的查询在毫秒与秒级之间反复横跳。希望这篇复盘对正在被同样问题卡住的你能有点帮助。本文还有配套的精品资源点击获取