简介这份DOC文档面向DB2数据库DBA与运维人员取材于某银行真实性能案例聚焦系统临时表空间TEMPSPACE1异常增大后引发的SQL执行变慢、活动会话升高等问题适合有一定DB2基础、需要提升性能诊断能力的读者参考。压缩包内仅含1个doc文件大小约607KB内容集中便于直接查阅。文档把现象确认、常规指标排查、LATCH等待分析、STACK收集与db2trc跟踪等线索串联起来并讨论SQL优化、参数调整和临时对象清理等处理方向可帮助读者理解临时表空间过大如何逐层影响查询性能。已有5903人学习对希望积累银行级DB2排错思路和性能分析工具使用经验的DBA具有参考价值。1. 从一次“指标全绿”的慢查询说起被忽略的 DB2 系统临时表空间一家金融机构的 DB2 数据库某天开始变慢业务反馈集中在“提交变慢”活动会话数从常年个位数频繁冲到 30 以上。运维按惯例把 CPU、内存、I/O、缓冲池命中率、锁等待过了一遍每项都在正常区间甚至可以说漂亮。这类排查最难受的地方就在这里常规指标全绿问题却真实存在。等把执行时间最长的语句拉出来逐条比对才发现其中一部分语句的时间根本没花在读数据、等锁或者排序上而是花在 LATCH 等待上再往下追真正卡住一切的是那个常年没人关注过的 DB2 系统临时表空间它已经涨到 10G。这套从活动会话一路挖到表空间的路径适合正在处理数据库性能问题的运维和开发也适合把它当成一份完整性能分析案例来做方案设计的同学。2. 排查链路设计从活动会话到时间分布的取证顺序性能排查真正难的地方通常不在于缺工具而在于工具都用了一遍结论却是散的。这次的处理过程之所以能收敛是因为把六步取证串成了一条链先确认现象是数据库侧的再排除资源问题再抓语句再拆时间分布最后才允许自己去动 SQL。顺序反了很容易在第 4 步就一头扎进去优化语句把时间耗在错误方向上。2.1 先把活动会话拆到状态级业务人员说“慢”只能当线索不能当结论。真正让人开始紧张的是活动会话数。正常联机事务处理系统的活动会话数一般在 10 个以内忙一些的系统二三十个也属正常超过这个量级就该考虑扩容了但如果一个平时只有几个会话的系统突然频繁冲到几十个先别急着买硬件先看状态构成。活动会话的准确定义是“正在处理 SQL 请求的会话”从编译到执行完成中间会经历 Compiling、UOW Executing、Lock Waiting 等状态COMMIT 也是一个语句执行期间能看到 Commit Active启用并发限制后排队中的会话是 Queued。这些状态都计入活动会话数而一个事务里执行完一条语句、下一条还没开始应用处于 UOW Waiting不计入。# 连接目标数据库默认以 2 秒为间隔持续刷新 db2top -d OAPDB # 进入交互界面后 # d —— 查看 ActSess 汇总观察总量趋势 # l —— 进入 SESSIONS 页面逐个看会话处于什么状态 # D —— 查看 PACKAGE CACHE 中的动态 SQL # z —— 在 SQL 页面触发排序输入 4 表示按平均执行时间倒序db2top的-d后面跟库名进去后靠单字母指令切换视图。这次故障中活动会话全部是 UOW Executing 状态——没有大面积的锁等待说明会话不是被应用层或锁卡住而是自己算得慢。这一步的价值在于把“数据库慢”这个模糊判断收窄成“一批语句在并行执行且耗时异常”。同时也要习惯用db2 LIST APPLICATIONS SHOW DETAIL做交叉核对它给出的状态字段与 db2top 口径一致适合在事后复盘时截图留证。2.2 快速排除法哪些指标必须看哪些数据可以直接忽略快速排除法的目的是尽快把“资源不够”这个可能性划掉而不是把所有指标都研究一遍。CPU 利用率和操作系统内存用量在正常范围缓冲池命中率正常没有锁等待四项平均读写时间——AvgPRdTime、AvgDRdTime、AvgPWrTime、AvgDWrTime——也都在正常区间基本可以判定 I/O 不是瓶颈。这里有两个容易误判的数据。一是 Deadlocks 显示 218看着吓人但这是累计值从实例启动算起不代表当前有死锁在影响性能二是 SortOvf排序溢出为 10偏高但应用本身排序语句较多有溢出属于可解释范围不足以解释三十多个会话同时变慢。把这两个数当成“重点怀疑对象”是很多排查走弯路的第一步。还有一点既然已经确认会话状态在数据库内部网络故障、应用服务器负载这类方向就可以直接排除不必再花时间。排除法的意义是缩小范围不是面面俱到。2.3 抓语句缓存里的和执行窗口里的确认是语句慢之后要把具体的语句捞出来。db2top 的D视图能看到缓存中的动态 SQL再输入z后会提示按哪一列排序输入 4 即按平均执行时间倒序。这次看到最慢的几条平均执行时间接近 4 秒但执行次数都很有限——典型的应用没走变量绑定、直接拼 SQL每条语句对数据库来说都是全新的缓存里自然积累不起来。针对活动会话数高的情况MON_CURRENT_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, -- UOW Executing 等 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是语句从开始执行到查询时刻的秒数不是完整执行时间所以抓出来的值一定比语句真实耗时短这属于正常现象。另一个注意点是活动会话数高这个现象不是持续存在的单次查询很可能抓不全通常需要连跑几次等峰值出现时再取结果。这次抓到接近 30 条和监控上的活动会话数对得上。2.4 拆时间分布别只看总耗时要看时间花在哪知道哪些语句慢还不够关键是搞清慢在哪里。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)) AS T WHERE NUM_EXECUTIONS 1 AND STMT_EXEC_TIME 0;STMT_EXEC_TIME是该语句累计执行时间除以NUM_EXECUTIONS得到平均耗时AVG_EXEC_TIME这一列才是可以直接和其他语句横向比较的值。表函数最后一个参数-2表示取当前成员上所有可用数据前三个NULL分别对应 activity、package、member 过滤条件不筛就全给。结果里出现了很关键的一组5 条以 with 开头的语句平均执行时间 2 到 6 秒不等PCT_WAIT全部在 95% 以上——也就是说时间几乎全花在等待上而PCT_READ、PCT_LOCK、PCT_LOG、PCT_SORT都很低。等待的不是读、不是锁、不是日志、不是排序那就只剩内部争用。在查询里再加一个TOTAL_EXTENDED_LATCH_WAIT_TIME答案就浮出来了这几条语句在等 LATCH。另外 5 条慢语句是调用存储过程的从这个视图看不出内部分布需要另找入口。2.5 什么时候该停手三个“没有变”帮你否掉优化 SQL 的方向拿到结果后常规动作是优化。当时也确实动了手几个全表扫描的语句加索引就解决了然后开始梳理那些又长又复杂的语句。但这里必须踩一脚刹车。那些复杂长 SQL 不是最近才上线的应用程序没有变更数据库也没有变更凭什么一个月前它跑得好好的现在突然慢加索引能解决的那几个语句只能解释个别会话变慢解释不了三十多个会话同时 UOW Executing。真正的问题一定是一个“最近才变化、又被所有人忽略”的因素。这三个“没有变”——没有应用变更、没有数据库变更、慢语句逻辑没变——是判断方向的硬约束。反过来推变化只能发生在数据库的运行状态里某个对象的大小、某个内部结构的分布、某个后台动作的代价。顺着这个思路往下走才轮到 LATCH 和 STACK 出场。3. LATCH 与 STACK 取证把内部函数名翻译成可判断的操作3.1 pool_table_latch 是什么从名字到机制的理解路径产品文档里几乎找不到对 LATCH 的科普性说明只有若干监控元素能间接反映它的存在比如前面提到的TOTAL_EXTENDED_LATCH_WAIT_TIME。可以这么理解LATCH 是数据库为保证多线程访问内部共享结构一致性而设的轻量锁。举个例子内存里有个预取队列处理用户请求的线程往里加任务多个预取线程不停从里面取任务为了协调这两方对队列的访问每次访问前都要先拿到 LATCH拿不到的就得等。所以 LATCH 本身不是故障它是正常机制只有当某个持有者拿着 LATCH 干了一件特别久的事后面排队的才会成串堵住。这次的情况正是如此db2pd -latches第一次跑没看到异常因为 LATCH 等待几秒就消散了单次采样很容易错过。改成持续采样# 每 2 秒刷新一次 LATCH 情况并持续输出便于捕捉瞬时争用 db2pd -d OAPDB -latches -rep 2-rep 2是重复间隔秒数不加就只能看一瞬间的快照这类瞬时现象必须靠连续采样。这次抓到了EDU ID 11665 持有类型为pool_table_latch的 LATCH同时 23 个 EDU 在等它。23 个等待者加上持有者活动会话数至少 24和监控曲线对得上。到这一步只确认了“有争用”还没确认“谁在争、争的是什么东西”。LATCH 的名字只有字面意义pool_table指什么、为什么会持有这么久靠猜没用。3.2 收集 STACK 的命令参数与资源代价既然瞬时状态抓不住现场就换个思路在抓现场的同时把每个线程当时的调用栈一起存下来。db2pd的-stack正是干这个的它在收集栈的同时也会记录该 EDU 当时的 LATCH 和锁信息。# 连续收集 10 次栈信息每次间隔 3 秒输出到指定目录 mkdir -p /tmp/stack0531 db2pd -d OAPDB -stack all dumpdir/tmp/stack0531 -rep 3 10-stack all表示收集所有 EDUdumpdir指定输出目录不指定就默认落在诊断目录里事后翻找很麻烦-rep 3 10是每 3 秒一次、共 10 次之所以要多次是因为 LATCH 争用不是常态单次采集大概率落空。必须强调代价-stack会挂起线程去抓栈在生产库上是一次实打实的扰动库越忙影响越明显。我一般的做法是控制次数、挑相对低峰、提前和业务打招呼绝不在高峰期做长时间采集。抓完之后立刻确认有没有命中cd /tmp/stack0531 grep -l pool_table_latch *.stack.txt | head命中后直接看文件内容能找到Holding Latch type和Waiting on latch type两类记录地址相同说明确实是同一把 LATCH。3.3 读 STACK靠函数名与调用关系反推操作类型栈里全是内部函数名第一眼确实劝退但不需要读懂实现只要能判断“这是在干什么活”。方法是看函数名、看调用链的层次再结合对数据库架构的基本认知去猜。持有者的栈里出现了sqlbSnapshotTablespace调用sqlbMonSMSGetContainerPages再往下是sqlofindn2、__xstat64这类文件系统调用。字面翻译对表空间做快照具体动作是在监控 SMS 表空间时枚举容器、获取数据页信息__xstat64说明它真的在跟操作系统要文件状态。这就解释了一个关键点——它在做一件跟文件数量、文件大小强相关的事。等待者的栈里是sqldTableCreateTemp调用sqldInternalCreateTable再调用sqlbGetSysTempDatapool同一条链上还有sqldEvalDataPred、sqlriFetch。这条链的含义很明确执行一条带谓词的查询过程中需要创建内部临时表于是去申请系统临时表空间。两条栈拼起来因果关系就成立了某个后台动作在对表空间做快照耗时过长长时间持有pool_table_latch而查询语句要建临时表、要用系统临时表空间必须拿同一把 LATCH于是成串排队。谁在频繁做快照答案是数据库监控系统部署在服务器上的采集代理它按周期采集表空间信息。到这里还剩最后一环快照为什么慢。快照慢只能是因为它要处理的对象太大——而那个对象正是 10G 的 TEMPSPACE1。3.4 analyzestack批量汇总栈文件的小工具手工 grep 加肉眼比对在十个文件内还行几十上百个就撑不住了。产品安装目录的sqllib/pd下有个 Perl 脚本analyzestack能把目录里所有栈文件按相同栈归并统计顺带做 LATCH 分析。# -d 指定比对时比较的函数层数-i 指定栈文件目录-l 开启 LATCH 分析 perl ~/sqllib/pd/analyzestack -d 50 -i /tmp/stack -l-d 50是比较深度值越大越严格栈要前 50 层都对上才算同一类-i可以是目录也可以是若干个具体文件-l打开 LATCH 分析。跑完会在当前目录生成LatchAnalysis.out和StackAnalysis.out两个文件前者列出持有者与等待者的配对关系后者是按栈归并后的统计哪个栈出现次数最多一目了然。需要提醒的是这个脚本在文件头自己写明了按现状提供、不做担保属于典型的“能用但别指望官方支持”的工具。生产环境用之前先在测试环境跑一遍确认输出格式别等到出事现场才发现参数不对。它解决的是归并统计这类体力活判断还得自己做。4. 根因确认与处置TEMPSPACE1 从 10G 回落的两步动作4.1 表空间体检查看动作本身变慢就是信号顺着“对象太大”这条线去查表空间第一个异常不是数值而是速度db2 LIST TABLESPACES SHOW DETAIL要跑好几秒才出结果。一个查看元数据的命令慢成这样本身就说明被查看的对象数量或规模已经失控。# 查看表空间明细重点关注 Total/Used pages 与容器数量 db2 LIST TABLESPACES SHOW DETAIL结果里 TEMPSPACE1 的信息是这样的类型为系统管理空间SMS内容为系统临时数据总页数 354661页大小 32768 字节区大小 32 页预取大小 768 页容器数 4已用页等于总页数。粗算一下就是十个 GB 级别。正常情况下系统临时表空间应该非常小涨到这个体量说明里面堆了大量本该被回收的东西。再去看容器目录问题更直观# 统计临时表空间容器目录下遗留的 TDA 文件数量与占用 ls -al /db2data/.../NODE0000/OAP/T0000001/C0000003.TMP | head find /db2data/.../NODE0000/OAP/T0000001 -name *.TDA | wc -l目录里是一堆SQL04313.MEMBER0000.TDA、SQL04314.MEMBER0000.TDA这样的文件每个 1MB 左右时间戳集中在同一个时间点。这些是临时数据文件按设计应该在用完后被清理现在全都留着。同时翻监控系统的表空间大小历史曲线能看到某个时刻 TEMPSPACE1 从 0 直接跳到 10G。时间点非常集中说明不是缓慢增长而是一次事件造成。4.2 TDA 文件残留一个“按设计如此”的行为查到这一步去官方技术支持文档里搜能搜到一条明确说明SMS 临时表空间中的 TDA 文件可能不被删除从而占满文件系统。关键在于它的定性——Work as Design产品设计如此不是缺陷。这个定性会直接改变处置思路。既然不是缺陷就别指望打补丁或者升级能解决只能从运行态入手想办法让这些文件被释放并且在制度上防止它再次堆积。至于为什么会堆积回溯到那个时间点就清楚了当时出现了一次并发尖峰活动会话一度冲到 800 多个大量语句同时使用临时表空间。数据库没崩语句也都执行成功了但留下了一堆 TDA 文件成了后面踩到的雷。这类雷的特点是当时完全无感几周后才以“系统变慢”的形式爆发排查时极容易和业务变化、应用变更混淆。4.3 处置动作与生效边界为什么重启实例不管用处置本身只有两步但边界必须说清楚# 先停用数据库再激活强制释放残留的临时表空间 db2 deactivate db OAPDB db2 activate db OAPDB # 验证表空间信息应立即恢复正常返回速度 db2 LIST TABLESPACES SHOW DETAIL必须是 deactivate/activate 这一对操作。只重启实例db2stop / db2start不管用因为实例重启不一定触发数据库级的清理动作残留文件会原样留着。这一点如果没搞清楚很容易在维护窗口里白折腾一轮然后得出“办法无效”的错误结论。激活后立刻验证表空间查询恢复正常速度TEMPSPACE1 回到很小的体量活动会话数回落到个位数之前那批 3 到 6 秒的语句恢复到正常耗时。再挂上监控观察一段时间确认曲线稳定处置才算完成。4.4 预防方案监控、容量与变更窗口的三道闸问题解决之后真正有价值的是把这类问题挡在下次之前。我一般从三个地方下手。第一道是监控口径。原来只监控表空间使用率对系统临时表空间基本不看因为“它反正会自己回收”。现在需要把 TEMPSPACE1 的大小和容器目录下的 TDA 文件数量都纳入采集设阈值告警——正常应该维持在很小的量级一旦出现数量级跃升就是异常。第二道是容量与类型的选择。系统临时表空间不是越大越好SMS 类型下容器文件数量会随并发峰值增长快照类操作遍历容器的成本也随之上升。规划时按真实峰值估算留合理余量即可不要图省事直接给一个巨大的初始值。第三道是变更窗口。deactivate/activate 会断开所有连接必须放在维护窗口做并且要提前确认没有长事务在跑。把“并发尖峰之后检查临时表空间残留”写进重大活动后的巡检清单比事后救火便宜得多。4.5 用 db2trc 做一次小验证结论是否可靠最好能复现。db2trc可以抓取数据库运行中的函数调用还支持在指定函数入口处暂停实例让人从容取证。# 打开跟踪在指定函数入口处挂起便于观察该函数被调用的时机 db2 CONNECT TO OAPDB db2trc on -crash DB2.SQLB.sqlbSMSGetContainerPages.entry -suspend # 复现表空间信息采集动作观察是否精确停在该函数 db2trc off db2trc fmt db2trc.dmp db2trc.txt-crash后跟函数名限定跟踪范围-suspend让实例在命中该函数时暂停。这个小实验验证了两件事表空间信息查看确实会走到sqlbSMSGetContainerPages也就是那个要枚举容器页、持有pool_table_latch的动作同时对数据库监控采集与性能下降之间的关系有了确定性的证据不再是靠推断。5. 避坑清单这类性能问题最容易翻车的五个地方5.1 常规指标全绿就下“数据库没问题”的结论现象是 CPU、内存、I/O、缓冲池、锁等待全部正常于是排查转向应用或者网络来回折腾几天没结果。原因是这套指标只覆盖资源层不覆盖内部争用。LATCH 等待既不吃 CPU也不体现在锁等待上普通监控里根本看不见。解决办法是把“会话状态构成”和“语句时间分布”作为第二层检查固定下来。活动会话里如果 UOW Executing 占多数而资源层又是绿的就该往内部争用方向查先看TOTAL_EXTENDED_LATCH_WAIT_TIME这个字段成本很低。5.2 盯着累计值指标做决策看到 Deadlocks 两百多、SortOvf 有十个立刻判断是锁和排序问题动手调排序区、查死锁。原因是累计值只反映历史总量不能反映当前是否在发生。这两项在这次故障里都出现了但都不是主因。解决办法是养成区分“累计值”和“当前值”的习惯判断前先问一句这个数是什么时候开始累加的最近有没有增量。有增量再看没增量就放过。5.3 LATCH 采样一次没抓到就放弃db2pd -latches跑了一次输出一切正常于是认为 LATCH 没问题转向别的方向。原因是 LATCH 争用往往只持续几秒瞬时快照的命中率很低。这次的问题里等待会在几秒内消散。解决办法是改成带-rep的连续采样采样周期和持续时间按现象的出现频率定。这次用-rep 2连续观察才第一次抓到 23 个等待者。另外抓到的 LATCH 名称要记录下来作为后续找栈的检索关键词。5.4 生产库上长时间 dump 全量栈为了多抓点信息直接在业务高峰期跑长时间、多次数的-stack all结果数据库响应进一步恶化把性能问题变成了可用性问题。原因是抓栈会暂停线程线程越多、次数越密扰动越大。忙库上这个代价会放大。解决办法是三条纪律挑低峰窗口控制次数通常 10 次以内够用先小范围试一次确认能命中再决定是否加量。同时提前准备好dumpdir避免事后在一堆诊断文件里翻找。5.5 处置后用“感觉快了”验收做完 deactivate/activate问了句业务说好了就结单。过一段时间同样的问题再次出现又从头查一遍。原因是缺少可对比的量化口径也缺少防复发措施。并发尖峰是会再来的。解决办法是验收至少看四项活动会话数曲线是否回到基线、慢语句平均执行时间是否恢复、TEMPSPACE1 大小和 TDA 文件数是否归零、表空间查询耗时是否正常。同时把并发尖峰后的巡检动作固化下来别指望下次还能想起这次。6. 验收与答辩用五项指标证明问题真的解决了这套东西讲给别人听的时候最容易被打断的地方是“你怎么证明是临时表空间导致的而不是同时段别的因素”。证据链要经得起这个追问靠的不是结论本身而是三条独立线索指向同一个对象。第一条线索是会话状态活动会话数从个位数涨到三十以上且状态全部是执行中没有锁等待说明是算得慢而不是被卡住。第二条线索是语句时间分布部分语句 95% 以上的时间在等待而读、锁、日志、排序的占比都很低把范围压缩到内部争用。第三条线索是 LATCH 加栈持有者在做表空间快照、等待者在建临时表两者争同一把pool_table_latch而快照慢的原因是临时表空间有 10G。三条线索单独看都只是线索合起来才构成因果。验收口径建议做成一张对照表答辩时直接摆出来比口头描述有说服力验收项故障期表现恢复后目标采集方式活动会话数频繁超过 30峰值 800稳定在个位数db2top 的 ActSess 曲线慢语句平均执行时间2 到 6 秒回到百毫秒级MON_GET_PKG_CACHE_STMTLATCH 等待单次采样 23 个等待者无持续排队db2pd -latches -rep 2TEMPSPACE1 大小约 10G已用页等于总页维持在很小量级LIST TABLESPACES SHOW DETAIL容器目录 TDA 文件大量残留时间戳集中数量稳定不增长目录文件计数巡检答辩时还有一个加分点主动讲自己走错的那一段。加索引解决了几条全表扫描然后花了时间去优化那些又长又复杂的语句——方向是错的因为应用没变更、库没变更、语句逻辑也没变解释不了为什么现在才慢。把这段讲出来比只讲正确路径更能说明你理解了排查的边界在哪里。评审的人真正想确认的是你能不能在下一次遇到没见过的问题时靠证据而不是靠运气收敛。另外准备好两段可复现的命令现场问就现场给一段是db2pd -stack all dumpdir... -rep 3 10说明为什么要多次采集、代价在哪一段是db2 deactivate db加db2 activate db说明为什么只重启实例不行。这两处是最容易被追问细节的地方答得利索整份方案的可信度就立住了。从那以后我每次处理“指标全绿但系统就是慢”的问题都会强制走一遍活动会话状态拆分和语句时间分布这两步不管前面看起来多像应用问题——因为那次真的让我明白最贵的不是工具不会用而是方向错了还在努力。希望帮到你。本文还有配套的精品资源点击获取