oracle direct path read处理过程
文章目录
- 缘起
- 处理过程
- 1.AWR Report 分析
- 2.调查direct path read发生的table
- 3.获取sql text
- 4.解释sql并输出执行计划：
- 结论：
- 补充direct path read等待事件说明
缘起
记录direct path read处理过程
处理过程
1.AWR Report 分析
问题发生时间段awr如下：
Load profile显示有大的read IO(MB):
Top 10等待事件by wait time
Top 等待时间by wait times
Top reads
显示top 1 sql_id是g2t273f41924k
2.调查direct path read发生的table
select e.* from dba_extents e,
(select event_id,p1,p2 from dba_hist_active_sess_history where
to_char(sample_time,'YYYY-MM-DD hh24') between '2024-09-24 06:00:00'
and '2024-09-24 08:00:00'
and event like '%direct path read%' and sql_id='g2t273f41924k') ev
where e.file_id=ev.p1 and ev.p2 between e.block_id and (e.block_id+blocks)
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ --------------------------------------------------------------------------------- ------------------
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
MES5040 MES_CFM_CARTON_IN TABLE
3.获取sql text
select sql_text from v$sqlarea where sql_id='g2t273f41924k'
输出：
SELECT ODR.ODR_NO, ODR.FIN_CUST_PO, ODR.CUST_ODR, ODR.ARTIC_NO, ODR.ETD_DATE, ODR.CUST_DATE, DECODE(ODR.REQ_DATE3,'00000000',(DECODE(ODR.REQ_DATE2,'00000000',ODR.RTD,ODR.REQ_DATE2)),ODR.REQ_DATE3) AS REQ_DATE2,
ODR.TOTAL_QTY,(SELECT COUNT(DISTINCT CARTON_NO) FROM MES5040.MES_PRO_CXBARCODE WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO AND SCAN_MK = 'Y') AS SCAN_CTN_QTY,
(SELECT SUM(YSCAN_QTY) FROM MES5040.MES_PRO_CXBARCODE WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO AND SCAN_MK = 'Y') AS SUM_QTY,
(SELECT NVL(MIN(DATUM),' ') FROM MES5040.MES_CFM_CARTON_IN WHERE WERKS = ODR.FACT_NO AND VBELN = ODR.ODR_NO AND MOVE_TYPE = '1') AS FRIST_SCAN_DATE,
(SELECT WM_CONCAT(DISTINCT SEC_NO) FROM MES5040.MES_PROD_PRODUCTION AA , MES5040.YY_FPODRM BB WHERE AA.ODR_NO = BB.FPODR_NO AND AA.PROD_TYPE = '005' AND BB.ODR_NO = ODR.ODR_NO) AS SEC_NM,
(SELECT WM_CONCAT(DISTINCT STORAGE_NO) FROM MES5040.MES_PROD_STORAGEPLANS WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS STORAGE_NO, (SELECT MAX(CHECK_MK_YJ)
FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007'
AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK_YJ,
(SELECT MAX(CHECK_MK_SJ) FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007' AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK_SJ,
(SELECT MAX(CHECK_MK) FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007' AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK,
(SELECT MES_ODR_NO FROM MES5040.MES_TRANS_SAP_ODR WHERE FACT_NO = ODR.FACT_NO AND SAP_ODR_NO = ODR.ODR_NO) AS MES_ODR_NO
FROM MES5040.YY_ODRM ODR, (SELECT DISTINCT FACT_NO, ODR_NO FROM MES5040.MES_PRO_CXBARCODE WHERE SCAN_MK = 'Y' AND OUT_MK = 'N') MCCI
WHERE MCCI.FACT_NO = ODR.FACT_NO
AND MCCI.ODR_NO = ODR.ODR_NO
AND ODR.FACT_NO = :as_fact_no
AND ODR.BRAND_NO = :as_brand_no
AND ODR.YYMM BETWEEN :as_yymm_s AND :as_yymm_e
4.解释sql并输出执行计划：
explain plan for
SELECT ODR.ODR_NO, ODR.FIN_CUST_PO, ODR.CUST_ODR, ODR.ARTIC_NO, ODR.ETD_DATE, ODR.CUST_DATE, DECODE(ODR.REQ_DATE3,'00000000',(DECODE(ODR.REQ_DATE2,'00000000',ODR.RTD,ODR.REQ_DATE2)),ODR.REQ_DATE3) AS REQ_DATE2,
ODR.TOTAL_QTY,(SELECT COUNT(DISTINCT CARTON_NO) FROM MES5040.MES_PRO_CXBARCODE WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO AND SCAN_MK = 'Y') AS SCAN_CTN_QTY,
(SELECT SUM(YSCAN_QTY) FROM MES5040.MES_PRO_CXBARCODE WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO AND SCAN_MK = 'Y') AS SUM_QTY,
(SELECT NVL(MIN(DATUM),' ') FROM MES5040.MES_CFM_CARTON_IN WHERE WERKS = ODR.FACT_NO AND VBELN = ODR.ODR_NO AND MOVE_TYPE = '1') AS FRIST_SCAN_DATE,
(SELECT WM_CONCAT(DISTINCT SEC_NO) FROM MES5040.MES_PROD_PRODUCTION AA , MES5040.YY_FPODRM BB WHERE AA.ODR_NO = BB.FPODR_NO AND AA.PROD_TYPE = '005' AND BB.ODR_NO = ODR.ODR_NO) AS SEC_NM,
(SELECT WM_CONCAT(DISTINCT STORAGE_NO) FROM MES5040.MES_PROD_STORAGEPLANS WHERE FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS STORAGE_NO, (SELECT MAX(CHECK_MK_YJ)
FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007'
AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK_YJ,
(SELECT MAX(CHECK_MK_SJ) FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007' AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK_SJ,
(SELECT MAX(CHECK_MK) FROM MES5040.MES_QA_CHECK WHERE PROD_TYPE = '007' AND FACT_NO = ODR.FACT_NO AND ODR_NO = ODR.ODR_NO) AS CHECK_MK,
(SELECT MES_ODR_NO FROM MES5040.MES_TRANS_SAP_ODR WHERE FACT_NO = ODR.FACT_NO AND SAP_ODR_NO = ODR.ODR_NO) AS MES_ODR_NO
FROM MES5040.YY_ODRM ODR, (SELECT DISTINCT FACT_NO, ODR_NO FROM MES5040.MES_PRO_CXBARCODE WHERE SCAN_MK = 'Y' AND OUT_MK = 'N') MCCI
WHERE MCCI.FACT_NO = ODR.FACT_NO
AND MCCI.ODR_NO = ODR.ODR_NO
AND ODR.FACT_NO = :as_fact_no
AND ODR.BRAND_NO = :as_brand_no
AND ODR.YYMM BETWEEN :as_yymm_s AND :as_yymm_e
执行计划输出如下图：
select * from table(dbms_xplan.display())
谓词部分的输出：
结论：
很明显，没有合适的索引，造成优化器选择的full table scan
后续处理报给开发人员创建合适的索引后，问题得到解决
补充direct path read等待事件说明
这篇博客已经做出很好的解释了，引用如下：
Oracle 的11g版本正式发布到今天已经10年有余，最新版本也已经到了20c，但是Direct Path Read（直接路径读）导致性能问题的案例仍时有发生，很多12c的用户还是经常遇到这个问题，所以有必要把这个事情再跟大家讲一遍，通过2个典型案例加深理解。
早在2012年，盖国强大师就撰写文章，介绍了direct path read这个11g版本推出的新特性：
https://www.eygle.com/archives/2012/05/oracle_11g_direct_path_read.html ；也有人把关闭这个功能作为“最佳实践”，我建议先多了解一些具体情况再决定。
Direct path read的目的是让一些不常使用的大表数据（冷数据），在全表扫描时，每次都从磁盘读到用户的私有内存（PGA），而不要去挤占有限的、宝贵的、频繁使用的数据（热数据）所在的共享内存（SGA-bufer cache）。
热数据只在第一次访问时从磁盘读，读到SGA的buffer cache后，再次访问会直接从内存读，效率高、对存储压力小。
试想一个表被频繁全表扫描访问（缺少索引或业务设计不合理），一开始表还不算太大，会放到共享内存，只需要少量的磁盘读，这时对存储压力不大；随着记录数的不断增加，达到了一个参数设置的阀值和条件后，就会使用direct path read，频繁的磁盘读就会造成存储的巨大压力，出现严重的性能问题。
从共享内存读到直接路径读，这个变化在不频繁的全表扫描时是起到积极作用的；如果业务不合理（一个大表正常情况不会有频繁的全表扫描）、或者缺少索引（这个是比较多的情况），频繁的大表全表扫描就会在某个触发点上对数据库性能做出致命一击，导致业务瘫痪。