一个常见场景业务量没怎么变某天运维告警突然弹出数据库所在主机I/O占用冲到了很高应用响应也跟着变慢。登录数据库拉了一份AWR排在前面的等待事件赫然是direct path write tempTop SQL里一条报表语句的磁盘排序次数比平时高了几十倍。熟悉Oracle的人看到这个组合第一反应就是磁盘排序把临时表空间和I/O拖下水了。这篇文章就围绕Oracle磁盘排序问题从现象定位、根因分析、参数调整、SQL改写、效果验证到避坑经验把我实际排查这类问题的完整套路整理出来。适合正在被排序性能问题困扰的DBA也适合开发人员理解为什么一条简单的ORDER BY会把数据库搞垮以及后续写SQL时怎么提前避开这些坑。1. 问题初现怎么确认你遇到的是磁盘排序1.1 磁盘排序到底是什么先讲清楚一个基础概念Oracle在执行ORDER BY、GROUP BY、DISTINCT、UNION这类操作时必须把数据排好序才能返回正确结果。排序过程需要一块内存区域也就是PGA里的工作区work area。如果待排序的数据量比较小PGA工作区装得下这个排序就是内存排序速度很快如果数据量太大内存放不下Oracle就会把中间结果写到临时表空间对应的临时文件中等排完再读回来。这一步“写出去再读回来”就是磁盘排序。磁盘排序本身不是一个错误它只是一种排序方式。真正的问题是当大量会话同时发生磁盘排序或者在短时间内反复触发大排序时临时表空间的I/O会成为系统瓶颈AWR里的direct path read temp、direct path write temp等待事件会飙升最终表现为整体性能下降。可以这样理解手工整理一堆卡片桌面放不下就得往地上摊整理到一半还得弯腰去翻地上的卡片来回折腾。偶尔地上放几摞没事如果每天都有几百人在同一个房间同时往地上扔卡片房间过道都会被堵死。磁盘排序的问题本质就是“房间的地板”被塞满了。1.2 三大定位入口AWR、动态视图、会话事件排查的第一步是确认问题真的来自磁盘排序而不是误判成业务系统的其他问题。我一般按三个入口确认顺序很重要。第一个入口是AWR报告。打开AWR先看Top Foreground Wait Events如果direct path write temp、direct path read temp排在前几位磁盘排序的可能性就非常大了。再往下看Tablespace StatisticsTEMP表空间的占用如果明显偏高基本可以锁定方向。最后看SQL Statistics留意Top SQL里有没有磁盘排序相关指标异常偏高的语句。第二个入口是动态性能视图。用v$sysstat直接看数据库总的排序计数器执行这句SQLSELECT name, value FROM v$sysstat WHERE name IN (sorts (memory), sorts (disk));如果sorts (disk)和sorts (memory)相比占比持续超过5%说明数据库整体排序健康度已经偏低了。这个问题下面还会细讲这个比例是我判断是否需要治理的核心指标之一。第三个入口是锁定正在发生磁盘排序的会话使用v$sort_usage视图SELECT s.sid, s.serial#, s.username, s.program, u.tablespace, u.segtype, u.blocks, u.extents FROM v$session s, v$sort_usage u WHERE s.saddr u.session_addr ORDER BY u.blocks DESC;这个视图会直接告诉你哪些会话正在临时表空间里建立排序段、排序段占了多少块。如果查询结果里某条记录的blocks特别大那基本就是“肇事SQL”的实时现场。1.3 一个定位案例临时段堵住了整个系统前几个月处理了一个典型场景简单复述一下过程某业务系统的监控在上午十点报警数据库主机I/O写等待非常高应用侧大量查询超时。我登录数据库后AWR报告的top等待事件里direct path write temp排第一接着用v$sort_usage查发现三个会话同时持有较大的SORT段其中一个段占到了几千个块。再关联v$session和v$sql定位到这三个会话执行的同一类SQL一条多表关联加GROUP BY的统计报表查询区间覆盖历史数据每次跑都会拉几百万行进排序区。临时表空间里的临时段被这几个会话撑爆后后续更多语句无路可走所有排序都堵在同一个临时文件上I/O自然扛不住。这个案例的排查路径非常标准现象 - 等待事件 - Top SQL - 排序视图 - 临时表空间。这套顺序也推荐新手照抄不要一上来就翻参数先找到“谁在排序”和“在哪个SQL里排序”方向远比细节重要。2. 根因分析排序为什么会溢到磁盘2.1 PGA工作区和排序的内存地图Oracle的PGA里专门有一块区域叫SQL工作区排序操作、哈希连接、位图合并都会用到它。自动PGA内存管理模式下Oracle会根据PGA_AGGREGATE_TARGET参数的值动态给每个活跃操作分配一块内存。关键点在于PGA_AGGREGATE_TARGET是一个总预算不是给单个排序操作的上限。数据库里同时有几十个会话在排序预算就要被切分成几十份如果其中某个排序数据量特别大超过它分到的那份内存溢出就发生了。单个操作最多能拿多少内存可以查v$pgastat里的global memory bound字段这个值会随着并发量动态变化。实际运维中常见的一种误解是排序溢出就一定是PGA配置太小。其实并发才是催化剂两个大排序错峰跑可能完全没问题十个小排序同时跑反而把内存切得很碎导致每个都不够。所以底下分析问题时我会把PGA总量、单操作内存上限、并发排序量三个因素一起看单看哪一个都容易误判。2.2 参数层面PGA_AGGREGATE_TARGET与旧SORT_AREA_SIZE这里有一个非常容易踩的坑很多老资料还在教大家设置SORT_AREA_SIZE但在自动PGA管理模式下这个参数已经被忽略了。你改了SORT_AREA_SIZE排序内存还是按自动管理的逻辑分配等于白改。注意我的说法是“自动PGA管理模式”下忽略只要PGA_AGGREGATE_TARGET大于0就属于这种模式或者说它处于启用状态。所以排查时先确认不要在一个无效参数上浪费时间。真正要关注的参数是两个ALTER SYSTEM SET PGA_AGGREGATE_TARGET 4G SCOPEBOTH; ALTER SYSTEM SET PGA_AGGREGATE_LIMIT 8G SCOPEBOTH;PGA_AGGREGATE_TARGET是软目标Oracle会在总量约束下动态调整分配PGA_AGGREGATE_LIMIT是硬上限任何会话的PGA总占用都不能超过这个值超过就触发进程终止。在11g及以上版本里我强烈建议同时设置这两个参数不然一旦PGA失控操作系统内存都可能被打爆。不过要注意调大PGA不一定能根除磁盘排序。它更像是给缓冲池扩容能缓解一部分压力如果SQL本身排序需求极大调再大也会溢到磁盘只是溢出的时间点往后延迟了而已。2.3 SQL写法哪些写法天然容易引发大排序从SQL文法上看有几类写法天生就容易制造磁盘排序。第一类是不必要或多余的排序SELECT列表里取了很多不相关字段却带上了ORDER BYUNION的去重语义要求排序但业务上根本不需要去重GROUP BY后面跟了索引覆盖不到的列组合。第二类是排序量超大的查询没有过滤条件的统计报表时间段跨度很大的汇总查询几百万行数据一次性进排序区。第三类则是驱动顺序不合理的多表连接中间结果被放大后再排序排序量呈几何倍数上涨。举个例子有两张表订单表和订单明细表各几百万行。有人喜欢这么写统计SELECT customer_id, COUNT(DISTINCT order_id) FROM orders GROUP BY customer_id;COUNT(DISTINCT order_id)这个去重操作本身就隐含了一次排序GROUP BY再来一次极容易出现磁盘排序。如果业务上order_id本来就是唯一的完全可以把COUNT(DISTINCT order_id)改成COUNT(order_id)排序量直接少一大截。可惜很多代码是复制来的业务约束没人较真这类低效排序就这样常年躺在线上。2.4 临时表空间被忽视的第二道防线临时表空间是排序溢出的最终落点。很多系统长期不关注TEMP表空间的大小和增长趋势等性能出问题时才发现临时文件已经写满了甚至出现ORA-01652错误直接导致SQL失败。这里有几个细节值得说。临时表空间使用的是临时文件tempfile不是普通数据文件它的写入不走重做日志排序列不备份不恢复这本身没问题。但临时文件必须合理规划大小和自动扩展策略。很多默认安装的数据库TEMP文件初始大小很小虽然开了AUTOEXTEND但MAXSIZE设得不够或者所在磁盘空间不足高峰期排序量一大就直接顶到天花板。另外我建议有条件的系统考虑使用临时表空间组。把多个临时表空间加入同一个组Oracle会在组内多个临时文件间分发排序段能明显降低单文件I/O压力。这个方案比单纯把单个临时文件调大更抗并发。3. 解决方案从内存到SQL的完整治理3.1 方案一正确调整PGA配置调PGA不是拍脑袋给个数要先看现状。执行以下SQL查看PGA关键指标SELECT * FROM v$pgastat;重点看几个字段aggregate PGA target parameter是当前目标值aggregate PGA auto target是自动分配池大小global memory bound是单操作最大可用内存total PGA allocated是当前实际分配量。如果total PGA allocated经常接近target且global memory bound偏小说明PGA确实紧张。具体调多大我的参考算法是以物理内存为基数先减掉SGA占用再减掉操作系统和其他进程的预留剩余空间里拿出合理比例给PGA。举例一台物理内存64G的数据库服务器SGA分配了24G操作系统和运维工具预留8G剩余约32G那么PGA_AGGREGATE_TARGET可以先给16G到20GPGA_AGGREGATE_LIMIT设成24G到28G。如果是OLTP环境PGA目标可以保守一些20%到30%的内存总量如果是报表分析类环境可以放宽到40%同时观察OS内存是否有压力。调参之后一定要观察一天到两天不要当天就下结论。PGA自动管理有逐渐适应的过程而且业务高峰和低谷期的表现差异很大。另外提醒一句PGA_AGGREGATE_LIMIT设置了硬上限后如果调太低反而容易触发进程终止这个参数宁可设得比目标值宽裕一倍左右。3.2 方案二临时表空间的治理与扩容先检查当前TEMP使用情况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;如果free_mb长期很小就需要扩容了。给TEMP表空间增加临时文件是常见做法ALTER TABLESPACE temp ADD TEMPFILE /u01/oradata/temp02.dbf SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 16G;注意控制文件里的临时文件路径不要和普通数据文件混用目录尽量放到独立的磁盘或SSD上因为临时文件的I/O特点是大量突发写入和读取对延迟敏感。如果物理环境支持把多个临时文件分布到不同磁盘效果比单个超大文件更好。更推荐的长期方案是临时表空间组。操作步骤很直接创建第二个临时表空间然后把两个表空间加入同一个组再把默认临时表空间改为组名。之后用户会话的排序段会自动在组内多个临时文件间平衡。CREATE TEMPORARY TABLESPACE temp2 TEMPFILE /u01/oradata/temp03.dbf SIZE 4G AUTOEXTEND ON NEXT 512M MAXSIZE 16G; ALTER TABLESPACE temp TABLESPACE GROUP temp_group; ALTER TABLESPACE temp2 TABLESPACE GROUP temp_group; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_group;这里有个实践细节改了默认临时表空间组之后已经连接的会话仍然沿用原来的临时表空间要等会话重连或重启实例后才完全生效。所以这种变更建议放到维护窗口做否则线上老会话还是争抢旧文件。3.3 方案三SQL改写与索引优化让排序从源头消失我给这条方案的评价是性价比最高但最容易被忽视。加内存、扩TEMP都是治标SQL改写是治本尤其在排序量巨大的场景下SQL不改硬件加多少都追不上业务增长。比较实用的改写方向有几个。第一去掉不必要的去重和排序UNION换成UNION ALL之前先确认业务允不允许重复。第二让排序走索引ORDER BY列和索引前列保持一致执行计划里就不会出现SORT ORDER BY。第三改写GROUP BY的维度结构如果业务上只需要粗粒度汇总就别把明细字段也塞进GROUP BY。第四大查询改成小查询报表类SQL可以先按分区裁剪缩小排序范围再让应用端聚合。举个简化例子。一条SQL从两张千万级表里取数后排序分页SELECT a.id, b.name, a.amount FROM orders a, customer b WHERE a.cust_id b.id AND a.create_time DATE 2025-01-01 ORDER BY a.amount DESC;如果amount上建了索引但create_time过滤条件没有和amount组合优化器没法直接利用索引顺序还是会产生SORT ORDER BY。这时候建一个(create_time, amount)的复合索引同时满足过滤和排序执行计划就会变成索引有序扫描排序操作消失性能提升往往是数量级的。改完之后务必用执行计划验证EXPLAIN PLAN FOR 原SQL; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);看PLAN里还有没有SORT ORDER BY、SORT GROUP BY、SORT UNIQUE、SORT JOIN这些字样。我见过不少开发者改完说没变化结果一查执行计划还是大排序白忙活。3.4 方案四辅助手段与并发控制除了上面三条主线还有一些容易被忽略的辅助手段。检查是否存在强制并行查询的配置或HINT并行排序会放大内存和临时空间的消耗如果业务并发本来就高把不需要的并行退掉能明显缓解排序压力。用这个SQL检查是否有强制并行痕迹SELECT sql_id, sql_text FROM v$sql WHERE sql_text LIKE %PARALLEL%;另外一个思路是错峰执行。大报表类的SQL尽量调度到业务低峰期尤其那些动辄几百万行排序的统计任务不要在高峰期和在线交易抢PGA、抢临时文件I/O。很多系统加了调度组件后这个问题自然就化解了。还有一个值得提的指标v$sql_workarea视图会告诉你每条SQL的内存排序状态optimal代表完全在内存完成onepass代表用了一次磁盘中转multipass代表磁盘来回读写了多次。定期查这个视图把onepass和multipass比例高的SQL收集起来优先级最高。它们才是真正需要治理的对象而内存排序完美的SQL根本不用管。4. 实操验证调优前后的效果怎么量化4.1 建立性能基线调优之前先建立基线不然改完有没有效果全靠“感觉”这是运维工作的大忌。我习惯在固定时间点比如工作日下午两点采集一组数据v$sysstat里的sorts (memory)和sorts (disk)AWR里的direct path write temp等待次数TEMP表空间的使用率以及重点SQL的执行时间和磁盘排序次数。采集动作可以写成简单脚本必要时用定时任务自动落库。核心是保证每次采集的条件一致同一业务时段、同一统计口径这样前后才有可比性。否则拿业务高峰期和低谷期做对比就算什么都没改数字也会变根本看不出效果。4.2 执行调优与效果对比调优动作按顺序来不要一次性全上。我通常先做SQL改写因为它根治问题且风险低然后调整PGA参数最后做临时表空间扩容。每完成一步重新采集一次数据两步之间至少间隔半天到一天确保数据有足够代表性。用一张简单的表格记录对比数据会特别直观指标调优前SQL改写后调优后一周sorts (disk)7241186152sorts (memory)526835322053180direct path write temp等待次数4832231188重点SQL执行时间47秒8秒8秒TEMP峰值使用率97%38%35%这个表格是我虚构的示意数据但形态和真实场景非常接近。注意看一个趋势sorts (memory)基本不变说明业务量没变但sorts (disk)大幅下降直接路径临时写等待也对应下降这就证明了磁盘排序问题被解决了。如果调完PGA后disk sorts还是没有变化那基本可以断定不是内存问题而是SQL本身的问题回头继续改SQL。4.3 长期监控建议问题解决之后监控不能停。我给团队的长期监控项是每周拉一次v$sysstat排序指标计算磁盘排序占比超过5%就重新开排查TEMP表空间使用率设80%告警阈值v$sql_workarea里的multipass语句每周检查一次发现新出现的大排序语句立即处理。监控还有一个容易被忽略的角度随着数据量增长以前能在内存里完成的排序半年后可能就溢到磁盘了。所以这类监控不是做一次就完结而是保持周期性。磁盘排序问题的特点就是偷偷摸摸地来等它明显反映在业务层面时往往已经持续消耗系统性能很久了。5. 常见问题与排查技巧实录5.1 高频问题速查表整理了一张速查表覆盖我在实际工作中遇到最多的几类问题现象可能原因应对方案ORA-01652无法在临时表空间中分配临时段临时文件太小或MAXSIZE限制扩容TEMP或加入临时表空间组调大PGA后磁盘排序没有减少SQL排序量远超内存上限优先SQL改写不要继续加内存PGA总占用接近物理内存PGA_AGGREGATE_LIMIT过高或未设置设置合理的硬上限并监控修改SORT_AREA_SIZE后无效自动PGA管理模式下该参数被忽略放弃该参数调整PGA_AGGREGATE_TARGET临时文件删除/缩容失败存在活动临时段等会话结束后操作或维护窗口重启实例AWR中direct path读临时很高可能是HASH JOIN溢出结合SQL执行计划判断单独处理这个表解决问题的思路比“标准答案”更有用。很多问题表象相似实际原因天差地别拿不准的时候回到v$sort_usage和v$sql_workarea_active去核实别直接套方案。5.2 两个值得收藏的排查技巧第一个技巧是用10032事件跟踪一条SQL的排序细节。对疑似有问题的会话执行ALTER SESSION SET EVENTS 10032 trace name context forever, level 1; -- 再执行目标SQL ALTER SESSION SET EVENTS 10032 trace name context off;生成的trace文件里会记录排序段的分配情况、排序输入行数、溢出后的具体行为。这个事件用于深入分析排序内部机制非常有用比单纯看视图更能理解Oracle到底怎么处理那次排序也方便后续和开发沟通优化方案的可行性。不过生产环境谨慎使用trace文件会产生额外开销跑完记得关闭事件。第二个技巧是实时的“抓现行”方法。遇到性能问题执行这条SQL能立刻看到当前所有正在使用临时段的会话及其SQLSELECT s.sid, s.username, s.sql_id, s.program, u.segtype, u.blocks, u.tablespace FROM v$sort_usage u, v$session s WHERE u.session_addr s.saddr ORDER BY u.blocks DESC;如果连续执行几次每次都能抓到同样的sql_id那这个SQL就是核心症结。拿到sql_id后再去v$sql或dba_hist_sqlstat里查它的历史执行情况判断问题是新增的还是长期存在的。我个人在实际操作中的一个体会是排查磁盘排序70%的时间应该花在“找到那条SQL”上只有30%的时间用在“怎么改配置”上。方向比努力重要很多人一上来就调参数结果问题依旧是因为根本没定位到病灶。另一个小技巧是改完SQL后不要让同事只测一次就说OK至少要覆盖全量不同输入条件的数据因为有些排序量大的分支可能在特定日期、特定客户维度下才会被触发。最后再分享一点如果条件允许把简单的磁盘排序问题顺手做成一个自动化巡检脚本每周跑一次把sorts (disk)占比和multipass语句自动汇总到一张表里。你会发现用这种“防患于未然”的方式比每次故障临时救火要舒服得多。