DB2系统临时表空间过大排查：排序、哈希与参数调优实践

简介一份面向DB2数据库运维人员的性能故障案例文档聚焦系统临时表空间TEMPSPACE1异常膨胀导致SQL执行时间显著变长的问题。资源基于某银行生产环境的真实排障经历完整记录了从发现ACTIVE SESSION数量激增、常规检查无果到最终定位临时表空间达10GB为根因的过程。文档重点介绍了DB2性能分析方法论包括db2top监控活跃会话、LATCH等待分析、堆栈收集与db2trc工具暂停实例等技巧并给出优化SQL、调整临时表空间参数、清理临时对象等解决措施对处理类似数据库性能瓶颈有直接参考价值。资源为单个doc文档大小607KB内容结构清晰已有5898人学习下载适合有一定DB2基础的DBA或性能调优工程师阅读。1. 系统临时表空间过大为什么性能问题总在这时候集体爆发某个周二的晚上十一点批量作业刚跑起来监控大屏上系统临时表空间的使用率从 20% 一路冲到 97%紧接着应用连接排队核心查询的排序时间翻了不止三倍。这类故障一旦出现应用方第一反应是加机器、加连接DBA 第一反应是查 temp 表空间——但真正的原因往往不是表空间容量不够而是某个 SQL 在排序或哈希连接阶段把临时空间吃满了。DB2 系统临时表空间System Temporary Table Space是排序、分组、哈希连接、索引创建和 REORG 的默认落盘位置。它一旦被撑大影响的不只是自身容量还会拖慢所有依赖排序的查询甚至触发锁等待和应用超时。这篇笔记按“谁吃掉了临时表空间 → 怎么定位 → 怎么处理 → 日常怎么防”这条线展开给出我在生产环境里反复验证过的命令、参数和避坑经验。适合正在处理同类问题的 DBA也适合被临时表空间告警缠身的应用开发。2. 临时表空间被谁撑大的排序、HASH JOIN 与隐式转换的连锁反应2.1 系统临时表空间和用户临时表空间监控前先分清两类对象很多同事一看到“临时表空间”就去查USERSPACE1这是最常见的方向搞错。DB2 数据库里的临时表空间分两类用途完全不同。系统临时表空间System Temporary Table SpaceDB2 内部使用排序、哈希连接、索引创建、REORG、物化查询表刷新都要靠它暂存中间结果。它在数据库创建时默认生成名字通常是TEMPSPACE1。这类表空间不能被用户直接建表。用户临时表空间User Temporary Table Space专门给DECLARE GLOBAL TEMPORARY TABLE这种会话级临时表使用没有它应用层临时表就建不出来。排查性能问题时90% 的流量都集中在系统临时表空间上。确认类型用一条命令就行db2 list tablespaces for sample show detail输出里每一段开头会有Tablespace ID、Type和Contents重点看ContentsTablespace ID 3 Type System managed space Contents System Temporary data State 0x0000Contents显示System Temporary data才是系统临时表空间。如果看到的是User Temporary data那是另一条排查路径。分清这一点告警来了才不会看错对象。2.2 消耗临时空间的四大来源SORT、HASH JOIN、索引创建与 REORG系统临时表空间的消耗来源不是随机的基本就这四类按出现频率排序排序操作。ORDER BY、GROUP BY、DISTINCT、集合运算UNION和MINUS、OLAP开窗函数只要排序内存不够中间结果就写到系统临时表空间。这是最常见的一类。哈希连接。DB2 在HASH JOIN构建阶段会把小表在内存里建哈希表如果内存阈值不够或者统计信息严重失真哈希表会被迫下探到临时表空间。典型场景是两个大表做等值连接但优化器错误评估了行数。索引创建与 REORG。CREATE INDEX排序索引键值REORG TABLE需要临时段来重组数据页REORG INDEXES也一样。这类操作平时不显眼一旦在白天窗口执行临时表空间使用率会瞬间抬升。声明全局临时表本身也会占空间但注意它用的是用户临时表空间如果业务代码里大量使用 DGTT 且没有及时DROP TABLE同样会给你“临时表空间又满了”的错觉。2.3 SHEAPTHRES、SHEAPTHRES_SHR 和 SORTHEAP三个参数如何控制排序落盘先要纠正一个常见误区临时表空间使用率高不代表表空间本身是瓶颈。排序是先走内存内存不够才落盘落盘的位置才是系统临时表空间。所以排查时必须把三个参数放在一起看。SORTHEAP数据库级参数代表单次排序可用的私有内存上限默认值在 256 到 4096 页之间单位是 4KB 页。可以动态调整。SHEAPTHRES_SHR数据库级参数限制所有共享排序活动在缓冲池里占用的总量。注意它限制的是“共享排序内存”不限制私有排序。DB2 会自动管理的话显示为 0。SHEAPTHRES实例级参数控制私有排序的内存总量上限。老版本里它经常被设成固定值设置不当会导致大量排序直接落盘表现为“还没到高峰临时表空间先满了”。它们之间的关系我用一句话概括一个排序申请SORTHEAP大小但所有排序加在一起不能超过SHEAPTHRES或SHEAPTHRES_SHR的限制超出的部分全部写进系统临时表空间。生产库里最常见的翻车组合是SORTHEAP调得很大但SHEAPTHRES保持很小结果大量排序并发时仍然疯狂落盘临时表空间被瞬时打爆。这种情况调SORTHEAP是没用的真正的调节对象是SHEAPTHRES。查看当前值的方法db2 get db cfg for sample | grep -i SORTHEAP db2 get db cfg for sample | grep -i SHEAPTHRES_SHR db2 get db cfg for sample | grep -i SHEAPTHRES2.4 隐式转换如何把简单查询变成临时表空间大户这一节值得应用开发重点看。很多临时表空间爆掉的 SQL表面上看 SELECT 语句长得挺干净问题出在谓词类型不匹配。某个查询在 WHERE 里把字符串列和数字常量比较SELECT order_id, cust_name FROM order_tab WHERE order_no 123456;如果order_no是VARCHAR类型DB2 优化器为了比较要么把列转成数字要么把常量转成字符串。一旦选择了对列做转换索引就废了。更麻烦的是如果目标列上要逐个转换并排序优化器就可能选择排序操作把结果暂存到系统临时表空间。避免这种问题常见做法是换个方向把常量转成列的类型SELECT order_id, cust_name FROM order_tab WHERE order_no CHAR(123456);还有一种情况是判断字符串字段是否由数字构成。比如代码里先查出一堆字符串再在应用层判断是不是数字或者在 SQL 里用正则过滤SELECT id, tag_value FROM temp_tag_tab WHERE tag_value REGEXP_LIKE(tag_value, ^[0-9]$);这类写法本身不是不行但会对大结果集产生额外开销。如果这个判断发生在 JOIN 和排序之前临时表空间占用会成倍叠加。DB2 判断数字字符串更高效的做法是先压缩数据范围再用TRANSLATE或CASE WHEN做一次轻量判断尽量让过滤发生在扫描早期。临时表空间被撑大的根因永远是“有操作需要中间结果落盘”而不是“临时文件天生就大”。带着这个认知去看监控方向不会偏。3. 定位临时表空间真凶db2pd 与 MON_GET_TABLESPACE 的排查命令链3.1 先确认容量LIST TABLESPACES SHOW DETAIL告警触发后的第一步不是抓 SQL是确认当前使用率是持续走高还是瞬时脉冲。用最直接的命令db2 connect to sample db2 list tablespaces show detail聚焦系统临时表空间那段输出重点看两行Total pages 1048576 Used pages 990452Used pages 除以 Total pages 就是当前使用率。如果 Used pages 接近总量且数值还在跳动说明正在有排序活动落盘如果数值稳定在高位可能是有长事务占用也可能是表空间扩展后没有收缩回来的历史残留。进一步看当前有哪些连接在活跃状态db2 list applications for database sample show detail记录下Application handle、Status和Statement text后面用 db2pd 精确锁定。这一步别跳太快很多 DBA 上来就查 SQL忽略了一个事实临时表空间高水位可能是十分钟前的那次批量作业造成真正“正在跑”的 SQL 反而只占小头。3.2 用 db2pd 抓正在跑的 SQL 与排序状态LIST APPLICATIONS看的是连接级信息粒度还不够。db2pd 能直接深入实例内存不需要建立新连接这在实例接近打满时非常有用。先抓表空间瞬时状态db2pd -d sample -tablespaces输出里能看到各个表空间的TbspaceID、State、TotalPgs和UsablePgs。如果State不是0x0000而是别的值比如有容器异常优先处理容器问题否则排序再正常也会被异常状态放大。再抓谁在消耗临时表空间db2pd -d sample -applications关注AppHandl、Status、TranHolds这几列。很多吃临时空间的事务会长时间停留在Preparing或Executing状态少数情况下能看到Sorting。这些连接对应的AgentID可以继续关联到动态 SQL 缓存db2pd -d sample -dynamic在dynamic输出里根据AppHandl对应的StmtUID找到具体语句用num_orders或sort_hash_jos这类字段判断它是否做了大量排序。这一步抓到的 SQL 就是后续改写和优化的直接证据。3.3 用 MON_GET_TABLESPACE 视图统计临时空间消耗趋势db2pd 看到的是“当前此刻”如果故障是几分钟前发生的瞬时数据可能已经错过。要回看趋势用监控视图最合适。SELECT varchar(tbsp_name, 30) AS tbsp_name, tbsp_type AS tbsp_type, tbsp_total_size_kb AS total_kb, tbsp_used_size_kb AS used_kb, tbsp_utilization_percent AS used_pct FROM TABLE (MON_GET_TABLESPACE(NULL, -2)) AS T WHERE tbsp_type 1MON_GET_TABLESPACE(NULL, -2)的第一参数表示不过滤表空间名-2表示查所有数据库分区成员。返回结果里tbsp_type为 1 的是系统临时表空间其他类型可以根据需求过滤。这个查询适合放进运维脚本里周期性落库每隔 30 秒跑一次把临时表空间的使用率写入监控表。故障复盘时按时间轴回放能准确看到飙升起点再把那个时段的 SQL 日志拉出来对比。如果实例版本老MON 视图不可用退而求其次用快照db2 get snapshot for tablespaces on sample快照输出里的Snapshot timestamp和Buffer pool temporary data logical reads能辅助判断排序强度。相比 MON 视图快照的粒度粗但聊胜于无。3.4 结合 db2top 和应用日志圈定窗口期命令敲得再熟也架不住信息分散。我一般会同步开一个 db2topdb2top -d sample进入后先看 Tablespace 面板确认临时表空间使用率再切到 Sorts 面板看每秒排序次数和排序堆溢出的比例。这两个面板放在一起能快速区分是“大量小排序挤在一起”还是“少数大排序吃满空间”。前者的处理方向是削减并发或微调SHEAPTHRES_SHR后者的处理方向是优化单条 SQL。二者方向完全不同处理前必须分清楚。如果 db2top 显示排序溢出比例持续超过 30%同时临时表空间使用率同步增长那基本可以断定排序内存不足。这时去应用的日志系统里拉同一时间段的慢 SQL 列表按执行次数排序找出窗口期内被反复执行的 TOP N。至此临时表空间问题已经从“表空间告警”还原成了“具体 SQL 具体时间点”。接下来才是真正动手处理。4. 系统临时表空间过大怎么办参数调整、SQL 改写与 db2stop/start 时机4.1 调整数据库参数SORTHEAP 与 SHEAPTHRES_SHR 怎么配合处理临时表空间过大很多人都想着“把临时表空间扩容”这是最直接的思路但可能治标不治本。我一般先调三个排序参数再决定要不要扩容量。先看当前值再按下面的顺序调整db2 update db cfg for sample using SORTHEAP 4096 db2 update db cfg for sample using SHEAPTHRES_SHR 64000参数说明SORTHEAP4096 表示单次排序最多使用 4096 个 4KB 页也就是 16MB。这是给每个排序的起步内存。SHEAPTHRES_SHR64000 表示共享排序总量限制为 250MB 左右。这个值要结合并发量看不是越大越好。SHEAPTHRES是实例级参数修改后要重启实例才生效。应急处理时先别动它避免为了救火引入新的停机窗口。调整的依据是临时表空间使用率还在涨但排序本身没有严重溢出说明并发排序太多优先加SHEAPTHRES_SHR如果单纯是某个大查询排序溢出优先加SORTHEAP同时观察SHEAPTHRES上限会不会变瓶瓶颈。这两个参数改完不要求重启数据库但deactivate和activate一下更保险db2 deactivate db sample db2 activate db sample如果实例里还有活跃连接deactivate会失败。这是正常的说明参数修改在下次完全停止激活时也会生效并不影响之后的新连接。4.2 改写 SQL消除排序、哈希连接和隐式转换参数是给系统兜底的SQL 才是决定临时表空间高度的天花板。改动 SQL 前先看执行计划确认排序算子从哪里来。先抓执行计划db2 explain plan for SELECT order_no, item_cnt FROM orders ORDER BY order_no; db2exfmt -d sample -1 -o exfmt.out如果exfmt.out里有独立的SORT节点且它的Rows接近全表行数说明这不是必要排序是索引缺失或谓词写法有问题。优先看是否能用索引消除排序CREATE INDEX idx_orders_no ON orders(order_no);让优化器用索引的有序扫描替代显式排序。注意如果查询里带了ORDER BY和GROUP BY混合索引覆盖不一定完整执行计划里仍然可能出现SORT。二次元现象是哈希连接被滥用。大表小表连接时优化器会选小表做哈希构建端如果统计信息偏差导致系统误以为小表很大哈希表就会膨胀落盘。这时重新收集统计信息比改写 SQL 更有效db2 runstats on table orders with distribution and detailed indexes all如果 RUNSTATS 之后临时表空间消耗仍然高再改写连接顺序用//*#$*/之类的语法不是好选择正确做法是让业务 SQL 里先过滤再连接。比如SELECT ... FROM orders o INNER JOIN order_items i ON o.order_id i.order_id WHERE o.pay_status PAID如果pay_status上过滤条件能先执行参与哈希连接的行数会大幅下降。隐式转换同样值得复查。前面提到的字符串列与数字常量比较在 SQL 里改写成一个显式类型转换往往能让优化器重新选择索引路径。4.3 重建或迁移系统临时表空间容器调整的可行与不可行如果临时表空间使用率长期维持 80% 以上业务 SQL 又无法快速优化那就需要给表空间扩容或者搬迁。自动存储数据库上最简单的是直接扩展db2 alter tablespace tempspace1 resize (all 10000)参数10000的单位是 4KB 页表示把所有容器统一扩展到 10000 页。自动存储管理的容器文件会随之增大。这个操作在线执行不中断业务。如果容器文件分布在不合理的磁盘上或者旧表空间文件已经碎片化严重可以重建一个系统临时表空间并切换过去db2 create temporary tablespace TMP_NEW pagesize 32 k managed by automatic storage; db2 alter database sample set default temporary tablespace TMP_NEW;注意顺序必须先建新表空间并设为默认等待旧连接全部结束再删除旧表空间。db2 drop tablespace tempspace1;删除前务必确认没有应用还在使用tempspace1。如果开发框架在连接池里缓存了指向旧临时表空间的语句删除会直接引发连接报错。我在生产环境见过SQLCODE -147就是因为旧连接没断开就去 drop。建议 drop 之前先切维护窗口或者等一个业务低峰。4.4 什么时候必须 db2stop/db2start重启实例的代价与收益临时表空间问题能不能靠重启实例解决这是一个被反复问起的问题。回答分两层重启确实能清理残留在系统临时表空间上的临时段和未释放的排序上下文。特别是由于大量排序异常中断、缓冲池状态混乱引发的“使用率居高不下但现场没有 SQL”的情况重启几乎是唯一快速恢复手段。但重启是最后手段不是第一手段。一次db2stop意味着所有连接被断开、未提交事务要回滚、缓冲池要重训。在核心系统上这个成本远高于临时表空间短暂的“稳定”。确需重启时按严谨顺序执行db2 force applications all db2 deactivate db sample db2stop db2start db2 activate db sample如果force applications all后仍有连接挂着通常是应用连接池没有及时释放需要到应用服务器上确认进程退出。否则db2stop会长时间停在waiting for applications to complete。另外要说清重启解决的是“残留”解决不了“根因”。如果临时表空间是被不可控的大排序打爆的重启完跑同一条 SQL还是会再次打爆。把重启省下来的时间去定位 SQL是这份工作最划算的投入。5. 系统临时表空间踩坑实录五个案例与排查路径5.1 现象临时表空间使用率 100%但“没有 SQL 在跑”某次告警显示系统临时表空间使用率 98%应用基本停摆。LIST APPLICATIONS看不到大量活跃排序MON_GET_TABLESPACE也显示使用率高位徘徊。原因一个长时间运行的REORG TABLE后台任务正在重建索引它不占用应用连接列表中的显式 SQL但持续使用系统临时表空间作为重组索引的工作区。还有一种可能是某个排序代理段卡在Interrupted状态遗留在表空间内没有释放。解决先查后台任务列表确认是否有REORG或RUNSTATS在凌晨批量中遗留。db2pd -d sample -reorgs能看到表级重组状态。如果确实有任务在跑让其自然完成不要再叠加新作业如果是中断遗留的代理段则等待事务超时或安排维护窗口重启。5.2 现象盲目调大 SORTHEAP 后临时表空间反而更满同事在临时表空间告警后把SORTHEAP从 1024 调到 8192结果第二天使用率更高了应用查询普遍变慢。原因SORTHEAP只控制单个排序的内存上限改大后单个排序能吃更多的内存但所有排序共享的实例级SHEAPTHRES没有同步提升。大量的排序并发在内存里互相“抢地盘”竞争不过的排序还是会落盘而且排序本身占用更久临时表空间被占用更长时间。解决调整SORTHEAP时必须同步评估SHEAPTHRES_SHR和SHEAPTHRES。以单次排序 16MB、并发 200 个排序估算共享阈值要留出 3.2GB 以上的空间。这不是越界调参能解决的需要按实际并发量算。5.3 现象加了索引SQL 还是走排序某个ORDER BY查询在排序列上建了索引EXPLAIN一看仍然有SORT节点临时表空间照样被吃满。原因常见有两类。一类是统计信息过期优化器不知道新索引存在或者索引列分布信息失真导致索引成本被高估另一类是谓词条件里对索引列做了函数运算比如WHERE UPPER(cust_name) UPPER(zhangsan)函数让索引失效排序自然无法避免。解决先RUNSTATS重新收集统计信息如果仍然走排序检查 WHERE 条件是否在索引列上套了函数改写为直接等值或范围比较。还解决不了把EXPLAIN输出里的SORT节点完整截图看排序发生在表扫描阶段还是连接阶段针对具体阶段做分区裁剪或者物化查询表优化。5.4 现象db2stop 正常db2start 却失败为了清理临时表空间执行了db2stop和db2start结果数据库实例起不来了报容器相关的错误。原因重启前有人为了“释放磁盘空间”删除了临时表空间的容器文件或者把目录结构做了迁移。DB2 启动时要重新打开所有容器发现文件缺失直接中止启动。这属于典型的临时表空间清理误操作。解决不要手工删除表空间容器文件。系统临时表空间在空闲时也不会自动缩回容器文件大小这是 DB2 的设计不是故障。如果磁盘确实吃紧用db2 alter tablespace调整容器大小或者重建新临时表空间并切换默认指向之后在旧表空间离线状态下再删除容器文件。容器文件的操作永远走 DB2 命令不走操作系统的rm。5.5 现象临时表空间“用完不释放”使用率居高不下更有意思的是查询已经结束、会话已经断开系统临时表空间使用率仍然停在 70%。原因DB2 的系统临时表空间对空间的管理是“占有并复用”。排序或哈希连接完成后空间会归还给表空间内部管理但不一定归还给操作系统。自动存储的表空间按需扩展扩展后不会自动收缩。所以从文件系统层面看临时文件一直很大从 DB2 内部看内存页是可复用的。解决这不一定是故障需要结合MON_GET_TABLESPACE的tbsp_used_size_kb判断。如果内部使用率仍然高查连接池是否还握着空闲连接某些框架的会话在断开后没有真正提交回滚排序段残留。如果只是文件大、内部空闲可以暂时不管旺季调整db2 alter tablespace容量即可。6. 把临时表空间纳入日常监控脚本、阈值与 EXPLAIN 验证6.1 监控脚本采样 db2pd 输出到时间序列文件我习惯在每台 DB2 服务器上放一段采样脚本定时把系统临时表空间使用率写进 CSV故障复盘时能直接拉出时间轴#!/bin/bash # 监控系统临时表空间使用率每 60 秒采样一次 DBsample OUT/var/log/db2/tbsp_$(date %Y%m%d).csv while true; do TS$(date %Y-%m-%d_%H:%M:%S) db2 connect to $DB /dev/null 21 db2 -x SELECT varchar(tbsp_name,20)||,|| tbsp_used_size_kb||,|| tbsp_total_size_kb||,|| tbsp_utilization_percent FROM TABLE(MON_GET_TABLESPACE(NULL,-2)) AS T WHERE tbsp_type 1 | while read LINE; do echo $TS,$LINE $OUT done db2 terminate /dev/null 21 sleep 60 done脚本每秒依赖一个短连接对实例有少量开销。在核心生产库上建议二次开发成守护进程用db2pd替代db2 connect避免监控本身成为连接压力来源。CSV 文件按天切分配合 Grafana 或任何时序平台导入后就能直观看到临时表空间使用率的上升斜率。6.2 阈值怎么设使用率、持续时间和趋势的组合不要只盯着“使用率 90% 告警”这一条规则。临时表空间的特点是只要 SQL 排序结束使用率会瞬间回落单一的百分比阈值只会制造大量无用告警。我一般设三档档位触发条件动作关注使用率 60%持续 10 分钟记录当前活跃 SQL观察趋势告警使用率 80%持续 10 分钟立即执行 db2pd 抓应用通知应用负责人紧急使用率 95%持续 5 分钟准备扩容或切换临时表空间评估是否中断批量趋势比绝对值重要。如果使用率从 20% 升到 60% 只用了 3 分钟即使还没到 80% 的告警线也要提前介入因为它的斜率说明背后有大量排序正在落盘。阈值要与排序溢出率联动判断单独看一方面是盲人摸象。6.3 用 EXPLAIN 验证排序是否真正消除处理完 SQL 后验证环节不能省。用完整的执行计划校验排序是否还在db2 explain plan for SELECT order_no, sum(amount) FROM orders GROUP BY order_no WITH UR; db2exfmt -d sample -1 -o /tmp/exfmt.out打开exfmt.out重点看两个地方一是是否存在独立的SORT节点二是SORT节点下面的Rows估算值。如果之前的问题 SQL 是ORDER BY order_no现在计划里排序节点消失说明索引已成功消除了排序如果排序节点还在但Rows从全表行数降到了小范围说明分区裁剪或谓词过滤起了作用。还要对比改前改后的临时表空间使用率。改完 SQL 后下一个批量窗口重新观察监控 CSV确认峰值是否明显下降。只有当“执行计划无排序 监控峰值下降”两者同时满足这次临时表空间优化才算真正闭环。我做 DBA 这几年系统临时表空间的问题处理了不少慢慢养成了一个习惯启动任何批量任务前先花两分钟看一眼临时表空间基线和排序溢出率而不是等告警响了再冲进机房。临时表空间不是不能涨但每个 DBA 心里都得有一杆秤——它是在帮 SQL 完成工作还是在帮烂 SQL 掩盖问题。前者可以接受后者必须清理。希望这篇文章的做法能帮你在下一次告警来临时少走一点弯路。本文还有配套的精品资源点击获取