简介这是一份面向DB2数据库运维DBA与性能调优学习者的真实案例文档源自某银行DB2系统的一次性能故障排查。当SQL执行时间变长、活动会话数异常攀升时常规的CPU、内存、I/O、锁等待与缓冲池命中率检查均未奏效最终锁定“罪魁祸首”为系统临时表空间TEMPSPACE1异常膨胀至10GB。文档完整还原了从现象确认到根因定位的分析路径并给出可落地的解决思路。资源包为单个doc文件约607KB内容涵盖LATCH分析方法、STACK收集与解读技巧以及用db2trc工具暂停实例等进阶手段对理解临时表空间过大如何层层拖慢查询性能颇具参考价值。目前已有5899人学习下载适合希望提升DB2性能诊断能力、积累真实排障经验的读者研读。1. DB2 临时表空间告急为什么你的 SQL 跑着跑着就卡死了一个工作日的上午业务反馈核心报表查询突然从 3 秒劣化到 90 秒监控大屏上数据库活跃会话数飙升应用端开始出现连接池耗尽。登录数据库服务器第一反应是看表空间使用率——用户表空间USERSPACE1水位正常但系统临时表空间TEMPSPACE1已经冲到 98%容器几乎被撑爆。这不是磁盘满了而是 DB2 在排序、哈希连接、重组等操作时把大量中间结果往临时表空间里灌灌到没有空闲页可用后续所有需要临时空间的 SQL 全部排队等待形成雪崩。DB2 系统临时表空间过大引发的性能问题本质是“临时空间被异常 SQL 或配置缺陷吃光”的连锁反应典型症状包括查询挂起、Latch 等待激增、日志里出现 SQL0952N 或 SQL1218N。这篇文章面向正在被 TEMPSPACE1 告警折磨的 DBA 和运维工程师从定位根因到参数调优把可复现的排查路径和止血手段讲清楚。2. 先搞清楚 DB2 临时表空间到底在干什么2.1 系统临时表空间与用户临时表空间的本质区别DB2 的临时表空间分两类很多人排查时混为一谈。系统临时表空间System Temporary Tablespace简称 STT由数据库管理器自动使用服务于排序、分组、哈希连接、索引重建、REORG 等内部操作你无法在里面手动建表。用户临时表空间User Temporary Tablespace简称 UTT则是给DECLARE GLOBAL TEMPORARY TABLE和已声明临时表用的需要显式创建。标题里说的“系统临时表空间过大”通常指 STT 被异常撑满而不是 UTT 被业务临时表占满。默认安装时 DB2 会创建一个名为 TEMPSPACE1 的 STT页大小与数据库页大小一致初始大小取决于创建数据库时的配置。如果建库时没有指定SYSTEM TEMPORARY TABLESPACEDB2 会自动生成一个由数据库管理器维护的临时空间但生产环境强烈建议显式创建并监控。STT 的消耗量不直接等于磁盘上某个文件的大小而是由缓冲池和容器共同承载。当排序数据量超过SORTHEAP能容纳的阈值时DB2 会把排序溢出写到 STT 容器里。如果 STT 容器所在文件系统空间不足或者 STT 的MAXSIZE被设死就会触发临时空间不足错误。更隐蔽的情况是STT 容器文件系统还有空间但 STT 的页分配达到上限同样会阻塞。所以“过大”有两种含义——物理文件膨胀到几十上百 GB或者逻辑使用率长时间接近 100%。2.2 临时表空间膨胀的四个典型触发源第一个触发源是失控的排序。一条没有走索引的ORDER BY或GROUP BY查询对千万级表做全表扫描后排序SORTHEAP放不下就溢出到 STT。如果并发有几十个这样的查询STT 会被迅速填满。第二个触发源是哈希连接。DB2 优化器在缺少合适索引时可能选择哈希连接HSJN构建哈希表需要大量临时空间尤其是连接键基数高、内存不足时。第三个触发源是REORG和索引重建。对大表做在线重组时DB2 需要临时空间存放中间数据如果表本身几百 GB临时空间需求可能达到表大小的 10% 到 30%。第四个触发源是LOAD或IMPORT的异常中断。加载失败后残留的临时数据没有及时清理或者加载过程中排序阶段消耗了过多 STT。这四个触发源里前两个是 SQL 层面的问题后两个是运维操作层面的问题。排查时要先区分是“持续增长”还是“脉冲式冲高”。持续增长往往意味着有长事务或游标未关闭导致临时空间无法释放脉冲式冲高则通常是某个大查询或批量操作瞬间吃光空间。监控MON_GET_TABLESPACE表函数和SNAP_GET_TABLESPACE快照可以拿到TBSP_UTILIZATION和TBSP_FREE_PAGES但更关键的是关联MON_GET_ACTIVITY找到正在消耗临时空间的 SQL。2.3 用监控表函数定位谁在吃临时空间DB2 11.1 及以上版本推荐用MON_GET_TABLESPACE和MON_GET_ACTIVITY组合查询。下面这段 SQL 可以直接查出当前系统临时表空间的使用率和正在执行的、可能消耗临时空间的语句。-- 查询系统临时表空间使用率DB2 11.1 SELECT TBSP_NAME, TBSP_TYPE, TBSP_CONTENT_TYPE, TBSP_UTILIZATION_PERCENT AS UTIL_PCT, TBSP_TOTAL_PAGES, TBSP_USED_PAGES, TBSP_FREE_PAGES FROM TABLE(MON_GET_TABLESPACE(, -2)) AS T WHERE TBSP_CONTENT_TYPE IN (SYSTEMP, USRTEMP) ORDER BY TBSP_UTILIZATION_PERCENT DESC;这段 SQL 的逻辑是MON_GET_TABLESPACE返回所有表空间的实时状态TBSP_CONTENT_TYPE过滤出系统临时和用户临时两类TBSP_UTILIZATION_PERCENT直接给出使用率。参数-2表示当前数据库空字符串表示所有表空间。如果UTIL_PCT超过 80 且持续不降就需要进一步查活动语句。-- 查询当前正在执行且可能消耗临时空间的 SQL SELECT A.APPL_ID, A.ACTIVITY_ID, A.ACTIVITY_TYPE, A.ACTIVITY_STATE, A.TOTAL_SORT_TIME, A.TOTAL_SORT_OVERFLOWS, SUBSTR(A.STMT_TEXT, 1, 200) AS STMT_SNIPPET FROM TABLE(MON_GET_ACTIVITY(NULL, -2)) AS A WHERE A.TOTAL_SORT_OVERFLOWS 0 OR A.ACTIVITY_TYPE IN (SORT, HSJN, REORG) ORDER BY A.TOTAL_SORT_OVERFLOWS DESC FETCH FIRST 20 ROWS ONLY;TOTAL_SORT_OVERFLOWS是关键指标它表示排序溢出到临时空间的次数。数值越大说明SORTHEAP越不够用或者排序数据量远超预期。ACTIVITY_TYPE里的SORT和HSJN直接对应排序和哈希连接。拿到APPL_ID后可以关联MON_GET_CONNECTION找到应用端信息定位是哪个业务模块发起的。提示如果MON_GET_ACTIVITY返回空但临时空间仍然很高检查是否有未提交的长事务持有临时空间或者REORG在后台运行。3. 从 SQL 和配置两头下手把临时空间压回去3.1 调整 SORTHEAP 与 SHEAPTHRES_SHR 的配比SORTHEAP是每个排序操作可用的私有内存上限SHEAPTHRES_SHR是数据库级共享排序内存阈值。当排序数据超过SORTHEAP时DB2 会把溢出部分写到 STT。所以最直接的止血手段是适当增大SORTHEAP让更多排序在内存完成。但SORTHEAP不是越大越好它乘以并发排序数不能超过SHEAPTHRES_SHR否则会触发排序内存不足反而导致更多溢出。# 查看当前排序相关配置 db2 get db cfg for SAMPLE | grep -i -E SORTHEAP|SHEAPTHRES # 调整 SORTHEAP 为 8192 页假设 4K 页大小约 32MB db2 update db cfg for SAMPLE using SORTHEAP 8192 # 调整 SHEAPTHRES_SHR 为 200000 页约 800MB db2 update db cfg for SAMPLE using SHEAPTHRES_SHR 200000参数说明SORTHEAP单位是页4K 页大小下 8192 页约 32MB。对于 OLTP 系统SORTHEAP建议 2048 到 8192 页对于 OLAP 系统可以到 16384 页以上。SHEAPTHRES_SHR要大于SORTHEAP乘以预估并发排序数。修改后需要db2stop和db2start才能完全生效但SORTHEAP在 DB2 11.1 中支持在线修改部分场景下动态生效。注意如果SHEAPTHRES_SHR设为 0表示不限制共享排序内存这在生产环境是危险配置容易导致内存耗尽。3.2 用 db2pd 和快照抓取临时空间增长现场当临时空间告警时第一时间用db2pd抓取现场比等快照更轻量。下面这条命令可以输出临时表空间的详细页分配和容器信息。# 抓取临时表空间详细状态 db2pd -d SAMPLE -tablespaces -dbptnmem # 抓取当前所有活动的排序和哈希连接 db2pd -d SAMPLE -activestatements -sort # 抓取 latch 等待情况确认是否因临时空间争用导致 db2pd -d SAMPLE -latches-tablespaces输出里关注Type为SystemTemp的条目看UsedPgs和FreePgs。-dbptnmem显示数据库分区内存使用如果SortHeap分配接近SHEAPTHRES_SHR说明排序内存已经吃紧。-activestatements -sort会列出正在排序的语句及其溢出次数。-latches里如果看到SQLO_LT_SQLB_TEMP_...相关的 latch 等待基本可以确认是临时空间争用导致的性能劣化。这些输出要重定向到文件方便后续对比。db2pd -d SAMPLE -tablespaces -dbptnmem /tmp/db2pd_tbsp_$(date %Y%m%d%H%M).out db2pd -d SAMPLE -activestatements -sort /tmp/db2pd_sort_$(date %Y%m%d%H%M).out注意db2pd在数据库挂起时可能也会阻塞建议在问题复现前先测试命令可用性并准备好备用连接方式。3.3 重建或扩容系统临时表空间的操作步骤如果 STT 容器所在文件系统还有空间但 STT 的MAXSIZE限制了增长可以通过ALTER TABLESPACE扩容。如果容器文件系统本身满了需要先加新容器或扩大文件系统。下面以 TEMPSPACE1 为例演示扩容和重建两种路径。-- 查看当前临时表空间容器和大小 SELECT TBSP_NAME, CONTAINER_NAME, CONTAINER_TYPE, TOTAL_PAGES, USABLE_PAGES FROM TABLE(MON_GET_CONTAINER(TEMPSPACE1, -2)); -- 扩容现有容器假设容器是文件路径 /db2data/temp01 ALTER TABLESPACE TEMPSPACE1 RESIZE (FILE /db2data/temp01 2048000); -- 或者新增一个容器 ALTER TABLESPACE TEMPSPACE1 ADD (FILE /db2data/temp02 2048000);RESIZE把现有容器扩大到指定页数ADD新增容器。2048000 页在 4K 页大小下约 8GB。如果容器是裸设备需要用RESIZE指定设备路径。扩容后 STT 立即可用不需要重启。但如果 STT 已经因为空间不足导致事务回滚扩容后需要等待回滚完成才能恢复。重建 STT 的步骤更重但能解决容器碎片或路径规划问题。先创建新的临时表空间再切换默认临时表空间最后删除旧的。-- 创建新的系统临时表空间 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 4K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 BUFFERPOOL IBMDEFAULTBP MAXSIZE 10240000; -- 将数据库默认系统临时表空间切换到新的 -- 注意DB2 不支持直接切换默认系统临时表空间需要修改 DB CFG实际上 DB2 不允许直接修改默认系统临时表空间指向常见做法是保留 TEMPSPACE1 但扩容或者通过db2move重建数据库时指定新的临时表空间。如果必须重建建议在维护窗口用db2look导出 DDL重建数据库后导入。这个操作风险高生产环境优先选扩容。3.4 用事件监控捕获临时空间耗尽的完整链路DB2 的CREATE EVENT MONITOR可以捕获临时空间相关事件适合在问题复现前部署。下面创建一个监控 STT 使用率超过阈值的事件监控。-- 创建表空间使用率事件监控 CREATE EVENT MONITOR TBSP_MONITOR FOR TABLESPACES WRITE TO TABLE BUFFER SIZE 1024 NONBLOCKED; -- 开启监控 SET EVENT MONITOR TBSP_MONITOR STATE 1; -- 查询监控数据 SELECT TBSP_NAME, TBSP_UTILIZATION_PERCENT, EVENT_TIMESTAMP FROM TBSP_MONITOR_TABLESPACES WHERE TBSP_UTILIZATION_PERCENT 80 ORDER BY EVENT_TIMESTAMP DESC FETCH FIRST 50 ROWS ONLY;FOR TABLESPACES指定监控表空间事件WRITE TO TABLE把数据写到自动生成的表中表名格式为监控名_事件类型。NONBLOCKED表示不阻塞数据库操作。开启后每次表空间使用率变化都会记录可以回溯是哪个时间点开始冲高。结合MON_GET_ACTIVITY的历史数据能还原出完整的劣化链路。提示事件监控会占用额外存储建议设置定期清理策略只保留最近 7 天数据。4. 避坑与排查临时表空间问题的五个血泪教训4.1 坑一只扩临时空间不查 SQL扩容后一周又满现象TEMPSPACE1 从 10GB 扩到 50GB一周后再次告警使用率又到 95%。原因扩容只是缓解症状根因是一条日终批量 SQL 缺少索引每次执行都对 2 亿行表做全表排序单次消耗 30GB 临时空间。扩容后空间够跑一次但批量任务每天跑临时空间释放不及时就累积。解决用MON_GET_ACTIVITY抓TOTAL_SORT_OVERFLOWS最高的语句拿到STMT_TEXT后用EXPLAIN分析执行计划。如果是TBSCAN加SORT考虑加索引或改写 SQL 用FETCH FIRST限制排序数据量。临时空间扩容是止血SQL 优化才是根治。4.2 坑二SORTHEAP 调大后数据库内存耗尽触发 OOM现象把SORTHEAP从 2048 调到 32768并发查询上来后操作系统 OOM Killer 杀掉了 DB2 进程。原因SORTHEAP是每个排序的私有内存32 个并发排序就是 32 乘以 32768 页约 4GB 仅排序内存。加上缓冲池和其他内存超过了物理内存上限。SHEAPTHRES_SHR没有同步调整导致共享排序内存无上限。解决SORTHEAP调整要配合SHEAPTHRES_SHR后者建议设为物理内存的 20% 到 30%。同时用db2pd -dbptnmem监控实际排序内存使用。如果并发排序数不可控宁可保持SORTHEAP适中通过索引优化减少排序数据量。4.3 坑三REORG 期间临时空间翻倍导致业务查询被挤死现象对大表执行REORG TABLE时业务查询大面积超时临时空间使用率冲到 100%。原因REORG需要临时空间存放重组中间数据如果表有 LOB 或索引临时空间需求可能是表大小的 20% 以上。同时业务查询也在消耗 STT两者争抢导致排队。解决REORG安排在业务低峰期并用REORG TABLE ... ALLOW NO ACCESS或ALLOW READ ACCESS控制锁级别。如果必须在线重组先确认 STT 剩余空间大于表大小的 30%。DB2 11.1 支持REORG的INDEXSCAN和PREFETCH参数可以降低临时空间峰值。4.4 坑四临时表空间容器放在根文件系统磁盘满导致数据库挂起现象STT 容器路径是/db2temp但/db2temp和根分区共用根分区被日志写满后 STT 无法扩展数据库进入挂起状态。原因建库时没有规划独立文件系统STT 容器和操作系统日志混用。根分区满后DB2 无法分配新页所有需要临时空间的语句全部阻塞。解决生产环境 STT 容器必须放在独立文件系统或 LVM 卷上并设置监控告警。如果已经混用用ALTER TABLESPACE ... ADD把新容器加到独立路径再DROP旧容器。同时清理根分区日志设置 logrotate。4.5 坑五Latch 等待被误判为锁等待排查方向跑偏现象性能监控显示大量Latch等待DBA 按锁等待排查查LOCKWAIT和DEADLOCK都没结果问题持续。原因DB2 的 Latch 是内部轻量级同步机制临时空间页分配时会有SQLO_LT_SQLB_TEMP相关 Latch。当 STT 使用率极高时多个会话争抢临时页分配 Latch表现为 Latch 等待而非锁等待。锁等待查不到是因为根本没有行锁冲突。解决用db2pd -latches确认 Latch 名称如果包含TEMP关键字直接转向临时空间排查。同时查MON_GET_TABLESPACE确认 STT 使用率。Latch 等待的根因还是临时空间不足扩容和 SQL 优化同样适用。5. 把临时空间监控做成常态化一个轻量脚本和三个关键阈值临时空间问题不能靠告警驱动等告警来了业务已经受影响。我习惯在每套 DB2 实例上部署一个轻量监控脚本每 5 分钟采集一次 STT 使用率、排序溢出次数和 Latch 等待写入监控表。下面这个 Shell 脚本可以直接用依赖db2pd和db2命令行。#!/bin/bash # db2_tbsp_monitor.sh - 采集 DB2 临时表空间关键指标 DBNAMESAMPLE LOG/tmp/db2_tbsp_monitor.log TIMESTAMP$(date %Y-%m-%d %H:%M:%S) # 采集临时表空间使用率 UTIL$(db2 -x SELECT TBSP_UTILIZATION_PERCENT FROM TABLE(MON_GET_TABLESPACE(, -2)) WHERE TBSP_CONTENT_TYPESYSTEMP 2/dev/null) # 采集排序溢出总数 OVERFLOW$(db2 -x SELECT SUM(TOTAL_SORT_OVERFLOWS) FROM TABLE(MON_GET_ACTIVITY(NULL, -2)) WHERE TOTAL_SORT_OVERFLOWS 0 2/dev/null) # 采集临时空间相关 Latch 等待 LATCH$(db2pd -d $DBNAME -latches 2/dev/null | grep -c TEMP) echo $TIMESTAMP | UTIL${UTIL}% | OVERFLOW${OVERFLOW} | TEMP_LATCH${LATCH} $LOG # 阈值告警使用率超过 80% 或 Latch 等待超过 10 if [ ${UTIL:-0} -gt 80 ] || [ ${LATCH:-0} -gt 10 ]; then echo ALERT: DB2 temp tablespace high on $DBNAME at $TIMESTAMP $LOG fi脚本逻辑很直接db2 -x执行 SQL 并只返回结果值MON_GET_TABLESPACE拿使用率MON_GET_ACTIVITY拿排序溢出总数db2pd -latches统计含TEMP的 Latch 行数。三个指标写入日志超过阈值追加 ALERT 行。参数方面UTIL阈值 80% 是经验值OLTP 系统可以设 70%OLAP 系统可以放宽到 85%。LATCH阈值 10 是保守值如果实例并发高可以调到 20。这个脚本可以配合crontab每 5 分钟跑一次日志用logrotate按天切割。三个关键阈值我一般这样定STT 使用率持续 5 分钟超过 80% 触发警告超过 90% 触发严重告警排序溢出次数 5 分钟内增长超过 1000 次说明SORTHEAP偏小或 SQL 有问题TEMP相关 Latch 等待超过 10 个会话说明临时空间争用已经影响并发。这三个阈值不是死的要根据实例的基线调整。比如一个日终批量为主的库白天 STT 使用率可能只有 10%晚上冲到 70% 是正常的阈值就要按时间段区分。最后说一个我踩过的坑曾经为了省事把监控脚本的告警阈值设成固定 90%结果一次批量任务把 STT 冲到 89% 但没触发告警业务已经明显变慢。后来改成动态基线取最近 7 天同时段使用率的 1.5 倍作为阈值才抓住了那次异常。监控这东西宁可误报也不能漏报但阈值一定要跟着业务节奏走。希望帮到你。本文还有配套的精品资源点击获取