简介面向DB2数据库运维人员与性能调优工程师的一份实战案例文档源自某银行真实故障SQL执行时间骤增ACTIVE SESSION异常升高常规检查未见异常最终定位为系统临时表空间TEMPSPACE1异常膨胀至10GB。文档完整梳理了从CPU、内存、I/O、缓冲池命中率等常规指标排查到通过db2top观察活动会话、db2pd分析LATCH等待、收集STACK堆栈、利用db2trc暂停实例抓取现场的分步过程同时解释了临时表空间过大引发资源消耗、内存压力、LATCH竞争和磁盘排序开销增大的机制并给出优化SQL、调整排序参数、清理临时对象等具体解决措施。整个资源为1个doc文档约607KB内容精炼且步骤可复用特别适合遇到临时表空间膨胀或活动会话数飙升的DBA对照排查。该案例已被5898人学习浏览来自一线运维经验具有很高的实战参考价值。1. 系统临时表空间满了DB2 却说自己还有 90% 空闲——这个性能问题别重启了事DB2 告警里出现“临时表空间使用率超过 90%”的时候最诡异的一点是你连上数据库查db2 list tablespaces系统临时表空间明明显示还有大量空闲页可用空间充足可数据库就像被什么东西扼住喉咙——应用连接堆积、查询集体卡在排序和哈希连接上、跑批任务超时。这就是 DB2 系统临时表空间过大引发的性能问题的典型现场空间看着没满性能已经崩了。我当时第一反应也是重启db2stop再db2start瞬间恢复。可第二天同一个时段问题原封不动地回来。能靠重启解决的问题说明不是硬件瓶颈而是 DB2 内部资源的分配和释放出了岔子。这个问题的根源在系统临时表空间的区段分配机制上跟普通的表数据空间完全是两套逻辑。本文就把排查路径、参数设置和后续治理讲清楚给同样被 DB2 临时表空间拖垮过的运维同行一个完整参考。2. 系统临时表空间为什么会“假空闲、真阻塞”先说透分配机制2.1 临时表空间的角色排序、哈希连接和重组的公共垃圾场DB2 里有三种表空间常规表空间存业务数据大型表空间存大对象和索引数据系统临时表空间则专门处理数据库管理器运行期间产生的中间数据。凡是 SQL 执行计划里出现排序sort、哈希连接hash join、去重distinct、分组group by、游标操作以及reorg重建索引和表时产生的临时数据都会往系统临时表空间里写。关键点在于这个空间是“用完即走”的理论上会话结束后区段会被释放。但如果某个 DBA 把它建成了 DMS 表空间又给了个很大的初始大小同时还配置了自动扩展那它就会像一个只进不出的黑匣子——分配出去的空间DB2 不会自动还给操作系统。时间一长文件系统层面这个临时表空间文件越涨越大数据库内部却因为区段被碎片化占用在高峰期申请不到连续的临时页直接报SQL0964C事务无法在临时表空间中分配空间。2.2 排序内存排序溢出临时表空间膨胀的第一推手我处理的案例里九成临时表空间膨胀都跟排序有关。DB2 的排序分内存排序和溢出排序两段式。sortheap参数控制单个排序操作能用的内存上限sheapthres控制所有排序操作共享的内存阈值。当排序数据量超过sortheap限制DB2 会把中间结果写到系统临时表空间这叫“排序溢出”。一个错误的参数配比就能让情况恶化比如sortheap设得太大比如超过 4GB排序大部分在内存里完成临时表空间使用率反而不高但内存会被挤爆sortheap设得太小比如低于 256MB大量排序疯狂落盘临时表空间立刻告急。更隐蔽的是sheapthres设成 0意味着数据库管理器不强制限制总排序内存每个连接都能抢占内存做排序直到数据库内存耗尽。我在生产环境见过sheapthres0配合 2000 个并发连接系统临时表空间一小时内涨了 60GB。2.3 FILO 栈式分配为什么空间满了却无法回收系统临时表空间还有一个特殊机制区段分配遵循类似栈FILO的复用逻辑。DB2 优先复用最近释放的区段而不是从头扫描找空闲区段。这个设计本意是提高缓存命中率但在长会话、大批量排序场景下会出问题一个会话占用了一批靠上的区段释放后只归还了顶部一小块后续会话不断申请新区段只能从当前游标位置继续向上扩展——这就导致底部大量区段明明空闲却永远轮不到复用。最终表现就是临时表空间已经扩展到几百 GB文件系统快撑不住了但db2 list tablespaces showing detail看到的空闲页比例很高。因为空闲页分布在被占用的区段之间不连续排序申请连续页时会话一直等待性能就此崩塌。这解释了标题里“系统临时表空间过大引发的性能问题”的底层逻辑空间大是表象分配策略才是病灶。3. 用 10 分钟定位临时表空间瓶颈监控命令与指标解读3.1 先看空间快照与当前活动语句别急着调参数收到告警后第一步不是改配置而是确认临时表空间的真实状态和哪些语句在占用。按以下顺序执行# 1. 查看表空间总体状态与自动扩展设置 db2 list tablespaces showing detail # 2. 查看系统临时表空间的详细容器信息 db2 list tablespace containers for tablespace_id showing detail # 3. 查看当前占用临时表空间的操作 db2 get snapshot for database on dbname | grep -i temp执行完第一步重点关注State字段是否为0x0000正常以及Max size是否为-1表示不限制自动扩展第二步确认容器文件所在文件系统的剩余空间第三步快照输出里重点看Total temporary space used、High water mark和Temporary space used by active queries。如果高水位线远高于当前使用量说明历史上曾经分配过大量临时空间但未释放这就是“假空闲”的直接证据。3.2 抓出吃临时表空间的“肇事 SQL”空间快照能确认问题但要定位到具体语句需要查活动语句快照。标准做法是用db2pd取数据库级快照再结合MON_GET_TABLE表函数看 TEMP 分区上的读写量-- 查看当前正在消耗临时空间的语句快照 SELECT application_handle, application_name, workload_name, total_sort_time, sorts, sort_overflows, total_execution_time FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-2)) AS U ORDER BY total_sort_time DESC FETCH FIRST 10 ROWS ONLY;MON_GET_UNIT_OF_WORK是 DB2 10.5 及以上版本推荐的监控函数sort_overflows字段如果持续大于 0说明排序溢出正在发生配合total_sort_time能看到是哪些应用在持续吃临时空间。注意不要用 LIST APPLICATIONS 代替它只显示连接状态看不到排序活动和临时空间占用容易漏判。3.3 区分“正常膨胀”和“异常膨胀”看高水位与当前值的差用db2pd -dbsize可以按表空间粒度看分配和已用量这是区分临时表空间膨胀类型最直接的工具# 查看数据库所有表空间的分配细节包括扇区数和扩展情况 db2pd -dbsize -db dbname输出里Total pages是当前文件系统上实际分配的总页数Usable pages是当前可用页数Used pages是已使用页数。如果Total pages远大于Used pages说明数据库管理层次上文件已经被撑大但内部没写满——异常膨胀如果两者接近说明临时表空间确实承载了大量数据需要在语句优化或排序内存上想办法。再配合db2pd -tcbst查看每个表空间的缓存池命中和读操作# 查看临时表空间上的物理读、逻辑读和异步读 db2pd -tcbst -db dbname | grep -A 20 TEMPPOOL_TEMP_DATA_L_READ和POOL_TEMP_DATA_P_READ是临时表空间上的逻辑读和物理读计数。物理读比例高说明溢出严重数据在内存和临时空间之间来回搬运这时候改sortheap比扩临时表空间文件更有效。4. 临时表空间治理避坑4 个高频症状、根因与止血方案4.1 现象SQL0964C报错但表空间实际有空闲页这是误导性最强的报错。应用报“无法在临时表空间分配空间”但查表空间详情明明还有几十 GB 空闲。原因就是 2.3 节说的 FILO 栈式分配中的碎片化——空闲页存在但不连续DB2 分配连续页段失败。解决路径分三步。先看sortheap和sheapthres是否合理具体配比见第五章再看是否有长事务或长会话用MON_GET_UNIT_OF_WORK查执行时长超过 2 小时的会话最后如果确认是碎片化最有效的止血手段是断开所有连接后执行db2 force applications all db2 deactivate db dbname db2stop db2start db2 activate db dbname这里的逻辑是数据库重启会清空所有分配给系统临时表空间的区段让文件恢复到初始大小前提是文件系统支持收缩。如果业务不允许重启就只能通过ALTER TABLESPACE增加容器临时缓解但这不是长久之计。4.2 现象db2stop正常但db2start后临时表空间依然巨大重启后临时表空间文件大小没变化很多人会以为是重启失败。实际上如果临时表空间是用 DMS 类型建的并且PREFETCHSIZE和OVERHEAD配置不当重启后 DB2 会根据表空间定义重新预分配初始空间而不是恢复到最小状态。检查表空间定义时重点看USER子句里的PREFETCHSIZE是否过大比如超过 16MB。同时确认创建语句里是否用 USING 指定了初始大小初始大小定了 100GB那重启后就是 100GB。解决方法是重建系统临时表空间在维护窗口执行db2 CREATE SYSTEM TEMPORARY TABLESPACE SYSTOOLSTMPSPACE \ IN IBMCATGROUP PAGESIZE 32K \ MANAGED BY DATABASE USING (FILE /db2data/temp_ts 5000) \ EXTENTSIZE 32 \ PREFETCHSIZE 64 \ BUFFERPOOL IBMDEFAULTBP这里的核心参数是MANAGED BY DATABASE USING (FILE ... )指定初始文件大小5000 单位是页32K 页大小下约 160MB 起步后续靠自动扩展增长。EXTENTSIZE 32表示每次扩展 32 页PREFETCHSIZE 64是预读 64 页这两个值配合默认缓冲池避免碎片化提前出现。4.3 现象监控脚本发现临时表空间文件每天固定时间暴涨如果暴涨时间点固定比如每天早上 9 点到 10 点大概率是批处理任务集中启动排序操作叠加导致临时空间峰值集中。单纯调大临时表空间只会让峰值更高正确做法是错峰和限流。用db2 list applications查看这段时间的并发连接数再用MON_GET_UNIT_OF_WORK抓这段时间内排序时间最长的应用。如果是 ETL 工具的并行加载可以调整其并发度如果是报表查询可以限制查询优先级。通过这些手段降低峰值期的排序并发远比无条件扩容有意义。4.4 现象历史库的临时表空间连续数月持续增长这种增长通常没有急性故障但文件系统告警不断而且增速稳定。根因大多是统计信息过期导致执行计划选择了大规模哈希连接或排序。DB2 里统计信息直接影响优化器选择排序还是嵌套循环统计信息过期时优化器会高估行数选择排序且分配超大临时空间。解决方案是周期性运行RUNSTATS并且不只是对表对索引也要跑db2 RUNSTATS ON TABLE schema.table WITH DISTRIBUTION \ AND DETAILED INDEXES ALL配合REORG定期整理碎片db2 REORG TABLE schema.tableWITH DISTRIBUTION选项会收集列分布统计信息DETAILED INDEXES ALL收集索引的详细统计。对于大表注意设置UTIL_IMPACT_LIM限制工具影响度避免REORG本身占用过多临时表空间。5. 从止血到长效治理排序内存配比与自动清理方案5.1 排序内存参数setheap 和 sheapthres 的推荐起点sortheap和sheapthres是影响临时表空间使用最直接的两个参数。常见做法是先看数据库内存总量和并发连接数然后按比例设置。我的建议配比是sortheap取数据库共享内存的 2% 到 5%sheapthres取sortheap的 4 到 6 倍。落地命令如下# 查看当前配置 db2 get db cfg | grep -i sortheap # 修改排序堆大小单位是 4KB 页 db2 update db cfg using sortheap 1024 db2 update db cfg using sheapthres 6144这里sortheap 1024表示 1024 个 4KB 页即 4MBsheapthres 6144表示 6144 个 4KB 页即 24MB。修改后无需重启立即生效。注意不要简单地把 sortheap 调到 20000 以上那会让排序全部走内存看起来临时表空间问题没了但内存换页会让整机性能崩掉。判断标准是看 3.2 节的sort_overflows如果调整后该值归零说明排序全在内存完成sortheap还可以小幅下调如果仍持续增长说明内存排序写满了sortheap后才开始溢出再加临时空间意义有限应该从 SQL 层面减少排序。5.2 规划轮询清空任务定期整理临时表空间的碎片生产环境无法频繁重启数据库但可以做一个轻量级的轮询任务每周在低峰时段执行一次“温和清理”即把所有活动连接强制断开后执行一次db2 deactivate db再db2 activate db。这个操作比db2stop/start轻得多效果是让数据库管理器重新评估临时表空间的使用回收部分碎片空间。关键点是不能在业务高峰期做deactivate会断开所有连接。更安全的做法是先用db2 list applications确认活跃连接数低于 10 再执行。如果deactivate后临时表空间文件还是没有收缩说明文件系统不支持在线收缩只能走重建临时表空间路线见 4.2 节命令。5.3 自动化监控脚本提前 3 天预警而不是等告警把这条 SQL 放到服务器 crontab 里每 5 分钟执行一次检查临时表空间使用率和高水位线变化趋势#!/bin/bash DB_NAMEyourdb DB_USERdb2inst1 DB_PASSyourpass db2 connect to $DB_NAME user $DB_USER using $DB_PASS /dev/null 21 # 输出临时表空间使用率和高水位 db2 SELECT TABLESPACE_NAME, CURRENT_USED_PAGES, \ HIGHWATERMARK_PAGES, \ (HIGHWATERMARK_PAGES * 100 / CURRENT_USED_PAGES) AS WASTE_RATE \ FROM SYSIBMADM.TBSP_UTILIZATION \ WHERE TBSP_TYPE SYSTEM TEMPORARY \ ORDER BY WASTE_RATE DESC db2 terminateTBSP_UTILIZATION管理视图会输出每种表空间的高水位页数HIGHWATERMARK_PAGES如果高水位是当前使用量的数倍说明碎片化严重。把WASTE_RATE超过 200% 作为第一优先修复阈值超过 150% 作为预警阈值。脚本配上邮件通知能在业务感知到卡顿前的 2 到 3 天发现问题。5.4 业务层优化减少无序排序的 SQL 改写思路排序内存和临时表空间是下游上游是 SQL 写法。常见减少临时空间占用的改写思路ORDER BY尽量走索引避免显式排序db2expln里看到TBSCAN SORT就要考虑加索引。大表连接优先走 HASH JOIN但要让小表作为内表减少哈希表构建开销。分页查询用FETCH FIRST N ROWS ONLY配合OPTIMIZE FOR N ROWS限制 DB2 为排序预留的临时空间。去掉DISTINCT改用GROUP BY配合COUNT统计减少一步去重排序。曾有一个报表存储过程只是把OPTIMIZE FOR 1000 ROWS加到分页查询里临时表空间峰值就从 70% 降到 20%。这类改动不涉及架构调整POC 阶段就可以快速验证。6. 重建系统临时表空间的完整流程把文件大小收回去并验证性能如果前面的治理手段全部执行后临时表空间文件依然持续膨胀最后一个手段是重建整个系统临时表空间。这套流程的核心思路新建一个临时表空间接管负载删掉旧的高水位表空间让文件回到初始百 MB 级别。前提是数据库必须允许短暂中断业务建议在维护窗口操作。步骤分六步# 1. 确认当前临时表空间 ID 和文件路径 db2 list tablespaces showing detail | grep -A 8 TEMPORARY # 2. 创建新的系统临时表空间初始文件大小不要太大 db2 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE_NEW \ IN IBMCATGROUP PAGESIZE 32K \ MANAGED BY DATABASE USING (FILE /db2data/temp_ts_new 1000) \ EXTENTSIZE 32 \ PREFETCHSIZE 64 \ BUFFERPOOL IBMDEFAULTBP # 3. 把新表空间设置为默认临时表空间 db2 UPDATE DATABASE CONFIGURATION USING DFT_TBSP_NAME TEMPSPACE_NEW # 4. 删除旧的临时表空间 db2 DROP TABLESPACE TEMPSPACE1 # 5. 如果删除时报 5 字节锁等待先确认无活动会话再重新执行 db2 force applications all db2 DROP TABLESPACE TEMPSPACE1 # 6. 验证配置 db2 SELECT TABLESPACE_NAME, TBSP_TYPE FROM SYSIBMADM.TBSP_UTILIZATION执行第 3 步后DFT_TBSP_NAME指向新临时表空间后续新会话默认使用它。第 4 步删除旧表空间时DB2 会同步释放其占用的所有文件空间。如果第 4 步报错常见原因是仍有会话持有旧表空间的游标所以第 5 步先强制断开连接再重试删除。重建完成后观察两个指标一是db2 list tablespaces showing detail里新表空间总页数是否稳定在初始水平二是按第五章的监控 SQL 持续跟踪WASTE_RATE。如果重建后一个月内WASTE_RATE又超过 200%说明排序溢出确实高频发生问题不在空间而在语句。我处理过最头疼的一次重建后第三天temp_ts_new还是涨到了 40GB后来查出是一个 BI 工具每次全量抽取时用了DISTINCT。把它的抽取 SQL 从“先 DISTINCT 后 JOIN”改成“先 JOIN 再 GROUP BY”峰值立刻降回了 5GB 以内。这说明临时表空间治理七分在 SQL三分在数据库参数。希望这个从定位、配参到重建的完整路径能帮你下次碰到同类问题时少走几次弯路。本文还有配套的精品资源点击获取
DB2系统临时表空间“假空闲”导致性能崩溃的定位与治理
简介面向DB2数据库运维人员与性能调优工程师的一份实战案例文档源自某银行真实故障SQL执行时间骤增ACTIVE SESSION异常升高常规检查未见异常最终定位为系统临时表空间TEMPSPACE1异常膨胀至10GB。文档完整梳理了从CPU、内存、I/O、缓冲池命中率等常规指标排查到通过db2top观察活动会话、db2pd分析LATCH等待、收集STACK堆栈、利用db2trc暂停实例抓取现场的分步过程同时解释了临时表空间过大引发资源消耗、内存压力、LATCH竞争和磁盘排序开销增大的机制并给出优化SQL、调整排序参数、清理临时对象等具体解决措施。整个资源为1个doc文档约607KB内容精炼且步骤可复用特别适合遇到临时表空间膨胀或活动会话数飙升的DBA对照排查。该案例已被5898人学习浏览来自一线运维经验具有很高的实战参考价值。1. 系统临时表空间满了DB2 却说自己还有 90% 空闲——这个性能问题别重启了事DB2 告警里出现“临时表空间使用率超过 90%”的时候最诡异的一点是你连上数据库查db2 list tablespaces系统临时表空间明明显示还有大量空闲页可用空间充足可数据库就像被什么东西扼住喉咙——应用连接堆积、查询集体卡在排序和哈希连接上、跑批任务超时。这就是 DB2 系统临时表空间过大引发的性能问题的典型现场空间看着没满性能已经崩了。我当时第一反应也是重启db2stop再db2start瞬间恢复。可第二天同一个时段问题原封不动地回来。能靠重启解决的问题说明不是硬件瓶颈而是 DB2 内部资源的分配和释放出了岔子。这个问题的根源在系统临时表空间的区段分配机制上跟普通的表数据空间完全是两套逻辑。本文就把排查路径、参数设置和后续治理讲清楚给同样被 DB2 临时表空间拖垮过的运维同行一个完整参考。2. 系统临时表空间为什么会“假空闲、真阻塞”先说透分配机制2.1 临时表空间的角色排序、哈希连接和重组的公共垃圾场DB2 里有三种表空间常规表空间存业务数据大型表空间存大对象和索引数据系统临时表空间则专门处理数据库管理器运行期间产生的中间数据。凡是 SQL 执行计划里出现排序sort、哈希连接hash join、去重distinct、分组group by、游标操作以及reorg重建索引和表时产生的临时数据都会往系统临时表空间里写。关键点在于这个空间是“用完即走”的理论上会话结束后区段会被释放。但如果某个 DBA 把它建成了 DMS 表空间又给了个很大的初始大小同时还配置了自动扩展那它就会像一个只进不出的黑匣子——分配出去的空间DB2 不会自动还给操作系统。时间一长文件系统层面这个临时表空间文件越涨越大数据库内部却因为区段被碎片化占用在高峰期申请不到连续的临时页直接报SQL0964C事务无法在临时表空间中分配空间。2.2 排序内存排序溢出临时表空间膨胀的第一推手我处理的案例里九成临时表空间膨胀都跟排序有关。DB2 的排序分内存排序和溢出排序两段式。sortheap参数控制单个排序操作能用的内存上限sheapthres控制所有排序操作共享的内存阈值。当排序数据量超过sortheap限制DB2 会把中间结果写到系统临时表空间这叫“排序溢出”。一个错误的参数配比就能让情况恶化比如sortheap设得太大比如超过 4GB排序大部分在内存里完成临时表空间使用率反而不高但内存会被挤爆sortheap设得太小比如低于 256MB大量排序疯狂落盘临时表空间立刻告急。更隐蔽的是sheapthres设成 0意味着数据库管理器不强制限制总排序内存每个连接都能抢占内存做排序直到数据库内存耗尽。我在生产环境见过sheapthres0配合 2000 个并发连接系统临时表空间一小时内涨了 60GB。2.3 FILO 栈式分配为什么空间满了却无法回收系统临时表空间还有一个特殊机制区段分配遵循类似栈FILO的复用逻辑。DB2 优先复用最近释放的区段而不是从头扫描找空闲区段。这个设计本意是提高缓存命中率但在长会话、大批量排序场景下会出问题一个会话占用了一批靠上的区段释放后只归还了顶部一小块后续会话不断申请新区段只能从当前游标位置继续向上扩展——这就导致底部大量区段明明空闲却永远轮不到复用。最终表现就是临时表空间已经扩展到几百 GB文件系统快撑不住了但db2 list tablespaces showing detail看到的空闲页比例很高。因为空闲页分布在被占用的区段之间不连续排序申请连续页时会话一直等待性能就此崩塌。这解释了标题里“系统临时表空间过大引发的性能问题”的底层逻辑空间大是表象分配策略才是病灶。3. 用 10 分钟定位临时表空间瓶颈监控命令与指标解读3.1 先看空间快照与当前活动语句别急着调参数收到告警后第一步不是改配置而是确认临时表空间的真实状态和哪些语句在占用。按以下顺序执行# 1. 查看表空间总体状态与自动扩展设置 db2 list tablespaces showing detail # 2. 查看系统临时表空间的详细容器信息 db2 list tablespace containers for tablespace_id showing detail # 3. 查看当前占用临时表空间的操作 db2 get snapshot for database on dbname | grep -i temp执行完第一步重点关注State字段是否为0x0000正常以及Max size是否为-1表示不限制自动扩展第二步确认容器文件所在文件系统的剩余空间第三步快照输出里重点看Total temporary space used、High water mark和Temporary space used by active queries。如果高水位线远高于当前使用量说明历史上曾经分配过大量临时空间但未释放这就是“假空闲”的直接证据。3.2 抓出吃临时表空间的“肇事 SQL”空间快照能确认问题但要定位到具体语句需要查活动语句快照。标准做法是用db2pd取数据库级快照再结合MON_GET_TABLE表函数看 TEMP 分区上的读写量-- 查看当前正在消耗临时空间的语句快照 SELECT application_handle, application_name, workload_name, total_sort_time, sorts, sort_overflows, total_execution_time FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-2)) AS U ORDER BY total_sort_time DESC FETCH FIRST 10 ROWS ONLY;MON_GET_UNIT_OF_WORK是 DB2 10.5 及以上版本推荐的监控函数sort_overflows字段如果持续大于 0说明排序溢出正在发生配合total_sort_time能看到是哪些应用在持续吃临时空间。注意不要用 LIST APPLICATIONS 代替它只显示连接状态看不到排序活动和临时空间占用容易漏判。3.3 区分“正常膨胀”和“异常膨胀”看高水位与当前值的差用db2pd -dbsize可以按表空间粒度看分配和已用量这是区分临时表空间膨胀类型最直接的工具# 查看数据库所有表空间的分配细节包括扇区数和扩展情况 db2pd -dbsize -db dbname输出里Total pages是当前文件系统上实际分配的总页数Usable pages是当前可用页数Used pages是已使用页数。如果Total pages远大于Used pages说明数据库管理层次上文件已经被撑大但内部没写满——异常膨胀如果两者接近说明临时表空间确实承载了大量数据需要在语句优化或排序内存上想办法。再配合db2pd -tcbst查看每个表空间的缓存池命中和读操作# 查看临时表空间上的物理读、逻辑读和异步读 db2pd -tcbst -db dbname | grep -A 20 TEMPPOOL_TEMP_DATA_L_READ和POOL_TEMP_DATA_P_READ是临时表空间上的逻辑读和物理读计数。物理读比例高说明溢出严重数据在内存和临时空间之间来回搬运这时候改sortheap比扩临时表空间文件更有效。4. 临时表空间治理避坑4 个高频症状、根因与止血方案4.1 现象SQL0964C报错但表空间实际有空闲页这是误导性最强的报错。应用报“无法在临时表空间分配空间”但查表空间详情明明还有几十 GB 空闲。原因就是 2.3 节说的 FILO 栈式分配中的碎片化——空闲页存在但不连续DB2 分配连续页段失败。解决路径分三步。先看sortheap和sheapthres是否合理具体配比见第五章再看是否有长事务或长会话用MON_GET_UNIT_OF_WORK查执行时长超过 2 小时的会话最后如果确认是碎片化最有效的止血手段是断开所有连接后执行db2 force applications all db2 deactivate db dbname db2stop db2start db2 activate db dbname这里的逻辑是数据库重启会清空所有分配给系统临时表空间的区段让文件恢复到初始大小前提是文件系统支持收缩。如果业务不允许重启就只能通过ALTER TABLESPACE增加容器临时缓解但这不是长久之计。4.2 现象db2stop正常但db2start后临时表空间依然巨大重启后临时表空间文件大小没变化很多人会以为是重启失败。实际上如果临时表空间是用 DMS 类型建的并且PREFETCHSIZE和OVERHEAD配置不当重启后 DB2 会根据表空间定义重新预分配初始空间而不是恢复到最小状态。检查表空间定义时重点看USER子句里的PREFETCHSIZE是否过大比如超过 16MB。同时确认创建语句里是否用 USING 指定了初始大小初始大小定了 100GB那重启后就是 100GB。解决方法是重建系统临时表空间在维护窗口执行db2 CREATE SYSTEM TEMPORARY TABLESPACE SYSTOOLSTMPSPACE \ IN IBMCATGROUP PAGESIZE 32K \ MANAGED BY DATABASE USING (FILE /db2data/temp_ts 5000) \ EXTENTSIZE 32 \ PREFETCHSIZE 64 \ BUFFERPOOL IBMDEFAULTBP这里的核心参数是MANAGED BY DATABASE USING (FILE ... )指定初始文件大小5000 单位是页32K 页大小下约 160MB 起步后续靠自动扩展增长。EXTENTSIZE 32表示每次扩展 32 页PREFETCHSIZE 64是预读 64 页这两个值配合默认缓冲池避免碎片化提前出现。4.3 现象监控脚本发现临时表空间文件每天固定时间暴涨如果暴涨时间点固定比如每天早上 9 点到 10 点大概率是批处理任务集中启动排序操作叠加导致临时空间峰值集中。单纯调大临时表空间只会让峰值更高正确做法是错峰和限流。用db2 list applications查看这段时间的并发连接数再用MON_GET_UNIT_OF_WORK抓这段时间内排序时间最长的应用。如果是 ETL 工具的并行加载可以调整其并发度如果是报表查询可以限制查询优先级。通过这些手段降低峰值期的排序并发远比无条件扩容有意义。4.4 现象历史库的临时表空间连续数月持续增长这种增长通常没有急性故障但文件系统告警不断而且增速稳定。根因大多是统计信息过期导致执行计划选择了大规模哈希连接或排序。DB2 里统计信息直接影响优化器选择排序还是嵌套循环统计信息过期时优化器会高估行数选择排序且分配超大临时空间。解决方案是周期性运行RUNSTATS并且不只是对表对索引也要跑db2 RUNSTATS ON TABLE schema.table WITH DISTRIBUTION \ AND DETAILED INDEXES ALL配合REORG定期整理碎片db2 REORG TABLE schema.tableWITH DISTRIBUTION选项会收集列分布统计信息DETAILED INDEXES ALL收集索引的详细统计。对于大表注意设置UTIL_IMPACT_LIM限制工具影响度避免REORG本身占用过多临时表空间。5. 从止血到长效治理排序内存配比与自动清理方案5.1 排序内存参数setheap 和 sheapthres 的推荐起点sortheap和sheapthres是影响临时表空间使用最直接的两个参数。常见做法是先看数据库内存总量和并发连接数然后按比例设置。我的建议配比是sortheap取数据库共享内存的 2% 到 5%sheapthres取sortheap的 4 到 6 倍。落地命令如下# 查看当前配置 db2 get db cfg | grep -i sortheap # 修改排序堆大小单位是 4KB 页 db2 update db cfg using sortheap 1024 db2 update db cfg using sheapthres 6144这里sortheap 1024表示 1024 个 4KB 页即 4MBsheapthres 6144表示 6144 个 4KB 页即 24MB。修改后无需重启立即生效。注意不要简单地把 sortheap 调到 20000 以上那会让排序全部走内存看起来临时表空间问题没了但内存换页会让整机性能崩掉。判断标准是看 3.2 节的sort_overflows如果调整后该值归零说明排序全在内存完成sortheap还可以小幅下调如果仍持续增长说明内存排序写满了sortheap后才开始溢出再加临时空间意义有限应该从 SQL 层面减少排序。5.2 规划轮询清空任务定期整理临时表空间的碎片生产环境无法频繁重启数据库但可以做一个轻量级的轮询任务每周在低峰时段执行一次“温和清理”即把所有活动连接强制断开后执行一次db2 deactivate db再db2 activate db。这个操作比db2stop/start轻得多效果是让数据库管理器重新评估临时表空间的使用回收部分碎片空间。关键点是不能在业务高峰期做deactivate会断开所有连接。更安全的做法是先用db2 list applications确认活跃连接数低于 10 再执行。如果deactivate后临时表空间文件还是没有收缩说明文件系统不支持在线收缩只能走重建临时表空间路线见 4.2 节命令。5.3 自动化监控脚本提前 3 天预警而不是等告警把这条 SQL 放到服务器 crontab 里每 5 分钟执行一次检查临时表空间使用率和高水位线变化趋势#!/bin/bash DB_NAMEyourdb DB_USERdb2inst1 DB_PASSyourpass db2 connect to $DB_NAME user $DB_USER using $DB_PASS /dev/null 21 # 输出临时表空间使用率和高水位 db2 SELECT TABLESPACE_NAME, CURRENT_USED_PAGES, \ HIGHWATERMARK_PAGES, \ (HIGHWATERMARK_PAGES * 100 / CURRENT_USED_PAGES) AS WASTE_RATE \ FROM SYSIBMADM.TBSP_UTILIZATION \ WHERE TBSP_TYPE SYSTEM TEMPORARY \ ORDER BY WASTE_RATE DESC db2 terminateTBSP_UTILIZATION管理视图会输出每种表空间的高水位页数HIGHWATERMARK_PAGES如果高水位是当前使用量的数倍说明碎片化严重。把WASTE_RATE超过 200% 作为第一优先修复阈值超过 150% 作为预警阈值。脚本配上邮件通知能在业务感知到卡顿前的 2 到 3 天发现问题。5.4 业务层优化减少无序排序的 SQL 改写思路排序内存和临时表空间是下游上游是 SQL 写法。常见减少临时空间占用的改写思路ORDER BY尽量走索引避免显式排序db2expln里看到TBSCAN SORT就要考虑加索引。大表连接优先走 HASH JOIN但要让小表作为内表减少哈希表构建开销。分页查询用FETCH FIRST N ROWS ONLY配合OPTIMIZE FOR N ROWS限制 DB2 为排序预留的临时空间。去掉DISTINCT改用GROUP BY配合COUNT统计减少一步去重排序。曾有一个报表存储过程只是把OPTIMIZE FOR 1000 ROWS加到分页查询里临时表空间峰值就从 70% 降到 20%。这类改动不涉及架构调整POC 阶段就可以快速验证。6. 重建系统临时表空间的完整流程把文件大小收回去并验证性能如果前面的治理手段全部执行后临时表空间文件依然持续膨胀最后一个手段是重建整个系统临时表空间。这套流程的核心思路新建一个临时表空间接管负载删掉旧的高水位表空间让文件回到初始百 MB 级别。前提是数据库必须允许短暂中断业务建议在维护窗口操作。步骤分六步# 1. 确认当前临时表空间 ID 和文件路径 db2 list tablespaces showing detail | grep -A 8 TEMPORARY # 2. 创建新的系统临时表空间初始文件大小不要太大 db2 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE_NEW \ IN IBMCATGROUP PAGESIZE 32K \ MANAGED BY DATABASE USING (FILE /db2data/temp_ts_new 1000) \ EXTENTSIZE 32 \ PREFETCHSIZE 64 \ BUFFERPOOL IBMDEFAULTBP # 3. 把新表空间设置为默认临时表空间 db2 UPDATE DATABASE CONFIGURATION USING DFT_TBSP_NAME TEMPSPACE_NEW # 4. 删除旧的临时表空间 db2 DROP TABLESPACE TEMPSPACE1 # 5. 如果删除时报 5 字节锁等待先确认无活动会话再重新执行 db2 force applications all db2 DROP TABLESPACE TEMPSPACE1 # 6. 验证配置 db2 SELECT TABLESPACE_NAME, TBSP_TYPE FROM SYSIBMADM.TBSP_UTILIZATION执行第 3 步后DFT_TBSP_NAME指向新临时表空间后续新会话默认使用它。第 4 步删除旧表空间时DB2 会同步释放其占用的所有文件空间。如果第 4 步报错常见原因是仍有会话持有旧表空间的游标所以第 5 步先强制断开连接再重试删除。重建完成后观察两个指标一是db2 list tablespaces showing detail里新表空间总页数是否稳定在初始水平二是按第五章的监控 SQL 持续跟踪WASTE_RATE。如果重建后一个月内WASTE_RATE又超过 200%说明排序溢出确实高频发生问题不在空间而在语句。我处理过最头疼的一次重建后第三天temp_ts_new还是涨到了 40GB后来查出是一个 BI 工具每次全量抽取时用了DISTINCT。把它的抽取 SQL 从“先 DISTINCT 后 JOIN”改成“先 JOIN 再 GROUP BY”峰值立刻降回了 5GB 以内。这说明临时表空间治理七分在 SQL三分在数据库参数。希望这个从定位、配参到重建的完整路径能帮你下次碰到同类问题时少走几次弯路。本文还有配套的精品资源点击获取
