做DBA这行最怕的不是系统宕机而是明明没有报错但业务就是慢得离谱。最近处理的一个Oracle 19.17生产环境就很典型一张业务视图的查询前面几条记录秒出总览统计却要跑一分多钟AWR和ASH里疯狂刷direct path write temp等待同事反馈后台ASM实例的IO util也一直居高不下。把视图查询优化、等待事件和ASM IO这几条线串起来排查才算把它彻底摁下去。这篇文章就把这个case的完整思路写出来适合正在被Oracle 19c复杂视图查询性能困扰的DBA和开发人员参考。这类问题有个有意思的地方它不像ORA-错误那样报得明明白白所有指标表面看都“正常”——数据库没锁、会话没阻塞、CPU不高但SQL就是慢临时表空间不停涨ASM磁盘组忙个不停。真正的线索全部藏在等待事件和时间模型里需要一层层剥开。1. 先看现场视图查询慢等待事件与ASM IO双双飙高1.1 典型症状与业务影响这次遇到的业务场景是一个供应链报表系统用户访问某个汇总视图时查看明细正常但一旦按月份、按部门做汇总页面就要转很久。一开始业务方怀疑是网络问题后来发现该视图同时被十几个用户查询时整个库的IO明显升高连磁盘组上其他数据库实例也受到了牵连。从数据库侧观察症状集中在几个方面v$session里出现大量direct path write temp等待等待时间从几百毫秒到几秒不等临时表空间的已用空间快速上升峰值时把默认临时表空间撑到接近上限ASM实例的IO util在高水位线徘徊单盘延迟从1毫秒不到飙升到几十毫秒。与此同时AWR报告里Top 5等待事件清一色的是direct path write temp和direct path read temp连常见的db file sequential read都被挤出了榜单。这种“视图汇总并发”的组合特别容易触发问题。视图内部如果有DISTINCT、GROUP BY、UNION或者大量排序操作优化器如果选择了物化中间结果集的执行计划就会产生大量临时段写入。而临时段写入又是绕过buffer cache直接写盘的所以一旦存储层稍有压力等待时间就会被成倍放大。1.2 一张图理解direct path write tempdirect path write temp字面意思是对临时对象的直接路径写入。所谓直接路径就是数据在排序、哈希连接、分组等操作中工作区不够用时把中间结果写到临时表空间这个过程不走SGA里的buffer cache而是由会话的服务进程直接发起写盘IO。为什么临时数据不走缓存道理很简单临时数据不需要复用写完之后很快就会被读取或丢弃如果还像普通数据块一样放进buffer cache反而会污染缓存把热数据挤出去得不偿失。所以Oracle选择了直接路径写这是一个合理的设计。问题在于当排序量特别大、或者排序频繁发生时这种直接写盘就会变成数据库的主要IO来源等待事件自然就冒出来了。更关键的是direct path write temp等待的不只是“写”本身客户端进程要等待这些临时块写完才能继续下一个阶段的排序归并所以它直接卡在SQL执行的关键路径上。如果底层正好是机械盘、或者ASM层IO调度出现瓶颈这条路径上的每个毫秒都会被放大。我在案例中看到的等待平均时长超过40毫秒放到一个需要多次归并的排序里总耗时自然就爆炸了。2. 追根溯源direct path write temp为什么总盯上ASM2.1 从等待事件名称看临时段写入机制要理解这个问题需要先弄清楚Oracle的排序工作区是怎么运作的。会话执行带排序、分组、去重或哈希连接的操作时会在PGA中划分出一块工作区work area专门用来进行这些运算。9i以后默认WORKAREA_SIZE_POLICYAUTO工作区大小由PGA_AGGREGATE_TARGET结合当前会话负载动态分配而不是再靠SORT_AREA_SIZE这类手工参数。当优化器估算某个操作的输入数据量很大并且估算出的工作区需求超过了当前会话能分到的PGA空间时Oracle就把一部分数据写到磁盘上的临时段称为“one-pass”或“multipass”模式。写盘的过程就是direct path write temp后续排序归并时再读回来对应direct path read temp。这里有个常见的理解误区很多人觉得只要PGA调大就永远不会出现direct path write temp。实际上优化器估算的排序区大小是独立于物理可用内存的。如果一个SQL本身要对上亿行做排序再大的PGA也兜不住PGA只是延迟了这个临界点没有消除问题的根源。另一个容易被忽略的因素是并发度就算单个查询的排序区需求不大但几十个会话同时做排序每个会话分到的工作区都会被压缩一样会出现大量写临时段。2.2 为什么同样的SQL在ASM上被放大把临时文件放在ASM磁盘组上问题会更加明显原因有几个层面。首先是ASM的条带化与分配单元AU机制。默认情况下ASM磁盘组的AU大小是1MBextent也以AU为粒度分配。对于排序归并阶段的大块顺序读写1MB的AU表现尚可但临时段的写出并不是全程顺序的归并过程中读取临时段时会有随机跳跃加上ASM的纵向条带化fine-grained striping在文件级层面把连续块打散到多个磁盘小IO的延迟就会显现出来。其次是ASM实例的资源开销。数据库实例的所有IO都要经过ASM实例的IO server进程如ASMB、RBAL来维护磁盘组状态、完成块指针转换等。当临时段写入特别频繁时ASM实例本身会成为新的瓶颈如果是共享ASM集群还可能影响同一个磁盘组里的所有数据库。最后是底层物理存储的差异。我遇到过把临时文件放在SSD和机械硬盘混合磁盘组里的情况direct path write temp的等待时间能差出五到十倍。排序本来就是“写一次、读多次”的模式存储介质的随机写能力和IOPS直接决定了SQL的最终耗时。ASM只是把数据分散到了更多物理盘上但如果那些盘本身性能差分散反而放大了延迟。2.3 19.17版本相关的优化器行为变化Oracle 19.17是19c较新的Release Update相比早期版本优化器对复杂视图的合并、子查询展开、谓词推导行为会有不少细微变化。经常听到同行说“升级后某个视图查询变慢了”这里面很大一部分不是bug而是优化器对新特性、新统计信息的使用方式变了。比如19c引入了自适应的执行计划、重新优化reoptimization和统计反馈机制一条SQL第一次执行时基于估算选择哈希连接和排序执行后发现实际行数远比估算大会触发第二次优化把计划改成别的形状。这个过程中临时段写入通常会先出现一次高峰。在19.17上如果视图里的表统计信息不完整优化器对基数的估算就会偏离进而选中一个“排序巨大”的计划。还有一个点容易被忽视执行计划里出现COLLECTION ITERATOR这类操作时说明优化器把视图内部结果物化成了嵌套表/临时集合这在19c的复杂视图查询中比较常见。update到19.17后部分视图查询的计划可能会从“视图合并”变成“视图物化”从嵌套循环变成哈希连接排序量可能大幅增加。排查时不要只看SQL文本一定要对比升级前后执行计划的转变。3. 用诊断SQL和报告把“锅”定位到需要优化的那一层3.1 从ASH/AWR看等待事件占比面对“视图查询慢temp写放大ASM IO高”的现场第一步不是改参数而是把证据钉在桌面上。AWR报告是最快的切入手段。在报告的时间段里重点看Top 10 Foreground Wait Events如果direct path write temp排进前三并且等待时间占DB Time比例超过20%基本可以断定临时段写入是主要矛盾。我这次拿到的AWR里这个比例达到了38%已经非常极端了。ASHActive Session History则能更精细地定位到问题SQL和等待的分布。可以执行下面的查询把direct path write temp等待按SQL_ID聚合SELECT sql_id, COUNT(*) AS session_waits, ROUND(SUM(time_waited)/1000000, 2) AS total_wait_sec FROM v$active_session_history WHERE session_type FOREGROUND AND event direct path write temp AND sample_time SYSDATE - INTERVAL 1 HOUR GROUP BY sql_id ORDER BY total_wait_sec DESC FETCH FIRST 10 ROWS ONLY;如果当前正在发生问题直接查v$session_wait_history或者v$session就能看到会话卡在哪个环节。注意捕捉时间窗口因为direct path write temp往往是间歇发生的错过了峰值就看不到。我会让同事保留ASH数据同时抓一个正在执行的SQL的实时执行计划双管齐下。3.2 找出排序特别严重的SQL和工作区用量光靠等待事件还不够还要量化排序到底多严重。v$sql_workarea_active可以直接看会话当前工作区使用情况但这个视图只反映活动状态更常用的是v$sql_workarea和dba_hist_sql_workarea。排查时重点关注三类信息实际内存使用量ACTUAL_MEM_USED、预计内存需求EXPECTED_SIZE、临时段最大使用量MAX_TEMPSEG_SIZE。如果一个SQL的MAX_TEMPSEG_SIZE动辄几个GB而实际内存只有几十MB说明这个SQL的排序量远超出内存承受范围direct path write temp是结构性输出而不是偶发。还可以用下面这个SQL从AWR历史中找workarea的Top消耗者SELECT sql_id, MAX(executions) AS executions, MAX(optimal_executions) AS optimal_cnt, MAX(onepass_executions) AS onepass_cnt, ROUND(MAX(tempseg_size)/1024/1024, 0) AS max_temp_mb FROM dba_hist_sql_workarea WHERE dbid (SELECT dbid FROM v$database) GROUP BY sql_id ORDER BY max_temp_mb DESC FETCH FIRST 10 ROWS ONLY;如果onepass_executions占比很高基本实锤了SQL设计或者执行计划有问题导致大量排序溢出。这个步骤不能省因为它决定了后续优化方向——如果排序本身合理比如总行数就是大但内存分配不足走PGA调整路线如果排序本身不合理走SQL改写路线。3.3 确认ASM IO层是否真的紧张当等待事件集中在temp写入时还要判断ASM IO是受害方还是加害方。判断方法不复杂看IO延迟、服务时间和吞吐量。先看磁盘组的总容量和IO情况SELECT name, total_mb, free_mb, usable_file_mb, state FROM v$asm_diskgroup_stat ORDER BY name;再看单盘IO统计SELECT dg.name AS diskgroup, d.name AS disk_name, ROUND(d.read_time/1000, 2) AS read_sec, ROUND(d.write_time/1000, 2) AS write_sec, d.reads, d.writes, ROUND(d.read_time/decode(d.reads,0,1,d.reads), 4) AS avg_read_ms, ROUND(d.write_time/decode(d.writes,0,1,d.writes),4) AS avg_write_ms FROM v$asm_disk_stat d, v$asm_diskgroup_stat dg WHERE d.group_number dg.group_number ORDER BY avg_write_ms DESC;我当时的判断逻辑是这样的如果单盘avg_write_ms明显高于底层存储设备标称值比如一个标称5ms以内延迟的SSD盘显示平均写延迟达到30ms说明ASM之上存在排队或控制器瓶颈存储侧需要介入如果延迟数据和存储厂商工具显示一致说明IO压力是真打到了硬件上问题还是出在“写给谁、写多少”的SQL层。这个区分很重要因为它决定了要不要动物理存储。动不动就“扩IO、换SSD”是最贵的解法而且未必对症。4. 视图查询优化从SQL到物理存储的完整治理路径4.1 先做SQL层面“减负”确认问题出在视图查询自身后我开始拆业务视图的SQL逻辑。这个视图并不算复杂但有个典型的结构问题视图内部先做了DISTINCT去重还带GROUP BY汇总外部再和另外三张业务表做关联限制条件写在了外部查询里。问题就出在这里。优化器在展开视图时会尝试把外部限制条件下推到视图内部的每个子查询中但视图内的DISTINCT和GROUP BY会阻碍下推。结果就是优化器选择把视图内部的中间结果整体物化到一个临时段先完成去重和分组再和外表关联。物化过程就是对几千万行做排序和去重direct path write temp想不出现都难。针对这个结构我做了三步调整。第一步把能在视图内部提前做的过滤条件移到子查询中让中间结果集变小再分组。第二步把视图内部的一个标量子查询改成普通子查询避免逐行触发临时排序。第三步对可能产生大排序的DISTINCT做语义改写在确认业务逻辑不变的情况下用ROW_NUMBER()窗口函数替代部分去重需求。另一个实用建议是遇到复杂的分析型视图优先考虑物化视图Materialized View而不是每次都临时计算。如果这个视图的汇总粒度是“月份部门产品”报表查询基本都落在同一个聚合粒度上就可以创建对应粒度的物化视图并开启查询重写QUERY REWRITE。数据库会把SQL重写成读物化视图彻底消除运行时的排序写盘。代价是增加存储占用和刷新成本但比起每次让用户等一分钟这笔账只赚不赔。4.2 PGA与临时表空间参数再平衡如果SQL层面已经优化了仍有一部分排序是业务上无法避免的比如全量汇总、大结果集排序那就需要把内存参数调到合适的水平。PGA调整有个基本原则不是无脑调大PGA_AGGREGATE_TARGET而是结合并发数和单会话工作区需求来算。Oracle 19c里PGA_AGGREGATE_TARGET默认是SGA的16%但如果系统里大量跑分析型报表可以适当提高到SGA的20%-25%。还有一个约束是PGA_AGGREGATE_LIMIT它默认比PGA_AGGREGATE_TARGET大200%或2倍具体看版本配置超过这个值Oracle会终止或回退调用这个值也要同步检查避免内存溢出导致DB进程被强杀。查看当前PGA内存管理状态SELECT name, value FROM v$parameter WHERE name IN (workarea_size_policy,pga_aggregate_target,pga_aggregate_limit,parallel_degree_policy); SELECT name, ROUND(value/1024/1024, 2) AS mb FROM v$pgastat WHERE name IN (aggregate PGA target parameter,total PGA inuse,maximum PGA allocated);如果v$pgastat里“total PGA inuse”持续接近PGA_AGGREGATE_TARGET说明内存已经把每个会话的工作区压得很小。此时优先给会话级的必要排序区“开小灶”对于并发不高的分析型SQL可以用ALTER SESSION或SQL hint在会话级别把SORT_AREA_SIZE这类手动参数调大需要兼容模式支持或ACCESS advisor等但生产环境我一般不动手工参数更倾向于用Resource Manager给特定应用用户打高的PGA优先级或者接受一定程度的并行执行以分摊排序压力。临时表空间侧也有操作空间。默认一个临时表空间一个tempfile并发大排序时很容易出现单一文件争用。增加tempfile并启用临时表空间组Temporary Tablespace Group是一个成熟做法把并发排序分散到多个文件甚至多个磁盘组上。在19c中还可以把临时文件放到本地的快速NVMe盘独立磁盘组专门给临时段用效果立竿见影。4.3 ASM与存储层优化如果SQL和PGA都优化过一轮temp写还是你的头号等待事件那就轮到ASM和存储层。ASM层面首先要确认AU大小。19c创建磁盘组时默认AU4MB我习惯按环境确认有的版本默认1MB。对于大体积排序批处理4MB AU比1MB AU更有利因为大AU能减少extent映射开销顺序IO效率更高。但AU在创建磁盘组后不能直接修改如果生产环境用的是1MB AU可以新建一个4MB AU的磁盘组专门存放临时表空间把TEMPFILE迁移过去而不是对现有磁盘组做破坏性操作。其次要关注ASM实例的CPU和调度。ASM实例本身很轻但在大量IO请求时ASM background进程的CPU使用率会升高。如果发现ASM实例的CPU被顶满而数据库实例CPU并不高说明等待时间里有相当一部分花在ASM层的排队上。这种情况优先检查磁盘组是否在做rebalance或resync这类后台操作会抢占IO带宽尽量错开维护窗口把rebalance的power调低让临时段写入优先获得IO资源。存储层方面必须在数据库诊断后拉一把存储人员确认设备指标。重点看磁盘类型SSD/NVMe/HDDHDD做sort work是很痛苦的事情RAID级别和条带大小RAID5写惩罚在临时段这种“频繁写覆盖写”场景下非常吃亏存储控制器缓存策略写缓存是否开启是否命中率高。如果一个存储系统写缓存没开做direct path write temp时每个写请求都会物理落盘延迟翻倍非常正常。这种问题SQL再优化也救不了必须从存储配置上解决。4.4 19.17的优化器hint与计划基线利用版本升级后视图查询计划发生变化是非常典型的19c排障场景。对此我的建议是不要急着改SQL语义先试着用hint和计划基线把计划“钉”回合理形态。最直接的排查手段是切换optimizer_features_enable。比如视图SQL在19.17上变慢可以先用当前环境执行ALTER SESSION SET optimizer_features_enable19.1.0; ALTER SESSION SET optimizer_features_enable18.1.0;分别执行同一条查询对比执行计划和响应时间。如果能稳定复现“旧版本优化器参数下性能更好”那问题基本锁定在新优化器行为上。这时候要么通过SPMSQL Plan Management把好的计划作为基线固定要么用SQL Plan Baseline捕获当前计划再手工演进。19c里我常用下面这组语句给问题SQL创建和执行计划基线固定-- 执行计划基线的捕获与固定核心步骤 DECLARE my_plan CLOB; BEGIN SELECT sql_fulltext INTO my_plan FROM v$sql WHERE sql_id sql_id AND ROWNUM 1; DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id sql_id, plan_hash_value good_plan_hash, sql_text my_plan, fixed YES, enabled YES ); END; /这里要注意LOAD_PLANS_FROM_CURSOR_CACHE里SQL文本和SQL_ID必须严格对应否则会报“ORA-13831”。我把这个底层机制讲明白一点SPM的实质是保存一个“SQL语句特征执行计划集合”的映射执行时优化器优先生成候选计划与基线集合比对只有匹配的才能执行。所以固定基线后就算统计信息变化、优化器版本变化执行计划也能保持稳定。还有一种手段是用SQL Patch打补丁修改优化器对SQL的hint行为而不改动SQL文本。比如视图里不能直接改的地方可以通过DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH强制加NO_QUERY_TRANSFORMATION或者特定的JOIN顺序。不过这类操作有风险上线前必须经过充分的计划和性能验证。5. 优化前后收益记录与常见误解5.1 一次真实环境的优化数据我把这次优化的关键数据整理成了一张表方便大家对照参考。这个例子里原始环境是Oracle 19.17数据库实例为2节点RAC临时表空间放在1MB AU的ASM磁盘组底层为SSD视图查询涉及约4800万行业务数据。指标项优化前优化后变化视图查询总耗时78秒9.5秒降低87.8%direct path write temp等待次数428,59232,104降低92.5%临时段峰值空间MB12,8401,960降低84.7%ASM磁盘组平均写延迟ms31.24.8降低84.6%PGA总使用量MB6,1503,940降低35.9%这个结果是怎么来的主要做了四件事把三个过滤条件下推到视图子查询把视图中一个DISTINCT改为ROW_NUMBER()语义改写把默认临时表空间切到新创建的临时表空间组两个tempfile给视图SQL配置了SPM计划基线锁定优化后的执行计划。PGA并没有大幅调整只是把PGA_AGGREGATE_LIMIT从默认的8GB提到了12GB避免极端并发下的OOM风险。有人可能会问为什么PGA没有大幅调大direct path write temp还降了这么多因为问题根源是优化器选了个“把中间结果集物化到临时段”的糟糕计划。计划修好之后排序数据量本身就小了很多自然不需要那么多内存。这也是我想强调的PGA和存储的调优是辅助执行计划才是命脉。5.2 常见误区与排查技巧速查写几个高频坑都是我在案例中或朋友踩过的误区一direct path write temp一出现就认定是PGA不足。实际上很多临时段写入由执行计划导致先检查执行计划里有没有巨量MERGE SORT JOIN、SORT ORDER BY、SORT GROUP BY再决定是否调PGA。误区二把问题直接甩给存储团队。如果换设备前没有确认SQL层和计划层是否有优化空间很可能会花了大价钱换NVMe最后发现SQL还是慢只是等待时间从数据文件读变成了等待其他事件。误区三视图查询优化只改SQL不更新统计信息。19c里视图涉及的基表统计信息如果过期优化器对行数估算会严重失真再好的SQL文本也白搭。排查时把DBMS_STATS.GATHER_TABLE_STATS和直方图策略一并检查。误区四升级到19.17之后看到性能下降就直接“回退参数”没有锁定计划。回退optimizer_features_enable只能算应急手段长期维护成本很高要趁热把好计划用SPM固定下来。下面是小速查表适合放在备忘录里症状现象优先排查项推荐SQL/动作视图查询慢temp暴涨执行计划中是否有SORTHASH JOIN超大中间集v$sql_plan / DBMS_XPLAN.DISPLAY_CURSORdirect path write temp占比高会话工作区是否onepass/multipassv$sql_workarea_activeASM IO高但数据库CPU低磁盘组是否rebalance/resyncv$asm_operation升级后同一SQL变慢新旧计划对比是否走视图物化DBMS_XPLAN.COMPARE_PLANS并发查询时全部变慢临时表空间文件和PGA是否充足v$temp_space_header6. 回归日常把这类问题拦在爆发前6.1 监控与巡检建议解决一个case不难难的是下次别让它毫无征兆地再出现。我把这套问题的监控归纳成三条线DBA可以按周期去检查。第一临时段层面。每周至少一次巡检关注临时表空间使用率峰值和TOP临时段占用SQL。SQL如下SELECT s.sid, s.serial#, s.sql_id, u.tablespace, ROUND(u.blocks*dt.block_size/1024/1024, 2) AS temp_mb, u.segtype, u.session_num FROM v$tempseg_usage u, v$session s, dba_tablespaces dt WHERE u.session_addr s.saddr AND u.tablespace dt.tablespace_name ORDER BY temp_mb DESC FETCH FIRST 20 ROWS ONLY;第二等待事件层面。关注direct path write temp在全库等待事件中的占比趋势一旦占比超过10%就标记关注超过20%就触发告警。可以用dba_hist_system_event做历史趋势分析观察是否随业务量增长呈线性上升。第三ASM层面。建立磁盘组IO延迟基线和利用率基线。用v$asm_disk_stat每小时采样一次avg_read_ms和avg_write_ms同一磁盘组的写入延迟连续三小时高于基线的2倍就要开始查SQL层。存储层的健康数据也要定期拉取和ASM侧数据做交叉验证。6.2 最后的经验心得处理direct path write temp与ASM IO问题最重要的是稳。先把等待事件、执行计划、临时段使用、ASM IO几个证据链拼完整再决定动手改哪里。我见过太多人一看到temp写盘就忙着加PGA一看到ASM IO高就急着换存储结果问题原封不动还在那里。按我个人的经验这类问题的出现频率在19c的复杂视图查询上并不低根本原因往往是视图内部的聚合和下推逻辑太“重”。数据库性能优化很多时候不是在跟Oracle较劲而是在跟自己的业务设计较劲把SQL结构理清了很多看似无解的等待事件自然就消失了。最后分享一个小技巧优化完一条复杂视图查询后别急着收工用DBMS_SQLTUNE.REPORT_SQL_MONITOR对比一下优化前后的执行耗时和执行计划IO分布。SQL Monitor报告里有专门的工作区使用情况和临时空间写入量统计这是验证优化效果最直观的工具比起单纯看AWR更聚焦单条SQL。把这些数据留存下来就是下一次性能问题排查时最好的参照。