一次direct path read 故障處理
產生direct path read事件的原因有三種情況：
Causes
This situation occurs in the following situations:
-
The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
-
Parallel slaves are used for scanning data.
-
The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
10.3.4.2 Actions
The file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves. This wait is the largest wait for large data warehouse sites. However, if the workload is not a Decision Support Systems (DSS) workload, then examine why this situation is happening.
Examine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See .
If tables are defined with a high degree of parallelism, then this setting could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is adequate for the degree of parallelism. Consider using disk striping if you are not already using it or Oracle Automatic Storage Management (Oracle ASM).
For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increasePGA_AGGREGATE_TARGET.
案例：
2014年11月17日16:30-17:30時營銷資料庫(toyx1a)出現在大量的direct path read、direct path read temp、direct path write temp、db file scattered read、read by other session事件。透過分析出現所有的等待事件都集中在表UTL.T_C_L_CUST_INFO_M上。
ASH 報告中direct path read在整個等待事件裡佔比為26.24%
| Event | Event Class | % Event | Avg Active Sessions |
|---|---|---|---|
| CPU + Wait for CPU | CPU | 36.40 | 2.66 |
| direct path read | User I/O | 26.24 | 1.92 |
| direct path read temp | User I/O | 8.00 | 0.58 |
| direct path write temp | User I/O | 6.73 | 0.49 |
| db file scattered read | User I/O | 5.94 | 0.43 |
Top Event P1/P2/P3 Values
| Event | % Event | P1 Value, P2 Value, P3 Value | % Activity | Parameter 1 | Parameter 2 | Parameter 3 |
|---|---|---|---|---|---|---|
| direct path read | 26.24 | "10","209408","128" | 0.03 | file number | first dba | block cnt |
| direct path read temp | 8.00 | "2001","469469","31" | 0.01 | file number | first dba | block cnt |
| direct path write temp | 6.73 | "2001","492862","31" | 0.01 | file number | first dba | block cnt |
| db file scattered read | 5.94 | "9","461440","49" | 0.01 | file# | block# | blocks |
| read by other session | 4.76 | "15","470969","1" | 0.01 | file# | block# | class# |
透過direct path read事件的p1,p2,p3值我們可以定位到該事件在哪個物件上發生等待
-
P1: File_id for the read call
-
P2: Start block_id for the read call
-
P3: Number of blocks in the read call
owner segment_name segment_type partition_name
--------- --------------------- ------------------- ------------------------------
UTL T_C_L_CUST_INFO_M TABLE PARTITION CUSTOM_PROFILE_PART_10 =====>direct path read 發生在表T_C_L_CUST_INFO_M上。
透過read by other session事件的p1,p2.p3值同樣也可以定位到該事件在哪個物件上發生等待
| Parameter | Description |
|---|---|
| file# | See |
| block# | See |
| class# | See |
SQL> select owner,segment_name,segment_type,partition_name from dba_extents where file_id=15 and 470969 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
------------------------------ --------------------------------------------------------------------------------- ------------------ ------------------------------
UTL T_C_L_CUST_INFO_M TABLE PARTITION CUSTOM_PROFILE_PART_2
透過db file scattered read 的p1,p2,p3同樣也可以診斷該事件在哪個物件上發生等待
Similar to db file sequential read, except that the session is reading multiple data blocks.
Wait Time: The wait time is the actual time it takes to do all of the I/Os
| Parameter | Description |
|---|---|
| file# | See |
| block# | See |
| blocks | The number of blocks that the session is trying to read from the file# starting at block# |
SQL> set lines 200
SQL> set pages 500
SQL> select owner,segment_name,segment_type,partition_name from dba_extents where file_id=9 and 461440 between block_id and block_id+blocks-1;
OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
------------------------------ --------------------------------------------------------------------------------- ------------------ ------------------------------
UTL T_C_L_CUST_INFO_M TABLE PARTITION CUSTOM_PROFILE_PART_2
因此我們在ASH報告裡搜尋表T_C_L_CUST_INFO_M發現如下語句，且該語句都存在order by 排序，因此也可以斷定為什麼該語句產生大量的direct path read 、direct path read temp事件了。
| 005wu57dp2nvz | select * from (select * from (select SERV_NO, GPRS_FEE_2, TOLL_CALL_FEE, GPRS_FEE_1, ARPU, GPRS_PRIV_FEE3, OVERPKG_MARK, JTCT_CALL_DUR, PKG_USED_FLOW, MYZJ_CALL_DUR, PKG_USED_PROP, OVERPKG_FLOW, CALL_PRICE2, VPMN_CALL_DUR, BDCT_CALL_DUR, STABILITY_SCORE, STAR_LEVEL_MARK, VPMNSP_CALL_DUR, ENTERPRISE_MARK, VPMN_FLAG, GPRS_FLOW_M, PKG_ALL_FLOW, ARPU_PRICE, PHOTOMEM_MARK, IS_MIGU_ORDER, IS_LINGXI_ORDER, IS_MOBMAP_ORDER, IS_PIM_ACTIVE, IS_MIGU_ACTIVE, CRING_FLAG, IS_CHEZHU_ACTIVE, IS_PIM_ORDER, IS_FETION_ACTIVE, IS_MOBMAP_ACTIVE, IS_139_5_ACTIVE, IS_MOBREAD_ORDER, IS_MOBGAME_ORDER, IS_FETION_ORDER, IS_139_20_ACTIVE, IS_LINGXI_ACTIVE, IS_139_5_ORDER, IS_139_20_ORDER, TERM_BRAND, TERM_USE_TIME, TERM_CODE, TERM_GET_DATE, TERM_TYPE, COOPERATE_FLEG, OPEN_DATE, YXPH_FREE, TOTAL_SCORE, YX_ZFPH, YX_BAODI, YXPH_HYYJ, YXPH_FEE, YXPH_OBJ, YXPH_TERM, PLAN_NAME, ACTIVE_DEPT_ID, ACTIVE_AREA_ID, GPRS_PRIV_FEE1, ROAM_CALL_FEE, GPRS_PRIV_FEE2, PLAN_ID from T_C_L_CUST_INFO_M where SERV_NO ='13583152372' ord er by DATA_MONTH desc ) where rownum=1 ) t0 |
| 0vyp4gqddp7mk | select * from (select * from (select SERV_NO, PAY_TYPE, GPRS_FEE_2, GPRS_FEE_1, ARPU, BRAND_ID, FEE_INCREASE, FEE_DECREASE, GPRS_FLOW_2, BECOME_DUE, UNBALANCE_FLAG, CALL_DURA_M, PRIV_4G_MARK, BECOME_TIME, SPRODUCT_MARK, SC_USER_MARK, TIME_DECREASE, UNIONACCT_MARK, PHOTOMEM_MARK, TERM_BRAND, TERM_USE_TIME, TERM_CODE, TERM_GET_DATE, TERM_TYPE, SEX_ID, USER_STATUS, OPEN_DATE, CITY_ID, PREPAY_FEE, GIVE_FEE, TOTAL_SCORE, YX_BAODI, CREDIT_LEV from T_C_L_CUST_INFO_M where SERV_NO ='13688689651' order by DATA_MONTH desc ) where rownum=1 ) t0 |
| gyzcb1gqtscms | select * from (select * from (select SERV_NO, GPRS_FEE_2, TOLL_CALL_FEE, GPRS_FEE_1, ARPU, GPRS_PRIV_FEE3, OVERPKG_MARK, JTCT_CALL_DUR, PKG_USED_FLOW, MYZJ_CALL_DUR, PKG_USED_PROP, OVERPKG_FLOW, CALL_PRICE2, VPMN_CALL_DUR, BDCT_CALL_DUR, STABILITY_SCORE, STAR_LEVEL_MARK, VPMNSP_CALL_DUR, ENTERPRISE_MARK, VPMN_FLAG, GPRS_FLOW_M, PKG_ALL_FLOW, ARPU_PRICE, PHOTOMEM_MARK, IS_MIGU_ORDER, IS_LINGXI_ORDER, IS_MOBMAP_ORDER, IS_PIM_ACTIVE, IS_MIGU_ACTIVE, CRING_FLAG, IS_CHEZHU_ACTIVE, IS_PIM_ORDER, IS_FETION_ACTIVE, IS_MOBMAP_ACTIVE, IS_139_5_ACTIVE, IS_MOBREAD_ORDER, IS_MOBGAME_ORDER, IS_FETION_ORDER, IS_139_20_ACTIVE, IS_LINGXI_ACTIVE, IS_139_5_ORDER, IS_139_20_ORDER, TERM_BRAND, TERM_USE_TIME, TERM_CODE, TERM_GET_DATE, TERM_TYPE, COOPERATE_FLEG, OPEN_DATE, YXPH_FREE, TOTAL_SCORE, YX_ZFPH, YX_BAODI, YXPH_HYYJ, YXPH_FEE, YXPH_OBJ, YXPH_TERM, PLAN_NAME, ACTIVE_DEPT_ID, ACTIVE_AREA_ID, GPRS_PRIV_FEE1, ROAM_CALL_FEE, GPRS_PRIV_FEE2, PLAN_ID from T_C_L_CUST_INFO_M where SERV_NO ='13969809161' ord er by DATA_MONTH desc ) where rownum=1 ) t0 |
語句查詢出來了，哪為啥突然之間會出現效能問題呢？
首先我們檢視該語句的執行計劃：
SQL> select * from table(dbms_xplan.display_awr('005wu57dp2nvz',null,null,'all'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 005wu57dp2nvz
--------------------
select * from (select * from (select
SERV_NO,GPRS_FEE_2,TOLL_CALL_FEE,GPRS_FEE_1,ARPU,GPRS_PRIV_FEE3,OVERPKG_
MARK,JTCT_CALL_DUR,PKG_USED_FLOW,MYZJ_CALL_DUR,PKG_USED_PROP,OVERPKG_FLO
W,CALL_PRICE2,VPMN_CALL_DUR,BDCT_CALL_DUR,STABILITY_SCORE,STAR_LEVEL_MAR
K,VPMNSP_CALL_DUR,ENTERPRISE_MARK,VPMN_FLAG,GPRS_FLOW_M,PKG_ALL_FLOW,ARP
U_PRICE,PHOTOMEM_MARK,IS_MIGU_ORDER,IS_LINGXI_ORDER,IS_MOBMAP_ORDER,IS_P
IM_ACTIVE,IS_MIGU_ACTIVE,CRING_FLAG,IS_CHEZHU_ACTIVE,IS_PIM_ORDER,IS_FET
ION_ACTIVE,IS_MOBMAP_ACTIVE,IS_139_5_ACTIVE,IS_MOBREAD_ORDER,IS_MOBGAME_
ORDER,IS_FETION_ORDER,IS_139_20_ACTIVE,IS_LINGXI_ACTIVE,IS_139_5_ORDER,I
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
S_139_20_ORDER,TERM_BRAND,TERM_USE_TIME,TERM_CODE,TERM_GET_DATE,TERM_TYP
E,COOPERATE_FLEG,OPEN_DATE,YXPH_FREE,TOTAL_SCORE,YX_ZFPH,YX_BAODI,YXPH_H
YYJ,YXPH_FEE,YXPH_OBJ,YXPH_TERM,PLAN_NAME,ACTIVE_DEPT_ID,ACTIVE_AREA_ID,
GPRS_PRIV_FEE1,ROAM_CALL_FEE,GPRS_PRIV_FEE2,PLAN_ID from
T_C_L_CUST_INFO_M where SERV_NO ='13583152372' order by DATA_MONTH desc
) where rownum=1 ) t0
Plan hash value: 3173533799
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2682K(100)| | | |
| 1 | VIEW | | 1 | 1135 | 2682K (2)| 08:56:34 | | |
| 2 | COUNT STOPKEY | | | | | | | |
| 3 | VIEW | | 1 | 1135 | 2682K (2)| 08:56:34 | | |
| 4 | SORT ORDER BY STOPKEY| | 1 | 289 | 2682K (2)| 08:56:34 | | | ＝＝＝＝〉存在排序
| 5 | PARTITION RANGE ALL | | 1 | 289 | 2682K (2)| 08:56:34 | 1 | 18 |
| 6 | TABLE ACCESS FULL | T_C_L_CUST_INFO_M | 1 | 289 | 2682K (2)| 08:56:34 | 1 | 18 | ＝＝＝＝〉該表發生全表掃。
--------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
1 - SEL$2 / T0@SEL$1
2 - SEL$2
3 - SEL$3 / from$_subquery$_002@SEL$2
4 - SEL$3
6 - SEL$3 / T_C_L_CUST_INFO_M@SEL$3
發現該語句的執行計劃發生了改變，檢視物件索引的建立時間發現建立時間為2014-11-17 17:19:35 因此可以斷定此索引剛剛建上。
SQL> SELECT OWNER,CREATED FROM DBA_OBJECTS WHERE OBJECT_NAME='INX_T_C_L_CUST_INFO_M_SERV_NO';
OWNER CREATED
------------------------------ -------------------
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
UTL 2014-11-17 17:19:35
最後透過和研發溝通，該索引刪除的目的是為了加快sqlldr導資料的速度。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29446986/viewspace-1336846/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- direct path read/read temp等待事件事件
- [20180905]lob與direct path read.txt
- Oracle direct path read相關隱含引數Oracle
- 【TUNE_ORACLE】等待事件之IO等待“direct path read”Oracle事件
- 【故障處理】ORA-600:[13013],[5001]故障處理
- linux故障處理Linux
- 故障分析 | Greenplum Segment 故障處理
- 等待事件db file sequential read、db file scattered read和direct read的區別事件
- GPON網路故障如何處理？GPON網路故障處理流程
- 記一次一波三折的Mysql故障處理MySql
- MySQL show processlist故障處理MySql
- 微服務的故障處理微服務
- Oracle更新Opatch故障處理Oracle
- teams登入故障處理
- 記一次一波三折的Oracle RAC故障處理Oracle
- 線上故障處理手冊
- 【TUNE_ORACLE】等待事件之IO等待“direct path write”Oracle事件
- 【故障處理】TNS-04610問題
- GaussDB(分散式)例項故障處理分散式
- Oracle 10g RAC故障處理Oracle 10g
- ORA-01591錯誤故障處理
- 如何處理HTTP 503故障問題？HTTP
- Oracle 11.2.0.4 Dataguard兩則故障處理Oracle
- 【TUNE_ORACLE】等待事件之IO等待“direct path write temp”Oracle事件
- hbase 故障的處理方案。 （轉載文章）
- Oracle DG同步失敗故障處理(二)Oracle
- NO.A.0001——zabbix常見故障的處理
- 體檢伺服器nginx故障處理伺服器Nginx
- Oracle client安裝the jre is 0故障處理Oracleclient
- 【問題處理】ORA-00376 file xx cannot be read at this time
- 【故障處理】ORA-3113 "end of file on communication channel"
- hillstone現場故障處理指導手冊
- 金融行業現場故障處理實錄行業
- TS - 處理故障的一些通用方法
- OracleORA-03113 ORA-600 [4193]故障處理Oracle
- 【故障處理】ORA-28547: connection to server failed, probableServerAI
- 叢集故障處理之處理思路以及健康狀態檢查（三十二）
- Bumblebee之負載、限流和故障處理實踐負載
- TiDB故障處理之讓人迷惑的Region is UnavailableTiDBAI