Shulou(Shulou.com)06/01 Report--

The direct path read/read temp wait event occurs when a session reads blocks directly from disk to PGA (bypassing SGA). The following figure briefly describes how to read in this way:

If the Imap O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.

The direct path read/direct path read temp wait event takes three parameters, which are located under the User Imax O wait class.

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

The file number of the data file in which the data block is to be read by file#:

First dba: the starting block number to read

Block cnt: number of blocks to read

There are four reasons for this wait event:

1. The executed sql statement contains a large number of sorted data, order by/group by/union/distinct/rollup, etc.

When there is not enough sort area in the PGA, Oracle writes the sort result to the temporary table space (direct path write temp). Waits on the direct path read temp event when the sorted data is read from the temporary tablespace.

2.hash join

When hash builds the hash table, it brushes out the hash partition that is not suitable for memory to the temporary tablespace, and then the direct path read temp wait event occurs when the hash partition is read back into memory.

3. Parallel query

During parallel execution, the direct path read wait event is only related to the Slave session derived from the Master session, the Slave session waits on the direct path read event, and the Master session waits for PX Deq.

4. Full table scan

After 11g, if the Oracle senses that the table is large enough, it will read the path directly.

The following tests are performed in turn

1. Direct path read/write temp caused by sorting

Session1:

SQL > alter session set workarea_size_policy=manual;-- set to manual pga management

Session altered.

SQL > alter session set sort_area_size=65536;-- sets the amount of memory allowed for sorting

Session altered.

SQL > create table scott.tb_test as select * from dba_objects where rownum

< 1000; --创建测试表 Table created. session2: SQL>

Oradebug setospid 7038-set 10046 event tracking 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

The statistics of direct reads and writes of temporary tablespaces by session1 before sorting are as follows:

SQL > select a.name SQL B. "VALUE"

2 from v$statname a minute vastsesstat 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 an order by 1, 2, 2, 3 desc) a where rn = 2

OBJECT_ID

-

forty-six

View the tracking file at this time:

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

END OF STMT

PARSE # 139877337074560 Phantom 10940 Magistracy 16752 pamphlet 13meme64 cuttle0MagicalMiss1Law ronomeDepending0DifficienceOggling1Coopers 240700742 moment timers 1542267932869427

EXEC # 139877337074560 Phantom 32 Magistrate 32 Magistracy 32Recycling pamphlet 0pyrrology Cutter0Difficult0 Magistracy Miss0Mizhiwuzhao depended 0DifficienceOGOQUOLTHY PLHING 240700742 PowerTime 1542267932869711

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

WAIT # 139877337074560: nam='Disk file operations I 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 Phantom 1865 Phantom 14945, pamphlet 4, pamphlet 4, crushing, 16, cutting2, misunderstanding 0, law 0, law 1, depended, 0, oglesson 1, plhure, 240700742, timeline 1542267932884816

WAIT # 139877337074560: nam='SQL*Net message from client' ela= 197driver id=1650815232 # bytes=1 p3400 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 Phantom 516 Magnum 515 pamphlet 1recronomy 0memorio cutlery Mishi 0memorialricial0depicient0cognac 1magnum plhagia240700742 timeline 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 p3400 obj#=88859 tim=1542267932885761

At this point, check the statistics of session1 for direct reads and writes of temporary tablespaces:

NAME VALUE

-

Physical reads direct temporary tablespace 5

Physical writes direct temporary tablespace 5

As you can see, as a result of the sort operation, Oracle performs five direct writes to the temporary tablespace, writing one block; each and performing two direct reads, reading four block and one block, respectively.

You can also view the tablespaces and segment types in which direct reads and writes occur during sql execution:

SQL > select a.eventrecovera.sid, c.sql_hash_value hash_value,decode (d.ktssosegtpr. 1, script SORT, 2, dada, 3, dada, index, index, 5, lobbies, datas, six, lobs, 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.serializations = 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.eventrea.sidgrad. Sqlwriting hashworthy value hash_value,decode (e.ktssosegtpari 1 pencyclical SORTstories, 2 pencils HASHQ pamphlets 3 pencils datas)) 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.serializations = e.ktssosno (+)

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

15 and a.sid = d.sid

16 and b.fileroomid= 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

You can see that it is indeed the hash object that occupies the temporary table space.

2. Direct path read/write temp caused by hash join

Create test table 2:

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

< 2; Table created. hash join前session1对临时表空间直接读写的统计如下： NAME VALUE ---------------------------------------------------------------- ---------- physical reads direct temporary tablespace 225 physical writes direct temporary tablespace 718 session1执行查询： SQL>

Select / * + use_hash (AMRO b) leading (AMO b) * / a.object_id

2 from scott.tb_test a,scott.tb_test2 b

3 where a.object_name = b.object_name

OBJECT_ID

-

twenty

View the tracking file:

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 (AMRO b) leading (AMO 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 Drew cymbals 83 Magistracy ePhen83Different pamphlet 0pyrronomy crunch0PowerCutter 0MIZOMIZOMIZOMIZOLERAPHY 0MICOLING depended 0MYOGRAPHY 1CHING PLHING 3759002927 timeline 1542269239154407

EXEC # 139877337073608 Drexel cymbals 53 Magistracy 53 Magistracy 54 pantomime pamphlet crackle cuttle0memorials misunderstanding 0memorialronomic0depicition0memorialogicialsplhages 3759002927 timeline 1542269239154772

WAIT # 139877337073608: nam='SQL*Net message to client' ela= 3 driver id=1650815232 # bytes=1 p3room0 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 Flux 139877337073608Phantom 16849 reignee 16849 reigneur paupery 45 memorialcutlery 0 memoir misdemeanor 0 memorialritorial1 depended 0memorie1 legendary 3759002927 famine timeline 1542269239171729

WAIT # 139877337073608: nam='SQL*Net message from client' ela= 170 driver id=1650815232 # bytes=1 p3room0 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 Phantom 140Divisione139thecontrol cracklle cutlery MIQIZOMIZOMIZOMIZOCHELERRAPHY 0MECHEROLING depended 0MICROLING OGTHY

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 p3room0 obj#=88864 tim=1542269239172458

In this case, session1's statistics on direct reads and writes of temporary tablespaces are as follows:

NAME VALUE

-

Physical reads direct temporary tablespace 229

Physical writes direct temporary tablespace 722

As you can see, Oracle performs four direct reads and four direct writes to the temporary tablespace as a result of the sort operation.

3. Direct path read caused by parallel query

For obvious results, increase the number of records in the scott.tb_test table:

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

...

Modify the parallelism of the scott.tb_ test table:

SQL > alter table scott.tb_test parallel 3

Session1 executes the query:

SQL > select count (1) from scott.tb_test

Query Master/Slave session:

SQL > select decode (a.qcserializations, 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

You can see that there are a total of four session, and one parent derives three child.

Looking at the tracking file at this time, you can see that parent session is waiting for PX Deq.

Select count (1) from scott.tb_test

END OF STMT

PARSE # 139877336651544 Phantom 19135memoir 59969recorder paupery 59969recorder paupery 128pyrronomy cuttle0memorialMishi 1cognally0magistro depended 0memorialOGCING 191387777665151552272658766051 pendant 291292600

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

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

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

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

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

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

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

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

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

EXEC # 139877336651544 Phantom 1759 reign 60216reigneur pant0pyrrhen43 cuttle0magicalmishi 0law0leglegdepict0memorialoglegic1cognac 291292600 timeline 1542272658826648

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

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

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

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

Looking at the v$active_session_history, you can see that the derived child session is waiting on the direct path read event:

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

...

Because parent session does not wait on direct path read, to count parent session waits on direct path read, you need to use the following statement:

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

Where the value of panret session is the accumulation of all child session.

4. Direct path read wait event caused by full table scan

After 11G, direct path read can not only be used for parallel queries, but also direct path read can be used to complete serial full table scanning after some conditions are met.

The following summary quotes the test results of "in-depth Analysis of direct path read (11G) http://www.itpub.net/thread-1815281-1-1.html (Source: ITPUB Forum-China's Professional IT Technology Community)"

Conditions for using direct path read to complete the read:

1) the parameter value setting in which the table is greater than _ small_table_threshold.

The unit of _ small_table_threshold is block. The default is 2% of the size of db cache size, which is dynamically determined during the startup of the instance. Before 11GR2, the size of the table will not be read directly until it is 5 times the value of the parameter _ small_table_threshold. After 11GR2, only the size defined by _ small_table_threshold will be read directly.

2) the dirty mass on the table is less than 25% of the total block of the table.

3) when the proportion of blocks in the table is less than 50% of cache

Their actual test, 1 is accurate, 2 and 3 are not accurate, temporarily doubtful.

Turn off the direct path read feature

1. _ serial_direct_read = false

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.