direct path read/read temp等待事件

    當會話從磁盤直接讀取數(shù)據(jù)塊到PGA(繞過SGA)時，發(fā)生direct path read/read temp等待事件 ，下圖簡要描述了這種方式的讀取方式：

如果I/O子系統(tǒng)不支持異步I/Os，那么每個等待對應于一個物理讀請求。

direct path read/direct path read temp等待事件有三個參數(shù)，位于User I/O等待類下面

SQL> select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name like 'direct path %';

NAME                           PARAMETER1           PARAMETER2           PARAMETER3           WAIT_CLASS

------------------------------ -------------------- -------------------- -------------------- --------------------

direct path sync               File number          Flags                                     User I/O

direct path read               file number          first dba            block cnt            User I/O

direct path read temp          file number          first dba            block cnt            User I/O

direct path write              file number          first dba            block cnt            User I/O

direct path write temp         file number          first dba            block cnt            User I/O

file#：要讀取的數(shù)據(jù)塊所在數(shù)據(jù)文件的文件號

first dba：要讀取的起始數(shù)據(jù)塊號

block cnt：要讀取的數(shù)據(jù)塊數(shù)目

導致此等待事件的原因有四個：

  1.執(zhí)行的sql語句含有大量排序數(shù)據(jù) order by/group by/union/distinct/rollup等

 當PGA中排序區(qū)不足時，Oracle會將排序結果寫入到臨時表空間中（direct path write temp）。當從臨時表空間中讀取排序數(shù)據(jù)時會在direct path read temp事件上等待。

  2.hash join

 hash在構建hash table時，將不適合位于內(nèi)存中的hash partition刷出到臨時表空間，隨后hash partition被讀回到內(nèi)存時會發(fā)生direct path read temp等待事件。

  3.并行查詢

 并行執(zhí)行過程中，direct path read等待事件只與Master會話派生出的Slave會話有關，Slave會話會在direct path read事件上等待，而Master會話等待的是PX Deq。

  4.全表掃描

  11g后如果Oracle感覺表足夠大，則會進行直接路徑讀取。

下面依次進行測試

一、排序引起的direct path read/write temp

  session1:

  SQL> alter session set workarea_size_policy=manual; --設為手動pga管理

 Session altered.

 SQL> alter session set sort_area_size=65536;--設置排序允許的內(nèi)存大小

 Session altered.

 SQL> create table scott.tb_test as select * from dba_objects where rownum < 1000; --創(chuàng)建測試表

 Table created.

 session2:

 SQL> oradebug setospid 7038 --設置10046事件追蹤session1

 Oracle pid: 20, Unix process pid: 7038, image: oracle@node3 (TNS V1-V3)

 SQL> oradebug event 10046 trace name context forever,level 12;

 Statement processed.

 SQL> oradebug tracefile_name

 /opt/app/oracle/diag/rdbms/bddev1/BDDEV1/trace/BDDEV1_ora_7038.trc

  排序前session1對臨時表空間直接讀寫的統(tǒng)計如下：

  SQL>  select a.name,b."VALUE" 

   2   from v$statname a,v$sesstat b 

   3   where b.sid = 18

   4   and a."STATISTIC#" = b."STATISTIC#" 

   5   and  a."NAME" like '%direct temp%';

 NAME                                                                  VALUE

 ---------------------------------------------------------------- ----------

 physical reads direct temporary tablespace                             0

 physical writes direct temporary tablespace                            0

 session1：

 SQL> select object_id from (select rownum rn,a.* from scott.tb_test a order by 1,2,3 desc) a where rn = 2;

  OBJECT_ID

 ----------

         46

 此時查看追蹤文件：

  select object_id from (select rownum rn,a.* from scott.tb_test a order by 1,2,3 desc) a where rn = 2

 END OF STMT

 PARSE #139877337074560:c=10940,e=16752,p=13,cr=64,cu=0,mis=1,r=0,dep=0,og=1,plh=240700742,tim=1542267932869427

 EXEC #139877337074560:c=32,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=240700742,tim=1542267932869711

 WAIT #139877337074560: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=88859 tim=1542267932869799

 WAIT #139877337074560: nam='Disk file operations I/O' ela= 39 FileOperation=2 fileno=201 filetype=2 obj#=88859 tim=1542267932870591

 WAIT #139877337074560: nam=' direct path write temp ' ela= 5112 file number=201 first dba=6528 block cnt=1 obj#=88859 tim=1542267932875922

 WAIT #139877337074560: nam=' direct path write temp ' ela= 7399 file number=201 first dba=6529 block cnt=1 obj#=88859 tim=1542267932883416

 WAIT #139877337074560: nam= 'direct path write temp' ela= 237 file number=201 first dba=6530 block cnt=1 obj#=88859 tim=1542267932883751

 WAIT #139877337074560: nam=' direct path write temp' ela= 242 file number=201 first dba=6531 block cnt=1 obj#=88859 tim=1542267932884205

 WAIT #139877337074560: nam=' direct path write temp' ela= 246 file number=201 first dba=6532 block cnt=1 obj#=88859 tim=1542267932884637

 WAIT #139877337074560: nam= 'direct path read temp' ela= 21 file number=201 first dba=6528 block cnt=4 obj#=88859 tim=1542267932884757

 FETCH #139877337074560:c=1865,e=14945,p=4,cr=16,cu=2,mis=0,r=1,dep=0,og=1,plh=240700742,tim=1542267932884816

 WAIT #139877337074560: nam='SQL*Net message from client' ela= 197 driver id=1650815232 #bytes=1 p3=0 obj#=88859 tim=1542267932885067

 WAIT #139877337074560: nam=' direct path read temp' ela= 16 file number=201 first dba=6532 block cnt=1 obj#=88859 tim=1542267932885400

 FETCH #139877337074560:c=516,e=515,p=1,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=240700742,tim=1542267932885620

 STAT #139877337074560 id=1 cnt=1 pid=0 pos=1 obj=0 op='VIEW  (cr=16 pr=5 pw=5 time=14948 us cost=73 size=25974 card=999)'

 STAT #139877337074560 id=2 cnt=999 pid=1 pos=1 obj=0 op='SORT ORDER BY (cr=16 pr=5 pw=5 time=16680 us cost=73 size=206793 card=999)'

 STAT #139877337074560 id=3 cnt=999 pid=2 pos=1 obj=0 op='COUNT  (cr=16 pr=0 pw=0 time=4019 us)'

 STAT #139877337074560 id=4 cnt=999 pid=3 pos=1 obj=88861 op='TABLE ACCESS FULL TB_TEST (cr=16 pr=0 pw=0 time=1268 us cost=6 size=206793 card=999)'

 WAIT #139877337074560: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88859 tim=1542267932885761

 此時再查看session1對臨時表空間直接讀寫的統(tǒng)計：

  NAME                                                                  VALUE

 ---------------------------------------------------------------- ----------

 physical reads direct temporary tablespace                             5

 physical writes direct temporary tablespace                            5

 可看到，由于排序操作， Oracle對臨時表空間執(zhí)行了5次直接寫，每次寫1個block；執(zhí)行了2次直接讀，分別讀取了4個block和1個block。

 也可以在sql執(zhí)行過程中，查看直接讀寫發(fā)生的表空間和段類型：

  SQL> select a.event,a.sid, c.sql_hash_value hash_value,decode(d.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,b.tablespace_name,b.file_name

  2  from   v$session_wait a, dba_data_files b, v$session c, x$ktsso d

  3  where  c.saddr  = d.ktssoses(+)

  4  and c.serial#= d.ktssosno(+)

  5  and d.inst_id(+) = userenv('instance')

  6  and a.sid = c.sid

  7  and a.p1  = b.file_id

  8  and a.event like 'direct path %'

  9  union all

 10  select a.event,a.sid,d.sql_hash_value hash_value,decode(e.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,b.tablespace_name,b.file_name

 11  from   v$session_wait a, dba_temp_files b, v$parameter c, v$session d, x$ktsso e

 12  where  d.saddr  = e.ktssoses(+)

 13  and d.serial#= e.ktssosno(+)

 14  and e.inst_id(+) = userenv('instance')

 15  and a.sid = d.sid

 16  and b.file_id= a.p1 - c.value

 17  and c.name= 'db_files'

 18  and a.event like 'direct path %'

 19  order by 1,2;

 EVENT                                 SID HASH_VALUE SEGMENT_T TABLESPACE_NAME      FILE_NAME

 ------------------------------ ---------- ---------- --------- -------------------- --------------------------------------------------

 direct path write temp                133  474626757 HASH      TEMP                 /opt/app/oracle/oradata/BDDEV1/temp01.dbf

 SQL> /

 EVENT                                 SID HASH_VALUE SEGMENT_T TABLESPACE_NAME      FILE_NAME

 ------------------------------ ---------- ---------- --------- -------------------- --------------------------------------------------

 direct path read temp                 133  474626757 HASH      TEMP                 /opt/app/oracle/oradata/BDDEV1/temp01.dbf

 可看到占用臨時表空間的確實是hash對象                       

二、hash join引起的direct path read/write temp

 創(chuàng)建測試表2：

  SQL> create table scott.tb_test2 as select * from dba_objects where rownum < 2;

 Table created.

 hash join前session1對臨時表空間直接讀寫的統(tǒng)計如下：

  NAME                                                                  VALUE

 ---------------------------------------------------------------- ----------

 physical reads direct temporary tablespace                              225

 physical writes direct temporary tablespace                             718

 session1執(zhí)行查詢：

 SQL> select /*+ use_hash(a,b) leading(a,b)*/ a.object_id

  2   from scott.tb_test a,scott.tb_test2 b

  3   where a.object_name = b.object_name;

   OBJECT_ID

 ----------

         20

 查看追蹤文件：

  PARSING IN CURSOR #139877337073608 len=125 dep=0 uid=0 oct=3 lid=0 tim=1542269239154410 hv=3044439960 ad='a54bb978' sqlid='djdqhmuurcxws'

 select /*+ use_hash(a,b) leading(a,b)*/ a.object_id

 from scott.tb_test a,scott.tb_test2 b

 where a.object_name = b.object_name

 END OF STMT

 PARSE #139877337073608:c=83,e=83,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3759002927,tim=1542269239154407

 EXEC #139877337073608:c=53,e=54,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3759002927,tim=1542269239154772

 WAIT #139877337073608: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=88864 tim=1542269239154834

 WAIT #139877337073608: nam=' direct path write temp' ela= 6823 file number=201 first dba=6528 block cnt=1 obj#=88864 tim=1542269239162866

 WAIT #139877337073608: nam= 'direct path write temp' ela= 7342 file number=201 first dba=6529 block cnt=1 obj#=88864 tim=1542269239170355

 WAIT #139877337073608: nam=' direct path write temp' ela= 249 file number=201 first dba=6530 block cnt=1 obj#=88864 tim=1542269239170970

 WAIT #139877337073608: nam= 'direct path write temp' ela= 271 file number=201 first dba=6531 block cnt=1 obj#=88864 tim=1542269239171450

 WAIT #139877337073608: nam=' direct path read temp' ela= 19 file number=201 first dba=6531 block cnt=1 obj#=88864 tim=1542269239171572

 WAIT #139877337073608: nam= 'direct path read temp' ela= 14 file number=201 first dba=6528 block cnt=1 obj#=88864 tim=1542269239171641

 WAIT #139877337073608: nam= 'direct path read temp' ela= 13 file number=201 first dba=6529 block cnt=1 obj#=88864 tim=1542269239171688

 FETCH #139877337073608:c=2374,e=16849,p=3,cr=45,cu=0,mis=0,r=1,dep=0,og=1,plh=3759002927,tim=1542269239171729

 WAIT #139877337073608: nam='SQL*Net message from client' ela= 170 driver id=1650815232 #bytes=1 p3=0 obj#=88864 tim=1542269239172124

 WAIT #139877337073608: nam=' direct path read temp' ela= 15 file number=201 first dba=6530 block cnt=1 obj#=88864 tim=1542269239172206

 FETCH #139877337073608:c=140,e=139,p=1,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3759002927,tim=1542269239172308

 STAT #139877337073608 id=1 cnt=1 pid=0 pos=1 obj=0 op='HASH JOIN  (cr=45 pr=4 pw=4 time=16850 us cost=55 size=145 card=1)'

 STAT #139877337073608 id=2 cnt=2999 pid=1 pos=1 obj=88866 op='TABLE ACCESS FULL TB_TEST (cr=42 pr=0 pw=0 time=4799 us cost=14 size=236921 card=2999)'

 STAT #139877337073608 id=3 cnt=1 pid=1 pos=2 obj=88862 op='TABLE ACCESS FULL TB_TEST2 (cr=3 pr=0 pw=0 time=29 us cost=3 size=66 card=1)'

 WAIT #139877337073608: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=88864 tim=1542269239172458

 此時session1對臨時表空間直接讀寫的統(tǒng)計如下：

  NAME                                                                  VALUE

 ---------------------------------------------------------------- ----------

 physical reads direct temporary tablespace                              229

 physical writes direct temporary tablespace                             722

 可看到， 由于排序操作，Oracle對臨時表空間執(zhí)行了4次直接讀和4次直接寫 。

三、并行查詢引起的direct path read

 為了效果明顯，增加scott.tb_test表的記錄數(shù)：

  SQL> insert into scott.tb_test select * from scott.tb_test;

 ...

 修改scott.tb_test表的并行度：

 SQL> alter table scott.tb_test parallel 3;

 session1執(zhí)行查詢：

 SQL> select count(1) from scott.tb_test;

 查詢Master/Slave會話：

  SQL> select decode(a.qcserial#, null, 'PARENT', 'CHILD') stmt_level,a.sid,a.serial#,b.username,b.osuser,b.sql_hash_value,b.sql_address,a.degree,a.req_degree

  2  from   v$px_session a, v$session b

  3  where  a.sid = b.sid

  4  order by a.qcsid, stmt_level desc;

 STMT_L        SID    SERIAL# USERNAME                       OSUSER                         SQL_HASH_VALUE SQL_ADDRESS          DEGREE REQ_DEGREE

 ------ ---------- ---------- ------------------------------ ------------------------------ -------------- ---------------- ---------- ----------

 PARENT         18      27857 SYS                            oracle                              305472416 0000000069D6DD30

 CHILD          20      12579 SYS                            oracle                              305472416 0000000069D6DD30          3          3

 CHILD         141      14701 SYS                            oracle                              305472416 0000000069D6DD30          3          3

 CHILD         149      21869 SYS                            oracle                              305472416 0000000069D6DD30          3          3

 可看到總共有四個session，一個parent派生出了三個child。

 此時查看追蹤文件，可看到parent session在進行PX Deq相關等待

  select count(1) from scott.tb_test

 END OF STMT

 PARSE #139877336651544:c=19135,e=59969,p=8,cr=128,cu=0,mis=1,r=0,dep=0,og=1,plh=291292600,tim=1542272658766051

 WAIT #139877336651544: nam='os thread startup' ela= 16334 p1=0 p2=0 p3=0 obj#=88866 tim=1542272658783336

 WAIT #139877336651544: nam='os thread startup' ela= 15250 p1=0 p2=0 p3=0 obj#=88866 tim=1542272658798803

 WAIT #139877336651544: nam='os thread startup' ela= 15279 p1=0 p2=0 p3=0 obj#=88866 tim=1542272658814265

 WAIT #139877336651544: nam='PX Deq: Join ACK' ela= 3495 sleeptime/senderid=268500992 passes=3 p3=2778333136 obj#=88866 tim=1542272658817981

 WAIT #139877336651544: nam='PX Deq: Join ACK' ela= 2 sleeptime/senderid=0 passes=0 p3=0 obj#=88866 tim=1542272658818053

 WAIT #139877336651544: nam='PX Deq: Join ACK' ela= 2 sleeptime/senderid=0 passes=0 p3=0 obj#=88866 tim=1542272658818074

 WAIT #139877336651544: nam='PX Deq: Parse Reply' ela= 4166 sleeptime/senderid=200 passes=1 p3=0 obj#=88866 tim=1542272658822383

 WAIT #139877336651544: nam='PX Deq: Parse Reply' ela= 155 sleeptime/senderid=200 passes=1 p3=0 obj#=88866 tim=1542272658822593

 WAIT #139877336651544: nam='PX Deq: Parse Reply' ela= 3778 sleeptime/senderid=200 passes=1 p3=0 obj#=88866 tim=1542272658826408

 EXEC #139877336651544:c=1759,e=60216,p=0,cr=43,cu=0,mis=0,r=0,dep=0,og=1,plh=291292600,tim=1542272658826648

 WAIT #139877336651544: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=88866 tim=1542272658826714

 WAIT #139877336651544: nam='PX Deq: Execute Reply' ela= 5524 sleeptime/senderid=200 passes=1 p3=0 obj#=88866 tim=1542272658832302

 WAIT #139877336651544: nam='PX Deq: Execute Reply' ela= 3 sleeptime/senderid=0 passes=0 p3=0 obj#=88866 tim=1542272658832414

 WAIT #139877336651544: nam='PX Deq: Execute Reply' ela= 4277 sleeptime/senderid=200 passes=2 p3=0 obj#=88866 tim=1542272658836731

 查看v$active_session_history，可看到派生出的child session在direct path read事件上等待：

  SQL> select to_char(sample_time,'yyyymmdd hh34:mi:ss') stime,session_id,session_serial#,sql_id,session_state,event,p1,p2,p3,blocking_session

  2  from v$active_session_history

  3  where sample_time >= sysdate-1/24/60

  4  order by 1 desc;

 STIME             SESSION_ID SESSION_SERIAL# SQL_ID        SESSION EVENT                                  P1         P2         P3 BLOCKING_SESSION

 ----------------- ---------- --------------- ------------- ------- ------------------------------ ---------- ---------- ---------- ----------------

 20181115 18:23:49         20           12581 3hhzygn93a8x0 WAITING direct path read                        4     116736        128

 20181115 18:23:49        141           14711 3hhzygn93a8x0 WAITING direct path read                        4     264192        128

 20181115 18:23:49        149           21927 3hhzygn93a8x0 WAITING direct path read                        4     275328        128

 20181115 18:23:48         20           12581 3hhzygn93a8x0 WAITING direct path read                        4     190848        128

 20181115 18:23:48        141           14711 3hhzygn93a8x0 WAITING direct path read                        4      37248        128

 20181115 18:23:48        149           21927 3hhzygn93a8x0 WAITING direct path read                        4     205440        128

 20181115 18:23:47         20           12581 3hhzygn93a8x0 WAITING direct path read                        4     139776        128

 20181115 18:23:47        141           14711 3hhzygn93a8x0 WAITING direct path read                        4     146816        128

 20181115 18:23:47        149           21927 3hhzygn93a8x0 WAITING direct path read                        4      56192        128

 ...

  由于parent session并不在direct path read上等待，因此，如果要統(tǒng)計parent session在direct path read上的等待，需要用如下語句：

  SQL> select a.name, b.sid, b.value, round((sysdate - c.logon_time) * 24) hours_connected

  2  from   v$statname a, v$sesstat b, v$session c

  3  where  b.sid = c.sid

  4  and a.statistic# = b.statistic#

  5  and b.value  > 0

  6  and a.name= 'physical reads direct'

  7  order by b.value;

 NAME                                  SID      VALUE HOURS_CONNECTED

 ------------------------------ ---------- ---------- ---------------

 physical reads direct                  18     977016               0

  其中panret session的值為所有child session的累積。

四、全表掃描引起的direct path read等待事件

  從11G之后，direct path read不僅可用于并行查詢，在符合某些條件后，串行的全表掃描也可以利用direct path read方式來完成。

 以下總結引用“ 深入分析direct path read（11G）http://www.itpub.net/thread-1815281-1-1.html (出處: ITPUB論壇－中國專業(yè)的IT技術社區(qū)) ”的測試結果

  采用direct path read完成讀取的條件：

  1）表大于_small_table_threshold的參數(shù)值設置。

 _small_table_threshold的單位為block。默認為db cache size的2%大小，在實例啟動過程中動態(tài)決定。11GR2之前，表的大小要是_small_table_threshold參數(shù)值的5倍才會采取直接路徑讀取方式，11GR2后只需要滿足_small_table_threshold定義的大小就會采取直接路徑讀取。

  2）表上的臟塊小于表總block數(shù)的25%

 3）表中的塊被cache的比例小于50%的時候

 自己實際測下來，1是準的，2與3不準，暫時存疑。

關閉 direct path read 特性

 1._ serial_direct_read =false  

 2.Event:  '10949 trace name context forever, level 1'