DB2性能排查：ACTIVE SESSION飙升与临时表空间LATCH等待

简介这份文档面向DB2数据库运维DBA与性能调优学习者记录某银行DB2系统SQL执行变慢的真实排查案例核心聚焦系统临时表空间TEMPSPACE1异常膨胀至10GB所引发的性能问题。资源包共1个doc文件约607KB内容涵盖性能问题初步确认、LATCH分析方法、STACK收集与分析技巧以及用db2trc工具暂停实例抓取诊断信息等实操手段并给出SQL优化与参数调整的解决思路。已有5903人学习下载适合希望积累真实故障定位经验、补充临时表空间与并发竞争排查方法的中高级DBA参考借鉴。1. 系统突然变慢ACTIVE SESSION 冲到 50常规排查全部扑空业务侧反馈“系统变慢”往往是最难接的一类工单因为这句话背后可能是应用、网络、存储也可能是数据库。这次遇到的场景很典型某业务系统从某天开始陆续有人反映响应变慢数据库监控频繁捕捉到 ACTIVE SESSION 数量超过 50而此前这个指标长期在 10 以内。登录服务器一看多条 SQL 执行时间从毫秒级涨到 36 秒对 OLTP 系统来说这已经是肉眼可见的卡顿。按部就班查了一圈CPU 利用率正常操作系统内存正常缓冲池命中率正常没有 LOCK WAITdb2top 右下角的物理读、直接读、物理写、直接写四个时间指标也都正常说明 I/O 没出问题。抓出执行时间长的 SQL建索引解决了几个全表扫描剩下的长 SQL 逻辑复杂但并不是新上线的。折腾一圈指标全绿问题还在。真正的原因藏在一个几乎没人看的对象上系统临时表空间 TEMPSPACE1 涨到了 10G。这篇笔记就把这条从“指标全正常”到“定位到临时表空间”的排查链路拆开讲清楚适合正在被 DB2 性能问题卡住、又不想盲目改参数的 DBA 和开发同学。2. 先确认是不是数据库的锅ACTIVE SESSION 与等待时间的判读2.1 ACTIVE SESSION 高不等于数据库有问题但状态分布会说话ACTIVE SESSION 指的是当前正在处理 SQL 请求的会话数编译中、执行中、锁等待、提交中、WLM 排队中的会话都计入而事务里执行完一条语句、下一条还没开始的 UOW Waiting 状态不计入。所以看到数字高第一步不是慌而是看这些会话卡在什么状态。用 db2top 连上库后按d看整体再按l小写 L进 SESSIONS 页面看明细。这次所有活动会话都处于 UOW Executing说明它们是真的在执行不是排队也不是等锁。判断逻辑很直接OLTP 系统平时 ACTIVE SESSION 个位数突然频繁冲到几十只有两种可能——业务量真的涨了或者单条 SQL 变慢了。和业务负责人确认访问量没有增加那就锁定为后者。这一步的价值在于排除方向性错误避免在“扩容”和“优化 SQL”之间反复摇摆。2.2 用 MON_CURRENT_SQL 抓现场用 MON_GET_PKG_CACHE_STMT 看时间花在哪抓正在执行的语句MON_CURRENT_SQL管理视图最直接动态静态 SQL 都能看到按已运行时间倒序排-- 抓当前正在执行的 SQL按已运行秒数倒序 SELECT COORD_MEMBER AS MEMBER, VARCHAR(SESSION_AUTH_ID, 10) AS USER, VARCHAR(CLIENT_APPLNAME, 10) AS APPNAME, ELAPSED_TIME_SEC AS ELAPSEDTIME, VARCHAR(ACTIVITY_STATE, 10) AS 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;ELAPSED_TIME_SEC是语句已运行秒数ACTIVITY_STATE能看出是执行还是等锁。注意这个视图抓的是“此刻”语句没跑完时显示的时间比完整执行时间短所以要在 ACTIVE SESSION 高的时候多执行几次才能抓到现场。要看时间到底消耗在哪用MON_GET_PKG_CACHE_STMT表函数算各项等待占比-- 计算平均执行时间与各类等待时间占比定位瓶颈类型 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;-2表示取所有成员上的语句信息NUM_EXECUTIONS 1过滤掉只跑过一次的语句避免平均值失真。结果里几条以 with 开头的语句pct_wait超过 95%但读、锁、日志、排序占比都很低说明时间花在了一种不在这几类里的等待上。把TOTAL_EXTENDED_LATCH_WAIT_TIME加进查询字段答案就出来了它们在等 LATCH。提示如果应用是拼接 SQL 而不是变量绑定包缓存里几乎每条语句都是新条目NUM_EXECUTIONS普遍很小这时候看平均值意义有限要结合MON_CURRENT_SQL的现场抓取一起判断。3. LATCH 等待怎么查db2pd 抓不到就持续抓再配合 STACK 看 EDU 在干什么3.1 pool_table_latch 异常一个 Holder 卡住 23 个 WaiterLATCH 是 DB2 内部为保证多线程访问共享内存结构一致性而设的轻量锁和用户可见的行锁、表锁不是一回事文档里几乎没有面向用户的说明。第一次用db2pd -latches快速扫了一眼没发现异常问题在于 LATCH 等待只持续几秒就消失单次采样很容易错过。改成持续采样# 每 2 秒采样一次 LATCH 情况持续输出到屏幕 db2pd -latches -rep 2-rep 2是重复间隔秒数不加次数就持续输出CtrlC 结束。这次抓到了EDU 11665 持有pool_table_latch同时有 23 个 EDU 在等它。一个操作拿着 LATCH 阻塞 23 个操作ACTIVE SESSION 至少 24和监控看到的数字对得上。但 LATCH 名字只是内部符号光看名字不知道 Holder 在干什么而等待几秒就散了来不及抓现场。3.2 用 db2pd -stack 收集 STACKgrep 出 Holder 和 Waiter 的调用栈db2pd 的-stack能在收集堆栈的同时记录每个 EDU 当时的 LATCH 和 LOCK 信息正好用来补上“Holder 在干什么”这块拼图# 每 3 秒收集一次全实例 STACK连续 10 次输出到指定目录 mkdir -p /tmp/stack0531 db2pd -stack all dumpdir/tmp/stack0531 -rep 3 10 # 在结果里找 pool_table_latch 的持有者和等待者 cd /tmp/stack0531 grep pool_table_latch *stack.txtdumpdir指定输出目录不指定就落到 DIAGPATH-rep 3 10表示间隔 3 秒、共 10 轮因为等待现象不是一直存在必须持续采样才有命中率。grep 结果里Holding Latch type是持有者Waiting on latch type是等待者后面跟着地址、代码行号和源文件名。注意生产库繁忙时收集 STACK 会明显消耗资源、影响性能务必在低峰或问题复现窗口内短时收集别长时间挂着跑。3.3 读 STACK 不求看懂全部抓函数名和调用关系就够Holder 的栈里能看到sqlbSnapshotTablespace - sqlbMonSMSGetContainerPages字面意思是对表空间做快照、在监控 SMS 表空间时获取容器数据页Waiter 的栈里是sqldTableCreateTemp - sqldInternalCreateTable - sqlbGetSysTempDatapool即创建内部临时表、申请系统临时表空间再往上有sqldEvalDataPred谓词求值说明这是一条带谓词、用到临时表的查询。结论成形监控代理定期对表空间做快照快照在 SMS 表空间上取容器页信息耗时变长长时间持有pool_table_latch把正在建临时表的查询全堵住了。手工 grep 效率低DB2 安装目录下有个 Perl 脚本analyzestack能汇总相同栈并做 LATCH 分析# -d 指定比较函数个数-i 指定 stack 文件目录-l 做 LATCH 分析 perl ~/sqllib/pd/analyzestack -d 50 -i /tmp/stack0531 -l执行后生成LatchAnalysis.out和StackAnalysis.out前者列出 Holder/Waiter 配对后者按调用栈聚类。这个脚本没有官方支持承诺属于“能用但别指望文档”的工具参数细节用-help看。4. 根因落地TEMPSPACE1 涨到 10GTDA 文件没被回收4.1 一个反直觉的信号查表空间信息本身都要好几秒顺着“临时表空间”这条线去查发现db2 LIST TABLESPACES SHOW DETAIL都要跑好几秒才出结果——查元数据都慢说明表空间对象本身状态异常。输出显示 TEMPSPACE1 是系统管理的 SMS 临时表空间总页数 354661页大小 32768 字节算下来约 10G而且 Used pages 等于 Total pages。正常系统临时表空间应该很小涨到 10G 本身就是异常。到容器目录下看文件一堆SQL0xxxx.MEMBER0000.TDA文件单个 1MB 左右数量很多。TDA 是临时数据文件SMS 临时表空间里的 TDA 文件在某些情况下不会被及时删除会一直占着空间这是产品设计行为而非缺陷。翻监控历史曲线能看到某个时间点 TEMPSPACE1 从接近 0 直接暴涨到 10G对应的是当时一瞬间 ACTIVE SESSION 冲到 800 多大量 SQL 同时使用临时表空间做排序和临时表语句都成功了、库也没崩但留下一地 TDA 文件成了后来踩到的雷。4.2 解决动作deactivate/activate 数据库不是重启实例确认根因后处理很简单但有个关键区别必须说清楚只重启实例释放不掉这些临时表空间必须对数据库做 deactivate 再 activate。# 先强制断开所有应用连接再停库、起库 db2 force applications all db2 deactivate db dbname db2 activate db dbname # 验证临时表空间是否回落 db2 LIST TABLESPACES SHOW DETAIL | grep -A 6 TEMPSPACE1force applications all踢掉残留连接否则 deactivate 会失败deactivate db才会触发临时表空间的清理activate db让数据库保持常驻激活状态。处理完再跑LIST TABLESPACES SHOW DETAIL秒级返回Used pages 大幅回落。持续观察一段时间ACTIVE SESSION 回到个位数之前 36 秒的语句恢复正常性能问题消失。4.3 为什么临时表空间大会拖慢查询一条 LATCH 竞争链把因果串起来临时表空间膨胀到 10G 后监控代理对表空间做快照、枚举 SMS 容器页的耗时被拉长这个操作持有pool_table_latch的时间随之变长而查询里只要用到系统临时表排序溢出、内部临时表就要申请同一个 LATCH 去拿临时数据池于是被堵在门口。等待时间不计入读、锁、日志、排序任何一类所以在MON_GET_PKG_CACHE_STMT里表现为“等待占比 95% 但不知道在等什么”。并发一上来等待链越拉越长ACTIVE SESSION 自然冲高。这也解释了为什么优化 SQL、建索引都没用——瓶颈不在语句本身而在一个被忽视的共享资源上。5. 避坑清单这类问题最容易踩的五个坑现象db2pd -latches单次执行显示一切正常误判没有 LATCH 问题。原因LATCH 等待只持续几秒单次采样命中率极低。 解决用db2pd -latches -rep 2持续采样在 ACTIVE SESSION 高的窗口内跑至少观察几分钟。现象重启数据库实例后TEMPSPACE1 大小没变。原因实例重启不触发数据库级临时表空间清理。 解决必须db2 deactivate db再db2 activate db执行前先force applications all。现象看到MON_GET_PKG_CACHE_STMT里等待占比 95%却查不出等的是什么。原因默认字段只覆盖读、锁、日志、排序LATCH 等待不在其中。 解决把TOTAL_EXTENDED_LATCH_WAIT_TIME加进查询字段再结合db2pd -latches交叉验证。现象生产库上收集 STACK 后业务更慢了。原因db2pd -stack all会挂起 EDU 抓栈繁忙库上开销明显。 解决限定-rep次数、选低峰窗口、只对必要成员收集收集完立即停止。现象只盯着慢 SQL 优化改完索引问题依旧。原因慢是结果不是原因共享资源竞争会让所有用到临时表的语句一起变慢。 解决先看 ACTIVE SESSION 状态分布和时间占比确认是语句自身慢还是等资源再决定优化方向。6. 快速定位同类问题的排查顺序与验证手法遇到“指标全绿但系统就是慢”的 DB2 性能问题我现在的固定顺序是先看 ACTIVE SESSION 的状态分布确认是执行中还是等锁排队再用MON_GET_PKG_CACHE_STMT算时间占比把TOTAL_EXTENDED_LATCH_WAIT_TIME一起带上等待占比高但读锁日志排序都低基本就指向 LATCH接着db2pd -latches -rep 2持续采样找 Holder 和 Waiter 的 EDU然后db2pd -stack all dumpdir... -rep 3 10抓栈grep LATCH 名用analyzestack汇总最后顺着栈里的函数名找被访问的对象这次就落到了表空间快照和临时表空间上。验证环节有个很实用的小实验用db2trc在特定函数入口挂起实例把“表空间快照持有 pool_table_latch”这件事坐实# 终端一连库后开启跟踪命中指定函数入口时挂起实例 db2 connect to dbname db2trc on -crash DB2.SQLB.sqlbSMSGetContainerPages.entry -suspend # 终端二触发一次表空间信息查询观察是否被挂起 db2 LIST TABLESPACES SHOW DETAIL # 终端一恢复运行并关闭跟踪 db2trc resume db2trc off db2trc fmt db2trc.dmp db2trc.txt-crash指定命中即挂起的函数入口-suspend让实例暂停这样能从容收集现场resume恢复、off关闭、fmt把二进制 dump 转成可读文本。这个手法适合在测试环境复现验证生产上慎用。几个关键指标的判读可以对照下表观察项正常表现异常信号下一步ACTIVE SESSIONOLTP 个位数到二三十突然频繁冲到 50 以上看状态分布等待时间占比读、锁、日志、排序分布合理总等待高但各项都低查 LATCH 等待TEMPSPACE1 大小很小接近 0数 GB 且 Used 等于 Total查 TDA 文件LIST TABLESPACES秒级返回执行要好几秒表空间对象异常从那以后我每次接手一个库第一件事就是把 TEMPSPACE1 的大小和 TDA 文件数量加进日常巡检而不是等业务喊慢才去看。临时表空间这种平时没人关心的对象出问题时恰恰最难联想。希望这些排查顺序能帮你在下一次“指标全绿”的故障里少绕几圈。本文还有配套的精品资源点击获取