临时表空间满、ORA-01652、跑批失败这三个词组合在一起的时候基本就是DBA本月最头疼的时刻。磁盘排序问题在Oracle性能故障里非常隐蔽——它不像锁等待那样能立刻看到阻塞会话也不像CPU耗尽那样监控直接报警。排序发生在SQL执行过程中数据先被算出来再由于内存不够被写到临时表空间表面看起来是“临时表空间涨了”背后却是PGA内存边界、执行计划选择、SQL写法三层因素在互相拉扯。这篇文章我按一次真实故障的排查路径来写从原理讲到定位再到不同层面的解法和一个完整复盘案例尽量把磁盘排序这件事讲透。适合正在做Oracle运维的DBA、负责报表系统性能的工程师也适合刚接触数据库调优、想系统理解排序机制的同学参考。1. 磁盘排序的根子到底出在哪1.1 先从一次真实故障说起某系统的生产库在月初凌晨跑批时挂了报警内容很典型ORA-01652: unable to extend temp segment by 128 in tablespace TEMP。当时监控页面上临时表空间使用率已经冲到99%调度平台上一连串报表任务标红。值班同事第一反应是扩容临时表空间但扩完不到半小时新生成的临时文件又涨了大半。紧接着开始杀会话杀完一个又出现新的。最后顺着v$tempseg_usage找到了一条GROUP BY查询那个SQL本身每天跑一次数据量因为业务活动翻了快三倍排序内存不够整个中间结果全落到了临时段里。这个场景里真正的问题是临时表空间只是“受害者”不是“病因”。排序发生前内存里的排序区先被撑满之后才会向临时段写数据。如果不看SQL、不看执行计划、不看PGA分配单纯扩容临时表空间等于一直给漏水的水桶加水。1.2 什么是磁盘排序内存不够硬盘来凑先讲清楚排序在Oracle里是怎么发生的。SQL里出现ORDER BY、GROUP BY、DISTINCT、集合操作、分析函数时优化器会生成排序算子。排序本身优先在内存里做这个内存区域叫排序区Sort Area归属在PGA的工作区里。当排序需要的内存超过了排序区可用上限Oracle就把中间结果分成多个批次把一部分写到临时表空间的临时段里之后再分批读回来合并。这个过程叫磁盘排序Disk Sort。想象一下整理办公桌手头空间够所有文件摊开就能排序空间不够就得先把一部分文件搬去仓库排序到一半再回来取来回折腾的时间自然就上去了。磁盘排序不是绝对不能有合理的少量spill可以被接受。关键问题在于“排序数据量太大”“排序区太小”“排序次数太多”这三件事同时发生导致SQL几乎全程在搬数据这时候性能就会断崖式下跌。1.3 哪些SQL操作最容易掉进排序的坑排序算子出现在很多地方DBA排查时要心里有数ORDER BY最直接的排序场景尤其对超大结果集排序。DISTINCT去重本质上是一个排序去重的过程。GROUP BY分组聚合优化器可能选择SORT GROUP BY也可能选择HASH GROUP BY。UNION、MINUS、INTERSECT集合操作都要求先排序才能合并。建索引CREATE INDEX构建B树索引时需要先把索引键值排序。MERGE JOIN两个数据集做连接时先各自排序再归并连接。分析函数ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)带排序窗口时分区数据的排序量可能很大。COUNT(DISTINCT col)本质上是DISTINCT加聚合多个去重列组合时排序开销尤其突出。这些操作本身是SQL语法和优化器逻辑的必然要求没法完全避免。重点是判断排序的数据量级如果参与排序的行数只有几千行内存里就完成了如果参与排序的行数是几千万行且排序键很宽那大概率要走磁盘。1.4 排序发生在哪个内存区域排序区不是SGA里的东西它属于PGA。PGA里每个会话都有私有SQL区排序区和哈希区合称工作区Workarea。10g以后默认WORKAREA_SIZE_POLICYAUTO由PGA_AGGREGATE_TARGET统一控制实例所有会话的工作区内存上限。当PGA总目标不够分比如同时有几十个会话都在做大排序Oracle只能降低每个会话的排序区上限让一部分排序spill到临时表空间。这是自动管理下的正常行为但代价是整体IO压力上升。判断一个SQL是否真的发生了磁盘排序最直接的方法是看执行计划的A-Temp列或者查v$sql_workarea视图。这个视图里记录了每个SQL子游标的工作区执行统计包括最优内存、实际使用内存、临时空间消耗排查时能很清楚地看到spill的量。2. 从等待事件和统计信息中抓住“元凶”2.1 等待事件怎么读磁盘排序相关的等待事件主要有这几种direct path write temp会话在向临时段写中间排序结果排序区溢出后高频出现。direct path read temp会话从临时段读回数据做归并排序。temp file resize临时文件自动扩展期间会话要等待文件变大。看到这些等待事件出现在AWR的Top 5里基本可以断定实例层面存在大量临时段IO。但它们只告诉你“有排序IO压力”没告诉你“是哪个SQL”。要往下定位必须结合会话和SQL层面的视图。2.2 全局统计v$sysstat里的sorts指标先做一个快速体检在实例级别看排序的整体健康度select name, value from v$sysstat where name like sorts%;典型的输出是sort (memory)、sort (disk)、sort (rows)三个指标。磁盘排序比率可以简单算disk / (memory disk)这个比率如果长期超过5%说明系统里存在高频或大容量磁盘排序值得深入排查。如果只是某个时间段飙升那就要对齐时间段去看当时的SQL统计。这个方法只能用于整体判断因为它是实例级累计值多个SQL的排序贡献混在一起分不出主次。但它胜在快一条SQL就能确认问题是否存在。2.3 会话级定位v$tempseg_usage和v$sort_segment要抓元凶核心视图是v$tempseg_usage。它记录了当前正在使用临时段的会话和SQL查询方式select se.sid, se.serial#, se.username, t.sql_id, t.segtype, t.blocks * 8 / 1024 as seg_mb from v$tempseg_usage t, v$session se where t.session_addr se.saddr order by t.blocks desc;SEGTYPE字段很关键SORT代表排序段HASH代表哈希连接溢出段LOB代表大对象临时段。很多刚入行的人看到临时表空间涨就说是排序问题结果查下来是HASH类型方向就偏了。v$sort_segment则是从临时表空间段的角度看分配情况可以观察临时段的总区和已用区数量。配合v$tempseg_usage一起用能判断临时段是一直处于占用中还是频繁分配又释放。2.4 AWR报告怎么看如果故障是过去某个时间段发生的当时没抓到现场那就翻AWR报告。重点看三个地方Top 5 Timed Events看direct path write temp和direct path read temp有没有进前五进了说明临时段IO是主要瓶颈。SQL ordered by Physical Reads临时段读写会体现在物理IO上排序量大的SQL往往排在这个列表前面。Segments by Physical Reads如果临时段的读激进可以看到临时文件对应的对象名列在Segments列表里。AWR定位到的SQL不一定是唯一问题源但能圈定一个很小的候选集合配合v$sql里的SQL文本检查基本能锁定方向。2.5 附临时表空间监控SQL我日常值班会用下面几条SQL成本低速度快-- 临时表空间总体使用情况 select tablespace_name, tablespace_size / 1024 / 1024 as total_mb, allocated_space / 1024 / 1024 as used_mb, free_space / 1024 / 1024 as free_mb from dba_temp_free_space; -- 当前各SQL占用临时段情况 select sql_id, segtype, count(*) as segment_count, sum(blocks * 8 / 1024) as temp_mb from v$tempseg_usage group by sql_id, segtype order by temp_mb desc; -- 临时文件扩展状态 select tablespace_name, file_name, bytes / 1024 / 1024 as size_mb, autoextensible, maxbytes / 1024 / 1024 as max_mb from dba_temp_files;这几条SQL组合起来能答清楚“临时表空间用了多少”“谁在用”“临时文件还能不能长”三个问题。建议在监控系统里做成定时抓取每半小时记录一次Top临时段SQL故障回看时非常有用。3. 完整排查实操从出现ORA-01652到锁定SQL3.1 第一步确认临时表空间物理状态报警出现后先别急着扩容先确认问题边界。查dba_temp_files和v$tempfile看临时文件数量、大小、是否自动扩展、最大上限。如果临时文件已经开在最大上限那说明单纯扩容能缓解但解决不了根本问题如果临时文件还有扩展空间但没扩出来那要检查文件系统剩余空间是否足够以及maxbytes有没有被人为设置成固定值。另一种情况是临时表空间处于NO AUTOEXTEND状态这种配置下文件到固定大小就报错排查时也容易忽略。我踩过一次的坑是生产库临时文件autoextensible确实是ON但maxsize被设置成了跟初始大小一样的值表面看能自动扩展实际上早就封顶了。3.2 第二步抓住正在做磁盘排序的会话执行下面这条查询select se.sid, se.serial#, se.username, se.program, se.status, t.sql_id, t.segtype, round(t.blocks * 8 / 1024, 1) as seg_mb from v$tempseg_usage t, v$session se where t.session_addr se.saddr order by t.blocks desc;结果集里seg_mb最大的行就是要找的目标。如果SEGTYPE是SORT拿到SQL_ID继续追如果是HASH问题核心在哈希连接要去看连接顺序和驱动表选择。这个时候不要急着kill会话。先隔几秒再查一次对比同一SQL_ID的seg_mb有没有持续增长。如果持续增长说明排序还在进行中kill掉能暂时释放空间如果数值已经稳定排序可能已经结束只是后续处理被其他环节拖住kill的意义不大。3.3 第三步拿到SQL ID后看执行计划用v$sql取SQL文本确认业务内容select sql_id, sql_text from v$sql where sql_id 目标SQL_ID;然后生成执行计划注意带allstats last参数这样能看到真实执行统计select * from table(dbms_xplan.display_cursor( 目标SQL_ID, null, allstats last peeked_binds));排查时重点看这几个字段A-Rows算子实际返回行数判断估算是否严重失真。A-Temp算子消耗的临时空间出现大数值说明这里发生了磁盘排序。Starts算子执行次数如果排序算子Starts值很大可能是嵌套循环里反复排序单次数据量小但累计量大。有一个很容易被忽略的点执行计划里的BUFFER SORT算子。它不一定产生磁盘排序但如果数据量大同样可能写到临时段。看到BUFFER SORT别直接跳过顺手看一眼A-Temp。3.4 第四步SQL Monitor与10053 trace深挖如果SQL还在运行且感觉执行计划保存的信息不够可以用SQL Monitor。它对运行时间较长的SQL特别有效能看到执行期间的内存和IO变化select dbms_sqlmonitor.report_sql_monitor( sql_id 目标SQL_ID, type TEXT) from dual;SQL Monitor报告里能看到每个算子的执行时长、内存使用、临时空间、并行度等完整信息比display_cursor更直观。如果已经初步判断优化器选错了执行路径想搞明白它为什么没走索引、为什么选了排序连接那就上10053事件alter session set events 10053 trace name context forever, level 1; alter session set tracefile_identifier sort10053; -- 然后执行目标SQL alter session set events 10053 trace name context off;10053的trace文件会把优化器的候选路径、成本计算公式、索引评估结果全部记录下来。定位阶段不一定要细看每一行重点看优化器在排序算子的成本评估里是否考虑了临时空间开销。这个方法比较重建议在已经确认就是某一条SQL、且反复优化无效时再用。3.5 定位阶段的笔记模板排查过程信息多而杂我习惯按固定模板记录方便后续复盘和交接时间会话IDSQL_ID等待事件临时段占用(MB)排序类型备注02:15123,456abc123direct path write temp40960SORT持续增长02:16123,456abc123direct path write temp51200SORT二次确认这份记录在故障处置完后发给开发和业务侧比口头描述清楚很多也方便后续跟踪“优化方案上线后临时段峰值有没有降下来”。4. 解法一SQL与执行计划层面优化治本4.1 先看执行计划为什么排序要去不掉定位到SQL之后先分析排序算子是“不得不排”还是“本可以避免”。优化器生成排序算子的原因主要有三种用户明确要求排序比如ORDER BY。语法逻辑要求有序比如DISTINCT、集合操作、分析函数。连接方式选择了MERGE JOIN需要先行排序。对于明确要求排序的场景要看排序的数据量级和筛选率。如果WHERE条件过滤完只剩下几百行这个排序无伤大雅问题是明明只需要最后20行却把一百万行全部排序了这种场景可以通过改写SQL把“排序数据量”压缩下来。先看执行计划里排序算子前的数据流确认它接收了多少行再结合WHERE条件判断真实需要排序的行数是多少。这里推荐一个排查原则排序前先过滤过滤后再排序永远比排序后再过滤强。4.2 索引设计消除大排序如果SQL里有ORDER BY且排序键正好是索引的前缀列优化器可以走索引有序扫描跳过排序算子。典型例子select * from t where owner :owner order by object_id;如果t表上有(owner, object_id)联合索引执行计划里就不会出现SORT ORDER BY因为索引扫描本身就按照object_id有序返回。GROUP BY和DISTINCT也有类似机制当分组列是索引前缀时优化器可以用SORT GROUP BY NOSORT这个算子不会真的排序而是利用索引顺序直接聚合。设计索引时要注意顺序等值条件的列放在前面排序键放后面。比如上面的例子owner :owner是等值条件object_id是排序键索引就该建(owner, object_id)而不是(object_id, owner)。列顺序反了优化器很可能还是选择排序。4.3 改写SQL减少排序数据量、延迟排序有些SQL没法靠索引消除排序但可以通过改写降低排序成本。一个常见场景是DISTINCT加多表连接。比如select distinct a.order_no, b.product_name from order_head a, order_line b where a.order_no b.order_no;这个写法会把连接后的结果集整体去重排序量大得惊人。改写思路是先去重再连接或者先在子查询里做局部聚合再关联select a.order_no, b.product_name from (select distinct order_no from order_line) a, order_head b where a.order_no b.order_no;另一个场景是ORDER BY发生在子查询里。MySQL里子查询排序经常被忽略Oracle里则容易出现“排序完外层又排序”的浪费。外层需要什么顺序就只在外层排序子查询里能省就省。改写SQL前务必确认业务语义一致尤其涉及DISTINCT和去重逻辑时。实际操作中我会先在测试库跑新旧两个SQL对比结果集数量和数据内容确保没有差异后再上线。4.4 分区键与并行度对排序的影响分区表遇到大GROUP BY时如果分组键正好是分区键Oracle可以在每个分区上分别执行局部聚合只把各分区的聚合结果汇总一次排序数据量大幅下降。这个特性叫分区智能连接或分区裁剪对报表类SQL效果显著。并行度对排序的影响则是双刃剑。并行排序确实会把一个大排序拆成多个并行子任务每个子任务用各自的排序区但它们最终还是要写临时段、再归并整体IO量并没有减少多少。盲目设PARALLEL反而可能导致临时段并发分配让单一临时表空间的段管理更紧张。我的建议是先把串行路径优化到合理范围再考虑并行。并行解决的是“单会话排序太慢”的问题而不是“排序本身不合理”的问题。5. 解法二PGA与临时表空间参数调优治标与兜底5.1 PGA参数pga_aggregate_target、workarea_size_policy确认SQL层面已经无法快速整改但业务必须先跑起来那就调整实例参数做兜底。核心参数是PGA_AGGREGATE_TARGET它决定了实例所有会话工作区的总内存上限alter system set pga_aggregate_target 8G scope both;同时确认WORKAREA_SIZE_POLICY是AUTO否则PGA_AGGREGATE_TARGET对排序区不起作用select name, value from v$parameter where name in (pga_aggregate_target, workarea_size_policy);调参时要注意整个实例的内存规划。一个常见错误是只盯着PGA不够就无限调大结果SGA的Buffer Cache被挤占物理内存不足触发操作系统换页系统整体性能反而更差。经验数值是SGA加PGA不超过物理内存的70%到80%留一部分给操作系统和其他进程。还有一点容易被忽略PGA_AGGREGATE_TARGET是目标值不是硬限制。Oracle会根据负载动态调整工作区分配。这意味着即使参数设置为8G实际PGA使用可能超过也可能低于这个值。查实际使用量要看v$pgastat里的total PGA inuse字段。5.2 给临时表空间扩容与分布兜底方案的第二个动作是临时表空间。先临时扩容解决眼前报警alter tablespace temp add tempfile /u01/oradata/temp02.dbf size 20g autoextend on maxsize 40g;但单纯加文件不够。临时文件放在哪个存储路径直接决定了排序IO的吞吐。如果所有临时文件都挂在同一块磁盘上多个会话同时做磁盘排序时IO会互相争抢。理想做法是把不同临时文件分散到不同挂载路径或不同存储层。11g开始支持的临时表空间组能进一步改善分配均衡。把一个组内的多个临时表空间看成一个资源池Oracle自动把排序段分散到组内的临时表空间而不是所有排序压在一个表空间上create temporary tablespace temp2 tempfile /u01/oradata/temp02.dbf size 20g autoextend on maxsize 40g tablespace group temp_group; alter tablespace temp tablespace group temp_group;把temp和temp2都划入temp_group后实例在分配临时段时会自动均衡单表空间成为瓶颈的概率明显下降。5.3 排序区参数的新旧对比9i、10g时代DBA习惯用SORT_AREA_SIZE控制排序区大小。这个参数在今天依然存在但10g以后默认WORKAREA_SIZE_POLICYAUTO手动设置SORT_AREA_SIZE会被忽略除非把WORKAREA_SIZE_POLICY改成MANUAL。我的经验是不要倒退去用MANUAL模式。AUTO模式下Oracle能够根据整个实例的负载动态调整MANUAL模式只能靠DBA拍脑袋给值。生产库并发会话一多固定排序区要么浪费内存要么不够用。非要在会话级控制某个大排序可以在执行前临时修改会话级PGA相关参数跑完再改回来但这也只是应急操作。真正靠谱的还是SQL优化加整体PGA规划两条腿走路。5.4 案例调参之后的效果与边界之前碰到一个环境物理内存32GSGA设置20GPGA_AGGREGATE_TARGET只有4G。某条排序SQL需要8G以上的排序空间内存当然扛不住每次跑批都依赖临时表空间撑场面。当时的处理分了两个阶段。第一阶段把临时表空间从20G扩到60GPGA_AGGREGATE_TARGET调到6G。效果是故障不再出现但SQL执行时间从40分钟涨到2小时跑批依然缓慢。这就是典型的“不报错但不解决问题”。第二阶段跟开发一起改SQL给基础表加了组合索引消除了SORT GROUP BY算子。改造后临时段峰值从40G掉到不到1G执行时间恢复到25分钟以内。这个案例让我印象很深参数调整掩盖了症状给了时间窗口但根因还得靠SQL本身。6. 复盘案例一次月度报表故障的完整处置过程6.1 故障现象与影响某个数据分析平台每月第一天跑渠道报表某次跑批开始后约两小时业务侧反馈报表迟迟不出来。数据库监控显示临时表空间使用率从20%一路飙到98%几分钟后应用日志开始报ORA-01652。调度平台上有多个依赖同一数据库的报表任务一个任务失败后连带后续任务全部阻塞影响范围逐级扩大。6.2 排查链条接到告警后先查了v$tempseg_usage发现一条SQL_ID占用了大约90GB临时段SEGTYPE为SORT。拿这条SQL_ID去查SQL文本核心逻辑大致是select 渠道, 产品线, count(distinct 客户号) from 日交易明细 where 交易时间 上月月初 and 交易时间 本月月初 group by 渠道, 产品线;执行计划显示SORT GROUP BY的A-Temp达到90GB而A-Rows只有几百行。也就是说为了算出几百行的聚合结果对全量明细数据做了大规模排序去重排序输入量达到2.6亿行。进一步对比历史数据后发现本月明细表数据量比上月增长了30%加上月初统计信息未及时更新优化器对该SQL的基数估算严重偏低选用了对内存最不友好的执行路径。6.3 根因与处置动作根因有两个层面一是COUNT(DISTINCT 客户号)这个计算本身需要去重排序参与去重的数据量又特别大。 二是统计信息陈旧导致优化器低估了数据量没有选择更优的聚合路径。处置动作分三步第一步先取消当前故障跑批释放临时段恢复其他报表任务。第二步临时扩容临时表空间保证业务当天能跑完。第三步跟开发确认业务语义后改写SQL先把明细表按渠道、产品线、客户号去重再在外面做聚合统计避免对2.6亿行全量做一次大去重排序。同时更新对应表的统计信息。改写后的SQL临时段峰值降到了500MB以下跑批时间从3小时缩短到40分钟。6.4 事后措施与监控这个故障之后做了三项改进。第一月报跑批前增加了统计信息检查步骤确认相关大表已收集最新统计信息后再启动任务。第二在监控平台上增加了临时段Top SQL的定时采集每半小时记录一次v$tempseg_usage聚合结果出现临时段异常增长时可以回溯到具体SQL。第三预留临时表空间容量按“历史最大消耗量的1.5倍”规划不再只看当前业务数据量。同时把临时文件分散到两个存储路径减少单个文件系统的IO压力。7. 常见问题速查与排错心得问题快速定位方法处理思路磁盘排序比率从哪里看v$sysstat里sorts系列指标计算disk占比长期偏高则深入排查PGA调大了还是磁盘排序v$sql_workarea查看单条SQL的TEMP_SPACE单个SQL需求内存超过PGA上限时只能优化SQL临时表空间足够大还报ORA-01652查dba_temp_files的maxbytes和磁盘剩余空间检查autoextend上限设置或文件系统空间不足排序会话杀了又出现查v$tempseg_usage的SQL_ID和来源PROGRAM定位作业调度来源从任务调度层面停掉重复触发临时表空间涨了但不清楚谁在用v$tempseg_usage按SQL_ID聚合关联v$session拿完整会话信息如何区分排序和哈希溢出看v$tempseg_usage的SEGTYPE字段SORT处理排序HASH处理哈希连接7.1 问磁盘排序比率从哪里看实例级看sorts (disk)和sorts (memory)的比值。会话级则看目标SQL在v$sql_workarea里的TEMP_SPACE字段。如果TEMP_SPACE大于0说明该SQL执行时发生过磁盘排序数值越大排序溢出的数据量越大。7.2 问PGA调大之后还是磁盘排序排序需要的内存和工作区分配是两回事。如果一条SQL的排序键特别宽、参与排序的行数特别多单次排序请求的内存可能超过PGA能够分配的上限。比如PGA配了16G但某个排序需要32G内存才能完全在内存中完成那它无论如何都会spill。这种场景继续调PGA没有意义核心思路是把排序的数据量降下来或者把排序键改窄。7.3 问临时表空间足够大还报ORA-01652ORA-01652的完整含义是“无法在临时表空间中扩展临时段”。表空间总容量够大不代表能继续扩展。常见原因是临时文件的maxbytes设置过小、磁盘分区没有剩余空间、或者临时文件所在文件系统达到了单文件大小上限。用前面给的dba_temp_files查询一查便知。7.4 问排序会话杀不掉或者不断出现排查时先看会话来源。如果来自应用连接池杀了一个连接池会立刻创建一个新会话。正确的做法是通知应用侧停掉对应任务或摘除连接池节点先断源头再清存量。如果来自作业调度平台要找到调度配置里对应的作业名称把调度暂停否则杀了也没用。对已经卡死的会话alter system kill session如果长时间没生效可以加immediate但操作前要确认该会话没有未提交事务。排错心得方面我自己踩过几次坑之后的体会是磁盘排序问题不要一上来就调参。排序涉及SQL写法、统计信息、执行计划、PGA、临时表空间五个层面纯靠调参很少能真正解决问题。正确的顺序是先恢复业务再定位SQL然后分析执行计划最后才考虑参数和容量调整。一个小技巧是平时就把v$tempseg_usage的Top SQL采集做成定时任务数据积累下来之后很多临时表空间问题都能在故障发生前看出苗头。我在实际运维里也发现凡是能提前看出“临时段峰值在逐月上涨”的库最后都能在故障发生之前完成SQL优化而不是等到ORA-01652把跑批打断之后再熬夜救火。
