Oracle direct path write temp、direct path read temp引起的效能问题

User报告系统很慢，top提示load average一度超40+，cpu利用率很高

vmstat看上去IO很重，但是cpu wa值很低

查看top事件，发现dblink位列第一，停掉dblink也没能解决问题

select EVENT,sum(time_waited) EVENT_COUNTS from dba_hist_active_sess_history
WHERE to_char(sample_time,'YYYY-MM-DD')='2022-04-04' AND session_state='WAITING' group by event ORDER BY EVENT_COUNTS DESC

TM-contention在事发当时没有看到有此事件，于是决定做一个10046 trace
使用top抓取cpu top 1的OS PID，定位出sid及serial#

select sid,serial# from v$session where paddr in (select addr from v$process where spid=13980)

这个procedure会在udump下产生一个文件名包含spid的trace文件

现利用tkprof产生trace文件的报告

输出的文件入下

查看输出的报告文件

很明显，上图中的sql在fetch阶段产生太多的disk read, 并且下方提示与direct path write temp、direct path read temp有关，发生这两个event是因为sql语句中有用到排序操作，但是oracle sort buffer不足以容纳排序内容，所以就会write到硬盘排序，之后再把排序结果read回来
set autotrace traceonly 验证一下
结果也显示physical reads的发生,将肇事sql中的min拿掉

SELECT  /*+INDEX(A,B,C,D,I,O)*/ A.PODR_NO,
       B.AUFNR,
       NVL(O.OUT_QTY,0) LL_QTY
 FROM PCS_SAP_ARTICMAT_PODR A,PCS_ZPPT_AUFNR B,PCS_ZPPT_AUFNR_ARBPL C,
(SELECT   /*+INDEX(A,B,C,D)*/A.AUFNR,A.AUART,B.IDNRK,A.VBELN,NVL(A.PROD_BATCH,A.VBELN)PROD_BATCH,A.PLNBEZ,B.MAKTX 
FROM PCS_ZPPT_AUFNR A,PCS_ZPPT_AUFNR_IDNRK B,PCS_SAP_ARTICMAT_PODR C,PCS_ORDER_M D
WHERE A.AUFNR=B.AUFNR  AND A.VBELN='10175151-0010'
 AND SUBSTR(B.IDNRK,1,15)=C.GA_MAT_NO
AND A.VBELN=D.PODR_NO AND D.PODR_NO=C.PODR_NO
 )D,( SELECT PODR_NO,AUART,PROD_BATCH,PLNBEZ,
 SUM(IN_QTY)OUT_QTY FROM PCS_ZPPT_AUFNR_IO WHERE (MK='O' OR MK='Y')
         AND PODR_NO='10175151-0010'
        GROUP BY PODR_NO,PROD_BATCH,PLNBEZ,AUART )O
WHERE A.PODR_NO=B.VBELN
AND A.GA_MAT_NO=SUBSTR(B.PLNBEZ,1,15)
AND B.AUFNR=C.AUFNR
AND B.AUART IN ('ZP01','ZP05','ZP03','ZP02','ZP13')
AND  C.STEUS IN ('PP03','PP04')
AND B.AUFNR=D.AUFNR
AND D.VBELN=O.PODR_NO
AND REPLACE(REPLACE(NVL(D.PROD_BATCH,'N'),'_N'),'_Y')=REPLACE(REPLACE(O.PROD_BATCH,'_N'),'_Y')
AND D.IDNRK=O.PLNBEZ
AND D.AUART=O.AUART
AND A.PODR_NO='10175151-0010'
AND A.PCS_NO='2';

再看看traceonly结果

Physical reads没有了，果然是min引起的sort操作肇事