如果AWR报告或v$session里突然冒出一大片direct path write temp等待十个里面有八个是某个视图查询把排序、哈希连接撑爆了PGA结果临时表空间被疯狂写入。我在一套Oracle 19.17环境上处理过一个典型的性能事故一个基于多层视图的报表查询平时跑几十秒某天突然变成十几分钟v$session里大量会话卡在direct path write temp事件上ASM磁盘组的IO也被打到接近饱和。这篇文章我会把整个排查链路、背后原理、19.17版本相关的坑以及最终如何靠改写视图把问题彻底解决的过程完整拆开讲适合正在面对类似等待事件的DBA、性能优化工程师以及想搞懂临时表空间和ASM IO关系的Oracle使用者。1. 先还原现场一段查询卡死的完整特征1.1 等待事件暴增时我们在v$session里看到了什么当时的现象很典型前端报表页面上一个需要汇总历史订单的查询开始超时紧接着监控告警说临时表空间使用率超过85%。登录数据库后用下面这条SQL看会话状态select sid, event, wait_class, p1, p2, p3, seconds_in_wait, state from v$session where type ! BACKGROUND order by seconds_in_wait desc;输出里密密麻麻全是direct path write temp状态为WAITING等待类为I/O。p1是临时表空间的表空间号p2是文件号p3是块号。从p3的增长频率能看出这些会话在持续往临时文件里写数据而不是偶尔写几块就结束。辅助看会话的IO统计同样能确认问题select s.sid, s.event, s.wait_time_micro, sn.value / 1024 / 1024 as mb_read_or_write from v$session s join v$sesstat sn on sn.sid s.sid join v$statname name on name.statistic# sn.statistic# where s.type ! BACKGROUND and name.name in (physical read total bytes, physical write total bytes) order by s.event nulls last;这里要说清楚一个容易判断错的地方direct path write temp是在写临时表空间不是写业务表空间。它对应的是排序溢出、哈希连接溢出这类需要临时段承载中间结果的操作。会话里一旦出现大量这种等待通常意味着某个或某几个SQL在内存里装不下中间结果集了。1.2 用AWR和ASH锁定真正的SQL光看到等待事件还不够关键是要找到是谁在制造这些temp写操作。AWR里Top 5事件基本被direct path write temp和direct path read temp占满SQL ordered by Disk Reads那一节会跳出几个读取量异常大的SQL_ID。为了精确锁定我直接查ASHselect sql_id, count(*) as ash_sample_cnt, round(avg(time_waited)/1000, 2) as avg_wait_ms from gv$active_session_history where sample_time sysdate - interval 2 hour and event direct path write temp group by sql_id order by count(*) desc fetch first 10 rows only;结果里排第一的SQL_ID对应的就是一个包装了两层视图的查询。再配合sverage这类工具或者直接查v$sqlarea可以看到这个SQL的disk_reads、temp_space_allocated都高得离谱select sql_id, disk_reads, direct_writes, temp_space_allocated/1024/1024 as temp_mb, elapsed_time/1000000 as elapsed_sec from v$sqlarea where sql_id 你的SQL_ID;这里temp_space_allocated反映的是语句执行过程中累计申请的临时空间能看到它超过几十GB的话基本可以断定计划里出现了超大中间结果集。1.3 别急着下结论区分temp读与temp写等待事件要成对看。direct path write temp是把数据写到临时表空间direct path read temp是从临时表空间把之前写出来的数据再读回去。两者同时大量出现说明某个执行步骤把很大的中间结果写出去之后还要再读进来做后续连接或排序。如果只有write没有read可能是会话还在不断追加临时数据如果read占大头说明问题已经进入“写出来又读回去”的阶段这个阶段IO放大效应会更明显。2. direct path write temp背后的内存与临时段机制2.1 排序与哈希连接如何“溢出”到临时表空间要理解这个等待事件先要建立一张内存与磁盘的关系图。PGA里专门有一块区域叫workarea用来执行排序、哈希连接、位图合并等操作。workarea_size_policyauto时Oracle会根据pga_aggregate_target动态给每个操作分配内存。比如一个ORDER BY或者GROUP BY如果参与排序的数据集超过了分配给操作的sort area数据库不会强行在内存里硬扛而是把中间结果写成临时段借助磁盘完成排序。这个写入过程在等待事件上就是direct path write temp。哈希连接同理。两张表hash join时需要把其中一张表通常是较小的那张加载进内存构建hash table。如果内存不足以容纳整张表就会采用“分区-溢出”的策略把部分分区写到临时表空间。哪个会话需要写哪个会话就会看到direct path write temp等待。用一个生活化的类比内存就像办公桌上的临时堆放区能放多少要看你分配到的桌面大小临时表空间是房间外的应急货架桌面放不下就把东西往货架上搬。搬的过程必然耗费时间而且后面要用还得再搬回来。direct path write temp就是“往货架搬”的动作。2.2 有利的正常写入与害人的超量写入这里必须强调不要一见到direct path write temp就认为是故障。数据库里很多常规操作本身就会设计用direct path写临时段例如大规模数据加载时直接路径加载会绕过buffer cache写数据文件CREATE INDEX、ALTER TABLE ... MOVE也会产生direct path操作。只要等待量和频率在合理范围内这反而是高效路径。真正的问题出在“持续、大并发、超大结果集”这三个特征上。比如单个会话每秒写几百MB连续写几分钟说明执行计划里一定有一个巨型中间结果被反复落盘。此时重点不是盯着等待事件本身而是回答一个问题这个SQL为什么需要这么大的workarea / temp空间回答路径通常分两条计划不合理比如过滤条件没下推、视图嵌套导致中间结果集被放大并发度太高每个会话都需要几GB的workareaPGA总量不够分。2.3 站在会话与段两层观察temp用量定位到SQL只是第一步还要看清楚temp段到底被谁占着。当时我同时查了这两个视图-- 查看会话级临时段使用情况 select s.sid, s.username, t.tablespace, t.segtype, t.blocks * 8 / 1024 as temp_mb from v$tempseg_usage t join v$session s on t.session_addr s.saddr order by temp_mb desc;v$tempseg_usage里的segtype很关键常见值包括SORT、HASH、DATA。如果是HASH占大头说明哈希连接溢出了如果是SORT占大头则是排序、去重、分组类操作溢出。另外还要看排序段历史统计-- 查看临时段空间占用历史 select to_char(begin_time, hh24:mi) begin_time, tablespace_name, segtype, sum(space_used_delta) / 1024 / 1024 as temp_mb from dba_hist_seg_stat where begin_time sysdate - interval 1 day and segtype in (SORT, HASH, DATA) group by to_char(begin_time, hh24:mi), tablespace_name, segtype order by begin_time desc;这一步能还原temp使用量随时间的曲线用来和业务高峰、SQL执行时间对齐确认问题是否集中发生在某个具体窗口。3. ASM IO瓶颈从数据库等待反推存储侧状态3.1 判断IO慢是否由ASM导致的三个信号Oracle 19.17环境里临时表空间基本都放在ASM磁盘组上。direct path write temp等待本来就会带来大量IO但究竟是“SQL消耗了太多IO”还是“ASM本身IO性能存在问题”必须分开判断。我总结出三个典型的信号信号一v$event_histogram里写等待的时间分布明显偏大。等待时间一直堆积在4ms甚至16ms以上说明单次IO服务时间过长不单纯是写量大的问题。信号二使用asmcmd lsdsk查看磁盘组内单盘延迟发现某一块盘的read_time、write_time明显高于同组其他盘比如其他盘平均2ms某块盘却持续20ms以上这就是典型的热盘或不均衡分布。信号三GI告警日志里出现了磁盘组rebalance或节点心跳超时相关记录。rebalance期间ASM实例在搬移extent会额外占用IO带宽如果恰好和业务尖峰撞在一起就会放大临时写等待。3.2 用asmcmd与磁盘组视图看IO分布当时我在ASM实例上执行了下面几组命令# 查看磁盘组磁盘信息与字节级IO asmcmd lsdsk -k DATA asmcmd lsdsk -k TEMP第一列能看到每块磁盘的total_mb和free_mb后面几列是read/write次数与时间。重点看两点各盘free_mb是否均匀各盘read_time/write_time是否悬殊。如果想从SQL层面看查询v$asm_disk_stat更直接select group_number, disk_number, name, total_mb, free_mb, reads, writes, read_time, write_time, round(read_time / nullif(reads, 0), 2) as avg_read_ms, round(write_time / nullif(writes, 0), 2) as avg_write_ms from v$asm_disk_stat order by group_number, disk_number;当时TEMP磁盘组里一块盘的表层平均写延迟达到12ms其余盘只有2-3ms。再配合v$asm_operation看有没有正在执行的rebalance操作select group_number, operation, state, power, actual_power, sofar, est_work, est_minutes from v$asm_operation;确认没有rebalance在跑那问题就集中在盘的物理性能或extent分布上。3.3 rebalance、小AU与坏盘拖累整条链路的经验ASM这块我想多说几句实际经验。第一AUAllocation Unit大小的选择会影响temp这类高并发顺序写场景。如果磁盘组默认使用1M AU而临时文件又开得很大大文件被切成大量小extent之后并发写时ASM需要频繁做extent指针解析和元数据更新对IO路径有一定放大。19c里新建磁盘组我一般建议根据上层的块大小和IO特征评估OLTP用默认值没问题但如果专门放临时文件适当调大AU到4M往往能让顺序写更平滑。注意这是要建组之前就想好的事后改AU很麻烦。第二磁盘组内盘的IO能力必须尽量一致。一块慢盘会拖慢整个组的响应时间因为ASM在读写时可能把IO分散到所有盘上整体延迟会被最慢的那块盘抬高。之前我在一个项目里就遇到过同一磁盘组混合了SSD和普通SAS盘的情况结果就是temp相关等待全部集中指向这组盘最后把慢盘替换掉才恢复正常。第三rebalance的power不要随意调到最大。很多DBA喜欢在需要搬迁数据时把asm_power_limit调成11来加速但代价是整个磁盘组IO瞬间升高。如果这个磁盘组上正好有业务临时表空间或历史表就会造成连锁的等待暴涨。我在操作的时候一般先查v$asm_operation观察当前IO压力再决定用4还是6的power慢慢搬避免一次把存储压垮。4. Oracle 19.17下的已知陷阱和参数切入点4.1 版本相关隐患direct path与temp的坑既然环境是19.17必须把版本因素考虑进去。19c整体的RU体系里不同补丁版本对direct path read/write的行为有细微调整尤其是在PDB架构下临时表空间的管理方式和12c之前差别很大。19.17本身不是最新RU当时在生产上我发现一个现象视图查询在PDB里执行时temp写入的执行计划路径和non-CDB有明显差异某些情况下优化器对并行查询的temp分配策略更激进。印象很深的一个场景是同一套SQL在19.13的测试库上不溢写temp在19.17的生产库上却溢写了几十GB。当时的处理路径是先在MOS上检索该版本和direct path write temp相关的已知问题列表排除掉已被官方确认并修复的bug后再回到SQL本身找原因。这里想强调版本排查是个“排除法”过程不能一上来就觉得是bug而忽略计划本身的问题但也不能完全不看版本差异。如果你也遇到类似场景建议先确认19.17上有没有适用的最新补丁版本同时把两条线并行推进。4.2 PGA类参数调整的先后顺序当direct path write temp被定位为“内存不足”时很多人第一反应是把PGA调大。这个思路没有错但顺序容易搞反。先看当前PGA状态select name, value from v$pgastat where name in ( aggregate PGA target parameter, aggregate PGA auto target, global memory bound, total PGA inuse, maximum PGA allocated );关注三个关键值aggregate PGA target parameter设置的pga_aggregate_target目标值aggregate PGA auto target可自动调优的workarea内存量maximum PGA allocated历史上PGA分配峰值。如果maximum PGA allocated已经逼近或超过pga_aggregate_target说明PGA确实不够用。调参顺序是先看物理内存余量再考虑调大pga_aggregate_limit硬上限与pga_aggregate_target一般建议两者一起动并且pga_aggregate_target不要超过物理内存的一半左右避免给SGA和其他进程留不下空间。但这里必须加一句调大PGA是在SQL已经优化、计划已经确认合理之后的兜底手段。如果SQL本身因为视图嵌套产生了几十GB中间结果你把PGA调到20GB也只是延迟溢出发生的时间点并不改变“中间结果集巨大”这个根本矛盾。4.3 19c新特性在视图查询里带来的双刃剑19c默认开启的实时统计信息收集、自适应计划等特性初衷是让统计信息更接近实际数据。但在视图查询这种多层嵌套场景里它们有时候会引入执行计划波动。比如某次查询中优化器因为实时统计信息判断某个基表选择性很好选择了一个原本不该选用的哈希连接路径结果临时表空间瞬间被写满。我不是让你把这些特性全部关掉。从我的实践看更稳妥的做法是把关键业务视图相关的查询纳入SQL计划管理SPM捕获稳定计划避免优化器在夜间或数据量变化时突然改变路径。19c里可以通过DBMS_SPM实现declare v_plans number; begin v_plans : dbms_spm.load_plans_from_cursor_cache( sql_id 你的SQL_ID, plan_hash_value 1234567890 ); end; /不过还是要再次强调SPM稳定的是计划不是“修正错误计划”。如果当前计划本身就在制造超大临时段绑定它没有意义先改写SQL才是正路。5. 视图查询优化的真正发力点从执行计划倒推写法5.1 视图无法合并时的中间结果灾难终于来到最核心的部分。为什么视图查询会带来这么严重的temp写入问题不在“视图”本身而在于优化器能不能把视图合并到主查询中。当视图包含聚合函数、分析函数、DISTINCT、GROUP BY这类结构时优化器往往无法把外层过滤条件下推到视图内部的基表上只能先把视图内部的子查询完整算出一个中间结果集再与外层进行连接或过滤。举个例子假设有这样的视图create or replace view v_order_summary as select customer_id, sum(order_amount) as total_amount from orders group by customer_id;外层查询select c.customer_name, v.total_amount from v_order_summary v join customer c on c.customer_id v.customer_id where c.region EAST;如果优化器无法把c.regionEAST压进视图内部的orders表那orders表会被全量扫描并做一次全量GROUP BY再把结果和customer表连接最后才过滤region。这中间的全量GROUP BY就是巨大的临时段写入来源。执行计划里看到VIEW算子下面挂着HASH GROUP BY并且VIEW行上有一个非常高的temp space消耗基本就能确认这个情况。更严重的是多层视图嵌套时每一层都可能放大一次中间结果集temp消耗是指数级上升的。5.2 谓词推入让过滤条件真正下探到基表要解决这个问题核心思路是让过滤条件尽可能早地作用在基表上在数据库原理里叫谓词推入predicate pushdown。优化器在部分场景下会自动做这件事但碰到复杂视图自动推入经常失败。人工改写时最直接的手段是把外层过滤条件复制到内层子查询里先缩小参与聚合的数据范围再做聚合。之前的SQL可以改写为select c.customer_name, v.total_amount from ( select customer_id, sum(order_amount) as total_amount from orders where customer_id in ( select customer_id from customer where region EAST ) group by customer_id ) v join customer c on c.customer_id v.customer_id where c.region EAST;或者更简洁地使用WITH子句让逻辑层级更清晰也让优化器有更多改写空间with east_customers as ( select customer_id from customer where region EAST ), v as ( select o.customer_id, sum(o.order_amount) as total_amount from orders o join east_customers ec on ec.customer_id o.customer_id group by o.customer_id ) select v.total_amount from v join customer c on c.customer_id v.customer_id;注意这里的语义变化如果外层还有基于全部客户的其他过滤逻辑改写时要格外小心。我在实际工作中处理这种问题时会把改写前后的SQL各执行一遍分别统计行数和关键字段求和值确保结果完全一致再上生产。5.3 一套可落地的改写步骤与对比验证我给自己总结了一套处理视图查询导致temp暴增的方法论处理上面的案例就是按这个顺序收集统计信息对视图设计到的所有基表执行DBMS_STATS.GATHER_TABLE_STATS确保优化器有完整的数据分布信息。这一步经常被忽略但统计信息过期时优化器对中间结果集大小的估算会严重失真。抓取原始执行计划记录原SQL的plan_hash_value、temp_space_allocated和耗时作为对比基线。尝试去掉一层视图把视图定义展开后合并进主查询观察执行计划中是否还有独立的VIEW算子。这一步能直观看出哪一层视图阻断了谓词下推。使用WITH子句分步改写将过滤条件尽可能下沉到最内层减少早期阶段的处理行数。如果必须保留视图结构可以考虑在视图定义层面把谓词推入逻辑预先处理好比如把大表关联条件放进视图内部。当时生产环境的具体改写效果对比改写前SQL执行耗时约11分钟temp_space_allocated约为46GBdirect path write temp等待事件在ASH中占比超过70%。改写后SQL执行耗时约35秒temp_space_allocated降到3GB左右direct path write temp基本从会话中消失。耗时和temp消耗降了一个数量级ASM磁盘组的IO压力也随之恢复正常。这里再补一个细节如果确实无法改写可以考虑对该SQL使用/* no_merge */或/* merge */提示去调整优化器的视图合并行为但提示方案治标不治本我一般只在极其特殊的情况下使用。6. 实战收尾这套情况下的处理优先级清单6.1 先做短期止血遇到生产环境正在被direct path write temp打爆时我不会一上来就大动作改SQL而是先止血检查临时表空间是否还有空闲空间必要时紧急增加一个tempfile并设置AUTOEXTEND ON防止temp写满导致ORA-1652评估物理内存余量如果充足可谨慎调大pga_aggregate_limit和pga_aggregate_target降低溢写程度如果查询是某个报表独有且可以随时中断先kill掉异常会话恢复系统整体稳定。6.2 再做结构优化止血之后再进入结构优化。针对视图嵌套、中间结果集放大的问题优先考虑的是改写SQL其次是为关联字段补充合适索引最后才考虑调整初始化参数。任何参数调整都需要带上时间跨度验证不能拍脑袋改完就走。6.3 一处容易被忽略的临时表空间细节最后分享一个我在多个项目里踩过的小坑临时表空间不要和业务表空间放在同一个性能较弱的磁盘组里。尽管ASM会自动均衡但temp写入的IO特征通常是高带宽顺序写如果和业务随机小IO混在一起两边都会受影响。更好做法是给临时表空间规划独立的磁盘组优先使用性能较好的物理盘并且把临时文件的initial_extent相关配置与业务数据文件区分开。很多direct path write temp等待其实没有真正落到SQL头上而是因为temp文件所在的存储路径本身兜不住这么大吞吐。可以说把存储层面的基础打牢很多“性能问题”能提前消除一大半。
