简介这份资源是面向DB2数据库运维DBA与性能调优人员的真实案例文档聚焦银行DB2系统因临时表空间TEMPSPACE1异常膨胀至10GB而引发的SQL执行变慢问题。资源包内含1个doc文件约607KB完整记录了从ACTIVE SESSION异常升高入手逐步排查CPU、内存、I/O、锁等待与缓冲池命中率最终锁定临时表空间这一根因的全过程。文档重点展开LATCH竞争分析方法、STACK堆栈收集技巧以及如何借助db2top、db2pd -latch、db2trc suspend等工具在问题发生时暂停实例抓取诊断信息并给出优化SQL、调整排序参数、清理临时对象等解决思路。已有5899人学习下载适合希望掌握DB2性能问题定位方法、积累真实排障经验的读者参考。1. DB2 临时表空间告急为什么你的 SQL 跑着跑着就卡死了生产环境凌晨两点监控告警突然炸了——某核心报表 SQL 执行时间从 3 秒飙到 40 分钟应用线程池被占满上游业务全线超时。登上去一看TEMPSPACE1使用率 98%磁盘 I/O 打满db2diag.log里全是临时表空间不足的告警。这不是玄学是 DB2 临时表空间过大引发的典型性能雪崩。临时表空间不像用户表空间那样存业务数据它专门扛排序、哈希连接、临时结果集这些中间过程一旦失控轻则单条 SQL 变慢重则整个实例被拖垮。这篇文章面向正在被 DB2 临时表空间问题折磨的 DBA 和开发从根因定位到参数调优再到避坑清单一步步拆开讲透让你下次再看到TEMPSPACE1告警时能十分钟内找到病灶。2. 先搞懂 DB2 临时表空间到底在扛什么活2.1 系统临时表空间 vs 用户临时表空间别把两类混为一谈DB2 里临时表空间分两种很多人排查时容易搞混。系统临时表空间System Temporary Tablespace由数据库管理器自动使用主要承载排序、分组、哈希连接、索引重建、REORG等操作的中间数据。你建库时默认创建的TEMPSPACE1就是系统临时表空间它的大小由SYSIBM.SYSBUFFERPOOL和底层容器共同决定。用户临时表空间User Temporary Tablespace则是给DECLARE GLOBAL TEMPORARY TABLE用的需要显式创建默认不存在。两者的核心区别在于系统临时表空间不够几乎所有复杂 SQL 都会受影响用户临时表空间不够只有用到全局临时表的会话会报错。生产上 90% 的“临时表空间过大”问题指的都是系统临时表空间也就是TEMPSPACE1这类。常见做法是先用LIST TABLESPACES SHOW DETAIL确认当前实例里到底有哪些临时表空间再判断是哪一类在膨胀。# 查看所有表空间状态重点关注 Type 列和 Used pages db2 connect to SAMPLE db2 LIST TABLESPACES SHOW DETAIL输出里Type列如果是System Temporary那就是系统临时表空间Used pages和Total pages的比值就是当前使用率。如果Used pages接近Total pages说明临时表空间已经吃紧接下来要查是谁在吃。2.2 临时表空间膨胀的四个典型触发场景临时表空间不会无缘无故涨背后一定有 SQL 在大量消耗。根据我处理过的案例触发场景集中在四类第一类是排序溢出。当SORTHEAP不够用时DB2 会把排序中间结果写到系统临时表空间。一条ORDER BY带几百万行的 SQL如果SORTHEAP只有 4KB临时表空间瞬间就能涨几个 GB。第二类是哈希连接溢出。DB2 优化器选择哈希连接Hash Join时如果SHEAPTHRES_SHR和排序堆不够哈希表会溢写到临时表空间。多表关联查询里这种场景特别常见。第三类是索引重建和 REORG。在线REORG或者CREATE INDEX时DB2 需要临时空间来存放中间数据大表操作能把临时表空间直接撑爆。第四类是失控的递归查询或笛卡尔积。写错的 SQL 产生海量中间结果集临时表空间成了背锅侠。这类问题最隐蔽因为 SQL 本身可能不报错只是慢直到临时表空间满了才暴露。-- 查询当前正在消耗临时表空间的 SQL需要 MON_GET 权限 SELECT APPLICATION_HANDLE, MEMBER, TEMP_READS, -- 临时表空间读次数 TEMP_WRITES, -- 临时表空间写次数 TEMP_SPACE_USED, -- 当前使用的临时空间KB STMT_TEXT FROM TABLE(MON_GET_ACTIVITY(NULL, -2)) AS T WHERE TEMP_SPACE_USED 0 ORDER BY TEMP_SPACE_USED DESC FETCH FIRST 10 ROWS ONLY;这条查询是定位“谁在吃临时表空间”的核心手段。TEMP_SPACE_USED单位是 KB按降序排就能看到消耗最大的活动。TEMP_WRITES高说明有大量溢写TEMP_READS高说明溢写的数据被反复读回两者都高基本可以判定是排序或哈希连接溢出。参数-2表示返回所有成员上的活动单分区环境可以改成-1。2.3 临时表空间大小到底该设多少一个可落地的估算方法很多人问临时表空间设多大合适网上答案从几 GB 到几百 GB 都有其实没有万能值。我一般按这个思路估先看最大表的行数和平均行宽算出单次排序的峰值数据量再乘以并发排序数最后留 30% 余量。举个例子一张 5000 万行的表平均行宽 200 字节单次全表排序峰值约 10GB。如果同时有 5 个这样的排序并发理论峰值 50GB加 30% 余量就是 65GB。但实际生产上不会所有排序同时达到峰值所以可以按峰值的 60% 到 70% 来设也就是 35GB 到 45GB。这个值不是拍脑袋而是根据MON_GET_ACTIVITY里历史TEMP_SPACE_USED的 P95 值来校准。# 查看临时表空间容器和当前大小 db2 LIST TABLESPACE CONTAINERS FOR 1 SHOW DETAIL # 动态扩展临时表空间以自动存储为例 db2 ALTER TABLESPACE TEMPSPACE1 RESIZE (FILE /db2data/temp01.dbf 40960)RESIZE的单位是页数默认页大小 4KB 时40960 页约等于 160MB。生产上更推荐开启自动扩展但一定要设上限否则临时表空间能把磁盘吃满。自动扩展的MAXSIZE建议设为预估峰值的 1.5 倍给自己留出应急空间。3. 从告警到根因临时表空间问题的排查链路3.1 第一步确认是临时表空间满还是磁盘满临时表空间告警和磁盘满的告警经常同时出现但处理方式完全不同。先登服务器看df -h如果文件系统还有空间但 DB2 报SQL1585N或SQL1131N那就是临时表空间容器满了不是磁盘满。SQL1585N的完整报错是“A system temporary table space with sufficient page size does not exist”意思是找不到足够页大小的系统临时表空间。# 查看 DB2 诊断日志里最近的临时表空间相关错误 db2diag -g levelError -H 2h | grep -i temp\|SQL1585\|SQL1131 # 查看当前临时表空间使用率 db2 SELECT TBSP_NAME, TBSP_USED_PAGES, TBSP_FREE_PAGES, (TBSP_USED_PAGES * 100.0 / (TBSP_USED_PAGES TBSP_FREE_PAGES)) AS USED_PCT FROM TABLE(MON_GET_TABLESPACE(, -2)) AS T WHERE TBSP_TYPE STBSP_TYPE S过滤出系统临时表空间。USED_PCT超过 80% 就要警惕超过 95% 基本已经在影响业务。注意MON_GET_TABLESPACE返回的是当前快照如果问题已经过去需要结合历史监控数据看趋势。3.2 第二步用 MON_GET_ACTIVITY 揪出消耗大户确认临时表空间紧张后下一步是找到具体哪条 SQL 在消耗。MON_GET_ACTIVITY是最直接的工具但要注意它默认只返回当前活跃的活动如果 SQL 已经执行完就查不到了。所以生产上建议开启活动历史收集。-- 开启活动历史收集需要实例级权限 UPDATE DATABASE CONFIGURATION USING MON_ACT_METRICS YES; -- 或者更细粒度地控制 CALL SYSPROC.ADMIN_CMD(UPDATE DB CFG USING MON_ACT_METRICS BASE); -- 从活动历史里查临时空间消耗 TOP 10 SELECT ACTIVITY_ID, APPL_ID, TEMP_SPACE_USED, ROWS_READ, ROWS_RETURNED, STMT_TEXT FROM TABLE(MON_GET_ACTIVITY_HISTORY(NULL, -2)) AS T WHERE TEMP_SPACE_USED 1024 -- 只看超过 1MB 的 ORDER BY TEMP_SPACE_USED DESC FETCH FIRST 10 ROWS ONLY;MON_ACT_METRICS设为BASE时收集基础指标设为EXTENDED会收集更详细的等待和排序信息但开销也更大。生产上建议先用BASE定位到具体 SQL 后再临时开EXTENDED深挖。TEMP_SPACE_USED超过 1MB 的 SQL 就值得关注超过 100MB 的基本就是元凶。3.3 第三步看执行计划确认是排序还是哈希连接溢出找到 SQL 后用db2exfmt看执行计划重点看SORT和HSJOIN节点。如果计划里有SORT且Cumulative Total Cost很高说明排序是瓶颈如果有HSJOIN且Hash Join的Probe阶段有大量临时空间消耗说明哈希表溢写。# 生成执行计划 db2 EXPLAIN PLAN FOR SELECT ... db2exfmt -d SAMPLE -g TIC -w -1 -n % -s % -# 0 -o explain_output.txt # 在输出里搜索 SORT 和 HSJOIN grep -n SORT\|HSJOIN\|TEMPSPACE explain_output.txt执行计划里SORT节点的Input Rows和Output Rows差距大说明排序数据量大。HSJOIN节点如果Build阶段的行数超过SORTHEAP能容纳的量就会溢写到临时表空间。常见做法是调大SORTHEAP和SHEAPTHRES_SHR让排序和哈希尽量在内存里完成。3.4 第四步临时表空间监控的常态化配置临时表空间问题不能等告警了才查日常监控要跟上。我一般会在 Zabbix 或 Prometheus 里配三个指标临时表空间使用率、临时表空间读写速率、活动 SQL 的临时空间消耗 TOP 5。使用率超过 70% 预警超过 85% 告警读写速率突增 3 倍以上也告警。# 写一个简单的监控脚本每 5 分钟采集一次 #!/bin/bash db2 connect to SAMPLE /dev/null 21 db2 SELECT TBSP_NAME, (TBSP_USED_PAGES * 100.0 / (TBSP_USED_PAGES TBSP_FREE_PAGES)) AS USED_PCT FROM TABLE(MON_GET_TABLESPACE(, -2)) AS T WHERE TBSP_TYPE S | while read line; do echo $(date %Y-%m-%d %H:%M:%S) $line done db2 terminate /dev/null 21这个脚本输出可以直接喂给监控系统。注意db2 connect和db2 terminate要成对出现否则连接会泄漏。采集频率不要低于 1 分钟否则可能错过瞬时峰值。4. 调参实战让临时表空间不再成为瓶颈4.1 SORTHEAP 和 SHEAPTHRES_SHR 的配比逻辑SORTHEAP是每个排序操作能用的私有内存上限SHEAPTHRES_SHR是所有并发排序能用的共享内存总量。这两个参数配不好要么排序频繁溢写临时表空间要么内存被排序吃光影响其他操作。我的经验配比是SORTHEAP设为单次排序峰值数据量的 1.5 倍SHEAPTHRES_SHR设为SORTHEAP乘以预期并发排序数再乘以 0.8。比如单次排序峰值 100MB预期 10 个并发排序那SORTHEAP设 150MBSHEAPTHRES_SHR设 1200MB。# 查看当前配置 db2 GET DATABASE CONFIGURATION FOR SAMPLE | grep -i SORTHEAP\|SHEAPTHRES # 修改配置单位是 4KB 页150MB 约等于 38400 页 db2 UPDATE DATABASE CONFIGURATION FOR SAMPLE USING SORTHEAP 38400 db2 UPDATE DATABASE CONFIGURATION FOR SAMPLE USING SHEAPTHRES_SHR 307200注意SORTHEAP在 DB2 11.1 之后支持自动调整SORTHEAP AUTOMATIC但自动调整不一定适合所有场景尤其是排序模式固定的报表库。我一般建议先手动设一个基准值观察一周后再决定是否开自动。4.2 临时表空间页大小选择4KB 还是 32KBDB2 支持 4KB、8KB、16KB、32KB 四种页大小的临时表空间。页越大单次 I/O 能读写的数据越多但内部碎片也越严重。对于大排序场景32KB 页的临时表空间通常比 4KB 快 20% 到 30%因为减少了 I/O 次数。但要注意临时表空间的页大小必须和数据库的页大小兼容。如果数据库是 4KB 页你建 32KB 的临时表空间DB2 会报SQL1585N。常见做法是建一个 32KB 的系统临时表空间专门给大排序用同时保留 4KB 的作为默认。-- 创建 32KB 页的系统临时表空间 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE32 PAGESIZE 32K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 32 BUFFERPOOL BP32K; -- 查看现有临时表空间的页大小 SELECT TBSP_NAME, TBSP_PAGE_SIZE FROM SYSIBM.SYSTABLESPACES WHERE TBSP_TYPE S;EXTENTSIZE设为 32 表示每个扩展 32 页32KB 页就是 1MB 一个扩展。BUFFERPOOL要对应建一个 32KB 的缓冲池否则临时表空间用不了。建好后优化器会自动选择合适页大小的临时表空间不需要手动指定。4.3 用 DB2_WORKLOAD 参数让优化器更懂你的业务DB2_WORKLOAD是 DB2 10.5 之后引入的注册表变量告诉优化器当前数据库主要跑什么类型的负载。设成ANALYTICS时优化器会倾向于选择哈希连接和大排序策略同时更积极地使用临时表空间设成OLTP时优化器会尽量避免大排序优先走索引。# 查看当前 DB2_WORKLOAD 设置 db2set DB2_WORKLOAD # 设为 ANALYTICS适合报表库和数据仓库 db2set DB2_WORKLOADANALYTICS # 需要重启实例生效 db2stop force db2start这个参数对临时表空间的影响很直接ANALYTICS模式下优化器会认为临时表空间是“廉价”的更愿意用排序换索引扫描OLTP模式下则相反。生产上如果临时表空间经常告警但业务确实是 OLTP 为主可以试试设成OLTP让优化器少用临时表空间。4.4 临时表空间自动扩展的坑MAXSIZE 一定要设自动扩展很方便但不设上限就是灾难。我见过一个案例临时表空间自动扩展到 500GB把数据盘吃满导致数据库直接挂起。MAXSIZE要根据磁盘剩余空间和业务峰值来设一般建议不超过磁盘总容量的 30%。# 查看当前自动扩展配置 db2 SELECT TBSP_NAME, TBSP_AUTO_RESIZE_ENABLED, TBSP_MAX_SIZE FROM TABLE(MON_GET_TABLESPACE(, -2)) AS T WHERE TBSP_TYPE S # 修改 MAXSIZE单位是页4KB 页时 10485760 页约等于 40GB db2 ALTER TABLESPACE TEMPSPACE1 MAXSIZE 10485760TBSP_AUTO_RESIZE_ENABLED为 1 表示开启自动扩展为 0 表示关闭。TBSP_MAX_SIZE为 -1 表示无上限这是最危险的配置。生产上一定要设一个明确的上限并且定期检查磁盘剩余空间。5. 避坑指南临时表空间排查中的五个血泪教训5.1 坑一只扩临时表空间不查根因现象临时表空间告警DBA 直接扩了 100GB第二天又满了。原因临时表空间膨胀是结果不是原因根因可能是某条 SQL 突然开始全表排序或者索引失效导致哈希连接溢写。不查根因扩多少都会被吃满。解决扩空间的同时必须用MON_GET_ACTIVITY和db2exfmt定位消耗最大的 SQL确认是排序溢出还是哈希连接溢出然后针对性调SORTHEAP或加索引。5.2 坑二SORTHEAP 设太大导致内存争抢现象调大SORTHEAP后临时表空间使用率降了但系统整体变慢其他 SQL 响应时间变长。原因SORTHEAP是每个排序的私有内存设太大时多个并发排序会吃掉大量内存导致缓冲池命中率下降磁盘 I/O 反而增加。解决SORTHEAP不要超过SHEAPTHRES_SHR的 1/10同时监控缓冲池命中率。如果命中率下降说明内存被排序抢了要适当回调。5.3 坑三忽略 MON_ACT_METRICS 的开销现象开启MON_ACT_METRICS EXTENDED后数据库整体吞吐量下降 10%。原因EXTENDED模式会收集每条 SQL 的详细排序和等待信息开销不小高并发场景下尤其明显。解决日常用BASE模式只在定位问题时临时开EXTENDED定位完立刻改回BASE。改完不需要重启动态生效。5.4 坑四临时表空间容器放在慢盘上现象临时表空间使用率不高但排序操作还是很慢。原因临时表空间的容器放在了机械盘或者网络存储上I/O 延迟高即使空间够读写也慢。解决临时表空间容器一定要放在本地 SSD 或高性能存储上。用LIST TABLESPACE CONTAINERS确认容器路径如果是网络盘尽快迁移到本地盘。5.5 坑五REORG 和 CREATE INDEX 期间临时表空间翻倍现象大表REORG时临时表空间突然涨到 90%业务 SQL 开始报错。原因REORG和CREATE INDEX需要临时空间存放中间数据大表操作时临时空间需求可能翻倍。解决大表REORG前先确认临时表空间剩余空间建议预留至少 2 倍于表大小的临时空间。如果不够先用REORG ... ALLOW NO ACCESS减少中间数据量或者分批REORG。6. 进阶技巧用 DB2 内置工具做临时表空间趋势预测临时表空间调优不是一锤子买卖业务在变SQL 在变临时表空间的需求也在变。我一般会用一个简单的趋势预测方法每周采集一次MON_GET_TABLESPACE的TBSP_USED_PAGES峰值连续采集 8 周用线性回归算下周的预测值。如果预测值超过当前容量的 80%就提前扩容或调参。-- 创建历史采集表 CREATE TABLE TEMP_MONITOR ( COLLECT_TIME TIMESTAMP, TBSP_NAME VARCHAR(128), USED_PAGES BIGINT, TOTAL_PAGES BIGINT ); -- 每周插入一次快照 INSERT INTO TEMP_MONITOR SELECT CURRENT TIMESTAMP, TBSP_NAME, TBSP_USED_PAGES, TBSP_USED_PAGES TBSP_FREE_PAGES FROM TABLE(MON_GET_TABLESPACE(, -2)) AS T WHERE TBSP_TYPE S; -- 查询最近 8 周的趋势 SELECT COLLECT_TIME, USED_PAGES, (USED_PAGES - LAG(USED_PAGES) OVER (ORDER BY COLLECT_TIME)) AS WEEKLY_GROWTH FROM TEMP_MONITOR WHERE COLLECT_TIME CURRENT TIMESTAMP - 8 WEEKS ORDER BY COLLECT_TIME;WEEKLY_GROWTH是每周增长量如果连续三周都是正增长且增速加快说明临时表空间需求在持续上升需要提前干预。这个表可以配合定时任务自动采集用ADMIN_CMD或者外部脚本都行。另一个技巧是用db2pd实时看临时表空间的底层状态比MON_GET_TABLESPACE更细粒度# 查看临时表空间的详细状态 db2pd -d SAMPLE -tablespaces # 查看临时表空间的 I/O 统计 db2pd -d SAMPLE -tabstatsdb2pd -tablespaces会输出每个表空间的容器、页大小、使用页数、空闲页数以及是否在自动扩展。-tabstats则输出读写次数、读写页数、读写时间可以算出平均 I/O 延迟。如果平均 I/O 延迟超过 10ms说明存储性能有问题临时表空间再大也快不起来。我自己的习惯是每次处理完临时表空间告警都会把根因、调参记录、验证结果写进一个运维笔记下次再遇到类似问题直接翻笔记。这个习惯帮我省了至少几十个小时的重复排查时间。临时表空间问题看起来复杂但套路就那几样摸清了就能从容应对。希望帮到你。本文还有配套的精品资源点击获取