简介一份面向DB2数据库运维与性能调优人员的实战案例文档由IBM认证高级DBA整理针对系统临时表空间TEMPSPACE1异常膨胀导致SQL语句执行时间显著变长的问题给出完整诊断与解决流程。案例源自某银行真实故障当时数据库ACTIVE SESSION数量频繁超过50常规CPU、内存、I/O、缓冲池命中率检查均无异常最终定位到临时表空间高达10GB。文档详细解释了临时表空间过大如何引发磁盘I/O下降、内存压力、LATCH竞争和过多磁盘排序并逐步演示了利用db2top观察活动会话、使用db2pd分析LATCH等待、收集SQL执行堆栈、借助db2trc工具在阈值处暂停实例等排障手段最后通过优化SQL、调整排序相关参数及清理临时对象恢复系统。资源包内含1个doc文档文件大小607KB内容结构清晰按现象、根因、工具、解决方案层层递进既讲原理又给命令适合正在处理类似性能问题或希望体系化学习DB2排障方法的初中级DBA参考。目前已有5899人学习该案例细节丰富对实际工作有较高参考意义。1. 系统变慢却找不到原因DB2 临时表空间问题从哪开始查DB2 系统临时表空间过大引发的性能问题听起来是个冷门话题但真正遇到的时候会让人相当头大。我接手过一个某银行核心业务系统的案例某天开始业务方陆续反馈系统响应变慢数据库监控频繁抓到 ACTIVE SESSION 数量超过 50而正常时这个数字长期在 10 以内。按标准流程查了 CPU、内存、I/O、锁等待、缓冲池命中率全部正常执行时间长的 SQL 也优化了几条性能却没有明显改观。最后发现罪魁祸首是平时几乎没人关注的系统临时表空间 TEMPSPACE1——它悄悄涨到了 10G。这篇文章就是把完整的排查思路和工具链拆开讲清楚适合做数据库运维、性能调优方向毕设或课设的同学也适合遇到类似生产问题的从业者直接参考。2. 常规性能排查流程为什么 CPU、内存、I/O 全查了一遍还是没头绪2.1 快速排除法先确认不是服务器资源问题遇到数据库性能下降第一反应是看服务器资源。这里有一个非常实用的排查顺序CPU 利用率、操作系统内存使用量、缓冲池命中率、锁等待、物理读写时间。通过 db2top 连上数据库后默认界面右下角有四个关键指标AvgPRdTime平均物理读时间、AvgDRdTime平均直接读时间、AvgPWrTime平均物理写时间、AvgDWrTime平均直接写时间四个值都在正常范围说明 I/O 没有瓶颈。锁等待也是同样的逻辑。db2top 主界面直接能看到有没有 LOCK WAIT当时结果是没有。缓冲池命中率正常说明数据页从内存读取命中率高问题不在数据访问路径上。这个快速排除法的核心思想是有顺序地排除掉最常见、最容易定位的瓶颈把排查范围一步步缩小。有一个指标要特别留意Deadlocks 计数器当时显示 218这个数字看起来吓人但它是累计值并不是当前发生的死锁不影响性能判断。另外 SortOvf排序溢出为 10考虑到应用的 SQL 用了较多排序少量溢出可以接受先不当成重点。我一般会把这些关键指标抄在一张纸上快速排除完就能判断出问题大概在哪个方向。2.2 抓取执行时间长的 SQLdb2top 和 MON_CURRENT_SQL 的两种思路确认完服务器资源没问题下一步就是找慢 SQL。db2top 提供了一个很方便的入口进入动态 SQL 缓存页面按平均执行时间降序排列。当时看到前几条 SQL 的平均执行时间接近 4 秒但执行次数都只有几次。这个现象本身就很有信息量——应用程序没有使用变量绑定直接拼接 SQL 导致每条语句对 DB2 来说都是新的所以每条 SQL 的执行次数少、但每次都很慢。这里的坑在于平均执行时间长的 SQL 不一定就是当前 ACTIVE SESSION 高的直接原因因为还有更多 SQL 正在执行中db2top 看不到。这时候需要换 MON_CURRENT_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 DESCMON_CURRENT_SQL 的特点是快照当前正在执行的语句需要多跑几次才能捕捉到 ACTIVE SESSION 高发的时刻。当时查到的结果是接近 30 条 SQL 正在执行而且都是 UOW Executing 状态事务正在执行语句不是锁等待、不是编译状态说明这些语句确实是在干活但干得很慢这进一步确认了问题出在语句执行本身而不是外部阻塞。2.3 用 MON_GET_PKG_CACHE_STMT 看等待时间分布拿到慢 SQL 列表之后下一个问题是一个语句执行了 4 秒这 4 秒花在哪里是读数据花时间、等锁花时间、写日志花时间、还是排序花时间MON_GET_PKG_CACHE_STMT 表函数能返回这个信息。我用它计算了各项等待时间在总执行时间中的占比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查询结果非常有价值平均执行时间 2~6 秒的语句里有 5 条 SQL 的 PCT_WAIT等待时间占比超过了 95%也就是说这几条语句几乎整个执行过程都在等待。但等待的不是读 I/O、不是锁、不是日志、也不是排序——这四个维度的占比都很低。这个结果把问题指向了一个之前没有考虑过的方向在 MON_GET_PKG_CACHE_STMT 的结果里增加了 TOTAL_EXTENDED_LATCH_WAIT_TIME 字段之后才发现这几条语句的等待时间几乎全部花在 LATCH 上。到这一步常规排查已经做完了结论是明确的数据库确实有性能问题有 SQL 在等待 LATCH但 LATCH 是 DB2 内部的机制没有文档可以直接查到它的含义。同时我也尝试优化了几条有全表扫描的 SQL建了索引确实有效果但逻辑复杂的长 SQL 并不是最近才上线的以前执行就不慢为什么现在突然慢了这时候我已经意识到方向可能不对必须停下来重新思考——SQL 优化是症状解不是原因解。3. LATCH 分析实战从 db2pd 到 STACK 收集再到 analyzestack3.1 pool_table_latch 的发现db2pd -latches 持续观察LATCH 是 DB2 内部的一种轻量锁机制它不像 LOCK 那样有文档和视图可以直接查询作用是为了保证多线程访问内部共享资源时的一致性。理解 LATCH 的一个直观例子是预取队列用户请求的线程向预取队列增加任务预取线程从队列读取任务两边同时访问队列就需要先获取 LATCH拿不到就等着。它不是数据库层的锁所以常规的锁等待监控完全看不到。第一次检查 LATCH 用的是命令db2pd -latches当时结果正常没什么异常。但问题现象是间歇性出现的单次检查很可能错过。于是改用持续刷新模式db2pd -latches -rep 2这个命令每隔 2 秒输出一次 LATCH 快照一直持续刷新。等了一段时间之后发现重大异常每隔一段时间就会出现大量 LATCH 等待其中 EDU ID 11665 持有名为 pool_table_latch 的 LATCH而另外 23 个 EDU 都在等待这个 LATCH 释放。这意味着一瞬间有至少 24 个活动会话被一个 LATCH 卡住正好对应了监控系统看到的 ACTIVE SESSION 数量从个位数飙到几十的异常。3.2 收集 STACK 信息db2pd -stack 的用法与生产环境注意点知道了是哪个 LATCH但不知道持有 LATCH 的线程在做什么。LATCH 等待持续时间很短几秒钟就消失用 db2pd 看不到持有者当前的操作。这时候需要收集 STACK 信息——通过把每个 EDU 当前正在执行的函数调用栈抓下来反推它在做什么操作。cd /tmp mkdir stack0531 db2pd -stack all dumpdir/tmp/stack0531 -rep 3 10这里的参数解释一下-stack all表示收集所有 EDU 的堆栈dumpdir指定输出目录-rep 3 10表示每隔 3 秒收集一次、连续收集 10 次。因为 LATCH 等待不是持续存在的需要高频、持续地抓才能碰到现场。收集完成后检查文件里是否包含 pool_table_latchgrep pool_table_latch /tmp/stack0531/*stack.txt结果成功捕捉到了现场——有的 STACK 文件里显示某 EDU 正在 Holding 这个 LATCH有的显示正在 Waiting。需要注意 STACK 收集在生产环境有明显开销数据库越繁忙影响越大所以要控制收集时长和频率尤其在业务高峰期要慎重。3.3 解读 STACK从函数名反推操作类型拿到 STACK 文件后分析关键在函数名的字面意思和调用关系上。Holder EDU 的调用栈里有这样一条链路sqlbSnapshotTablespace - sqlbMonSMSGetContainerPages。从函数名可以推测这个 EDU 正在对表空间做快照监控具体在获取 SMS 表空间容器container的数据页信息。Waiter EDU 那边则是另一条链路sqldTableCreateTemp - sqldInternalCreateTable - sqlbGetSysTempDatapool结合sqldEvalDataPredevaluate predicate执行表达式求值来看可以判断这些等待的线程正在执行查询语句并且需要创建内部临时表正在向系统临时表空间申请数据页。两个 STACK 放在一起逻辑就很清楚了某个线程在对表空间做监控快照执行时间较长一直握着 pool_table_latch其他线程想要访问系统临时表空间的数据池必须拿到同一个 LATCH于是被集体阻塞。各线程的调用关系对比可以用一张表格梳理角色关键函数推测操作HoldersqlbSnapshotTablespace → sqlbMonSMSGetContainerPages对 SMS 表空间做快照读取容器页WaitersqldTableCreateTemp → sqlbGetSysTempDatapool创建内部临时表申请系统临时表空间数据池WaitersqldEvalDataPred → sqlsfetc → sqlriFetch执行查询并做表达式求值这里还有一个工具可以大大提升分析效率——DB2 自带的 Perl 脚本 analyzestack位置在 sqllib/pd 目录下。这个工具能把所有 STACK 文件中相同的堆栈汇总统计还能自动做 LATCH 分析用法如下perl ~/sqllib/pd/analyzestack -d 50 -i /tmp/stack -l其中-d 50指定对比堆栈时比较的函数个数-i指定 STACK 文件目录-l表示执行 LATCH 分析。它会生成 LatchAnalysis.out 和 StackAnalysis.out 两个结果文件。这个脚本在 DB2 知识中心没有任何文档说明属于内部工具的外流但实战中非常好用尤其当一次收集了几十个 STACK 文件、人工一个个翻的时候。4. 根因确认与解决方案TEMPSPACE1 暴涨 10G 的完整链条4.1 发现 LIST TABLESPACES 变慢临时表空间已经失控分析到这一步逻辑推理指向了表空间监控和临时表空间的关联。回到数据库层面去检查表空间状态执行db2 LIST TABLESPACES SHOW DETAIL时发现一个反常现象这个平时秒回的指令现在需要好几秒才能执行完。这说明表空间监控相关的操作已经异常缓慢和 STACK 分析中看到的 sqlbSnapshotTablespace 变慢完全吻合。进一步检查发现TEMPSPACE1 居然有 10G 大小。正常情况下系统临时表空间应该很小只有当前正在使用的临时数据才会占空间执行完就释放。这里的情况明显不对——空间异常膨胀而且膨胀发生在一个特定的时间点。回头看监控系统的历史曲线TEMPSPACE1 的大小从接近 0 瞬间暴涨到 10G和业务高峰期大量 SQL 使用临时表的时间点吻合。关键信息在容器目录下能看到有大量 TDA 文件被遗留了下来每个文件大小 1MB 左右命名类似 SQL04313.MEMBER0000.TDA。这些文件是使用系统临时表空间时创建的临时数据文件正常情况下用完会删除但它们没有消失。4.2 TDA 文件遗留机制Technote 背后的设计行为针对 TDA 文件不删除的问题IBM 网站上有一篇 Technote技术说明记录了这个现象Tablespace TDA files not being deleted and are filling up the filesystem说明 TDA 文件可能会遗留在 SMS 临时表空间中并占满文件系统。特别注意这篇 Technote 的结论这是Work as Design也就是说是产品设计行为不是 bug。那 TDA 文件为什么会遗留结合这次案例的具体情况来分析在那个触发的时间点数据库的 ACTIVE SESSION 数量瞬间达到 800 多大量 SQL 同时申请系统临时表空间来创建内部临时表。这些 SQL 都执行成功了数据库也没有崩溃但临时表空间的清理机制没有跟上——部分 TDA 文件在语句执行结束后没有被正常删除。当这类遗留文件累积到一定量级临时表空间被撑到 10G 之后后续所有涉及表空间监控、临时表分配的操作都会变慢。这里有个对排查很有用的细节临时表空间用的是 System managed spaceSMS管理方式每个容器是一个目录TDA 文件直接躺在文件系统里。所以通过ls -al看容器目录就能直观看到遗留文件的规模。这比只盯着表空间总大小更符合排查习惯。4.3 解决步骤deactivate/activate 释放临时表空间技术说明给的解决方案很明确安排维护窗口对数据库执行 deactivate/activate 操作。这里要强调一个容易翻车的细节——必须对数据库做 deactivate 然后 activate只重启数据库实例无法释放这些临时表空间。原因是 TDA 文件与数据库的会话状态绑定deactivate 会强制断开所有连接并清理数据库级的状态而实例重启并不会触发这个清理逻辑。实际操作流程# 1. 在维护窗口确认无业务连接 db2 force applications all # 2. deactivate 数据库 db2 deactivate db sample # 3. 重新激活数据库 db2 activate db sample执行完成后再次查看 LIST TABLESPACES SHOW DETAIL速度恢复正常TEMPSPACE1 的空间被释放。持续监控一段时间后确认ACTIVE SESSION 数量回落到个位数那些原本执行 3~6 秒的 SQL 恢复正常性能问题彻底消失。这个解决方案的成本很低关键是在维护窗口执行、并且操作对象是数据库而不是实例。到这一步整个问题的完整链条已经闭环了某时间点 ACTIVE SESSION 暴涨 → 大量 SQL 并发使用系统临时表空间 → 高并发下部分 TDA 文件遗留 → 临时表空间膨胀到 10G → 表空间监控操作snapshot需要扫描大量容器页 → 持有 pool_table_latch 时间长 → 其他需要分配临时表空间的 SQL 等待 LATCH → 业务 SQL 整体变慢 → ACTIVE SESSION 堆积。5. 常见问题避坑清单这次排查中最容易翻车的 5 个点5.1 坑一临时表空间释放靠重启实例结果白做了现象按惯例重启了数据库实例启动后发现 TEMPSPACE1 还是 10GLIST TABLESPACES 依然很慢。原因TDA 文件与数据库级状态绑定实例重启不会清理这些遗留文件只有 deactivate 数据库才能触发清理逻辑。解决执行db2 deactivate db再db2 activate db必要时先db2 force applications all断开所有连接。这次案例里我一开始也惯性思维想重启实例了后来查了 Technote 才纠正方向。5.2 坑二db2pd -latches 只看一次错过间歇性 LATCH 等待现象第一次执行db2pd -latches结果显示正常就以为问题不在 LATCH 方向。原因这类 LATCH 竞争是间歇性的等待几秒钟就消失单次快照大概率抓不到现场。解决用-rep 2持续刷新或者结合 STACK 收集机制做多次采样。遇到间歇性性能问题所有监控命令都建议加持续刷新参数不能一查了之。这是我这次排查里得到的重要经验。5.3 坑三被慢 SQL 带偏方向盲目优化不止现象看到有几条 SQL 有全表扫描立刻建索引优化又花了大量时间去梳理那些逻辑复杂的长 SQL。原因看到慢 SQL 就默认是 SQL 本身的问题忽略了这些 SQL 以前也是这么执行为什么现在突然变慢这个关键问题。解决优化 SQL 之前先问三个问题这条 SQL 是最近才出现的吗执行计划最近有变化吗有没有可能不是 SQL 的问题而是环境变化了这次案例里那些长 SQL 执行时间变长是因为等待 LATCH即使 SQL 本身优化得再好LATCH 竞争不解决还是慢。5.4 坑四低估监控代理的 SNAPSHOT 操作对生产的影响现象监控系统每隔一段时间自动收集表空间信息触发 sqlbSnapshotTablespace 操作这个操作在临时表空间变大的情况下执行时间大幅上升。原因监控代理的采集频率是固定的当被采集的对象临时表空间异常膨胀时采集操作本身就成了性能瓶颈的触发器。解决临时表空间异常时需要关注监控采集的影响必要时临时调低表空间监控的频率。这个因素很容易被忽略但它在这次的 LATCH 持有链路里是关键一环。5.5 坑五临时表空间大小历史曲线平时没人看现象监控系统里有表空间大小历史曲线但平时根本没有关注直到出问题才去看才发现它已经在某个时间点暴涨过。原因系统临时表空间平时变化小既不影响业务也不触发告警运维人员的注意力集中在数据表空间和日志空间上。解决把系统临时表空间的大小纳入日常巡检清单建立基线——比如正常范围是 1G 以内超过 2G 就告警。这次案例里如果早发现 TEMPSPACE1 的异常增长完全可以在影响业务之前提前做 deactivate/activate。6. 验收与复盘如何确认问题真正解决并沉淀成排查手册6.1 验收标准从指标恢复到流程闭环问题处理完不是看起来没事了就结束需要有明确的验收指标。这次案例我按三个维度验收。第一是性能指标ACTIVE SESSION 数量回落并稳定在个位数db2top 中已经看不到 UOW Executing 状态的堆积第二是具体操作响应db2 LIST TABLESPACES SHOW DETAIL恢复秒回说明表空间监控操作不再被 TDA 文件拖累第三是临时表空间水位TEMPSPACE1 从 10G 释放到正常范围并且记录清理后的容器文件数量作为新的基线。持续观测了多个业务周期确认慢 SQL 不再出现才算是完整闭环。6.2 复盘动作把排查过程固化为标准操作解决之后要做复盘把这次排查中的关键判断点记录下来。我整理的排查卡片包含四条遇到 ACTIVE SESSION 异常增高先看 SQL 时间分布是等待还是执行等待类别排除 read、lock、log、sort 之后要检查 LATCH 等待LATCH 持续刷新观察结合 STACK 收集确定持有者操作表空间监控操作异常变慢时优先检查系统临时表空间是否有异常遗留文件。这份卡片后来直接沉淀成了我们团队的临时表空间专项检查手册。整个排查过程最核心的收获是性能问题不要只盯着 SQL 本身数据库内部机制的竞争往往藏在意想不到的地方。从那以后我每次遇到类似的 SQL 变慢问题都会强制走一遍完整链路——先确认资源层、再确认语句层、然后看等待事件、最后关联底层存储和临时空间状态避免在 SQL 优化上过多纠缠而错过了真正的系统性问题。希望这套分析思路和工具链能帮到你少走一些弯路。本文还有配套的精品资源点击获取