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

The db file scattered read wait event takes three parameters, which belong to the wait class of User Imax O:

SQL > select name,parameter1,parameter2,parameter3,wait_class from v$event_name where name ='db file scattered read'

NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS

Db file sequential read file# block# blocks User I/O

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

The starting block number to be read by block#:.

Blocks: the number of blocks to read.

The db file scattered read (data file discrete read) wait event is submitted when the session issues an Istroke O request to read multiple data blocks. The process is shown in the figure:

The reason why db file scattered read waits for the event to occur:

1. Full table scan

two。 Index fast full scan

Here, let's verify the two cases respectively.

First, create a test table:

SQL > create table scott.tb_test (id int,age int,name varchar2 (20))

Table created.

Insert test data:

SQL > begin

2 for i in 1..1000 loop

3 insert into scott.tb_test values (iMagazine iMagn1mt')

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

Track the current session 10046 in another session:

SQL > oradebug setospid 9305

Oracle pid: 22, Unix process pid: 9305, image: oracle@zhuga (TNS V1-V3)

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

Statement processed.

SQL > oradebug tracefile_name

/ opt/app/oracle/diag/rdbms/bddev2/BDDEV2/trace/BDDEV2_ora_9305.trc

Operate on the test table and continuously observe the output of the tracking file.

1. Full table scan

Execute the following statement in the current session:

SQL > alter system flush buffer_cache

System altered.

SQL > select * from scott.tb_test where age = 2

ID AGE NAME

1 2 test

View the tracking file:

* 2018-11-08 14 14 Fran 22 Fran 24.399

WAIT # 139755673442328: nam='SQL*Net message from client' ela= 9621966 driver id=1650815232 # bytes=1 p3400 obj#=91054 tim=1541658144399459

CLOSE # 139755673442328 Phantom 9, depravity, typewriting 3, timeliness 1541658144399580

=

PARSING IN CURSOR # 139755673515256 len=41 dep=0 uid=0 oct=3 lid=0 tim=1541658144399734 hv=3789676117 ad='55ad24638' sqlid='493dsa3hy3rkp'

Select * from scott.tb_test where age = 2

END OF STMT

PARSE # 139755673515256 Flux 144Magistrate 113DifferentiaPhen0memorialcronomicalCupper0pyritoriality0memorialDepending0Differentia0DifficienceOGREOCHERELLLPLHY 1092599453 timeline 1541658144399732

EXEC # 139755673515256 Phantom 27momentum pencyclo pendant 0pyrronomy crunch0phae0pyrus 0phae0phae0phae0ptimum 1092599453 Phi 154165814439999967

WAIT # 139755673515256: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3400 obj#=91054 tim=1541658144400005

WAIT # 139755673515256: nam='db file sequential read' ela= 13 file#=5 block#=178 blocks=1 obj#=91054 tim=1541658144400098

WAIT # 139755673515256: nam='db file scattered read' ela= 20 file#=5 block#=179 blocks=5 obj#=91054 tim=1541658144400359

FETCH # 139755673515256 Phantom 427 ~ (th) ~ (th) 427 ~ (th) ~ (th) ~ (th) pendant 427 ~ (th) ~ (th) pendant 6 ~ (th) the crackers 5 ~ (th) the cuttings 5 ~ (th), the misunderstanding 0 ~ (th), the r ~ (1) (depended) ~ (0)), the ogical () ~ (1)) (plhages) 1092599453

WAIT # 139755673515256: nam='SQL*Net message from client' ela= 179 driver id=1650815232 # bytes=1 p3400 obj#=91054 tim=1541658144400675

FETCH # 139755673515256 Phantom 4, pendant 4, pendant 0, crackers 3, cutlery 0, misprit 0, ritual 0, deporation, ogony 1, plhages, 1092599453, timeline 1541658144400760

STAT # 139755673515256 id=1 cnt=1 pid=0 pos=1 obj=91054 op='TABLE ACCESS FULL TB_TEST (cr=8 pr=6 pw=0 time=414 us cost=3 size=38 card=1)'

WAIT # 139755673515256: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3400 obj#=91054 tim=1541658144400842

You can see that a single-block read and a multi-block read have occurred, where the single-block read is the first block to access the scott.tb_test:

SQL > select owner,header_file,header_block from dba_segments where segment_name = 'TB_TEST'

OWNER HEADER_FILE HEADER_BLOCK

SCOTT 5 178

Multi-block reads are blocks of read scott.tb_test:

SQL > select distinct dbms_rowid.rowid_relative_fno (rowid), dbms_rowid.rowid_block_number (rowid) from scott.tb_test order by 2

DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) DBMS_ROWID.ROWID_BLOCK_NUMBER (ROWID)

5 179

5 182

5 183

two。 Index fast full scan

Create an index:

SQL > create index scott.idx_id on scott.tb_test (id)

Index created.

Execute the following statement in the current session:

SQL > alter system flush buffer_cache

System altered.

SQL > select id from scott.tb_test where id = 100

View the trace file:

PARSING IN CURSOR # 139755673927112 len=43 dep=0 uid=0 oct=3 lid=0 tim=1541659656338252 hv=2432667479 ad='55b721790' sqlid='1vgubku8gz3ur'

Select id from scott.tb_test where id = 100

END OF STMT

PARSE # 139755673927112Phantom 80, pause, pendant, crushing, curving, cuttle0, mischievous, deporation, ogony, plhure, 578627003, timeline, 1541659656338250

EXEC # 139755673927112Phantom 32meme0pyrricium crunch0memorialitymischievy0memorialDepending0DifficienceOgglleg1Magi plhagia578627003 timeline 1541659656338348

WAIT # 139755673927112: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3400 obj#=91056 tim=1541659656338387

WAIT # 139755673927112: nam='db file sequential read' ela= 20 file#=5 block#=187 blocks=1 obj#=91057 tim=1541659656338473

WAIT # 139755673927112: nam='db file sequential read' ela= 9 file#=5 block#=188 blocks=1 obj#=91057 tim=1541659656338534

FETCH # 139755673927112Partition 163memoriale192pacific2magicalCROGRAPHY 0MIZOMIZOMICHY 1DIFFECTIVE 0MICROLING 1, PLHING 578627003 moment 1541659656338602

WAIT # 139755673927112: nam='SQL*Net message from client' ela= 158driver id=1650815232 # bytes=1 p3400 obj#=91057 tim=1541659656338804

FETCH # 139755673927112 Freud cymbals 89 report1541659656338941

STAT # 139755673927112 id=1 cnt=1 pid=0 pos=1 obj=91057 op='INDEX RANGE SCAN IDX_ID (cr=3 pr=2 pw=0 time=186 us cost=1 size=13 card=1)'

WAIT # 139755673927112: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3400 obj#=91057 tim=1541659656339036

You can see that the execution plan at this time is an index range scan, and the wait event is db file sequential read. Add hint to force fast full scan of the index:

SQL > alter system flush buffer_cache

System altered.

SQL > select / * + index_ffs (tb_test idx_id) * / id from scott.tb_test where id = 100

ID

-

one hundred

View the trace file:

PARSING IN CURSOR # 139755673927112 len=75 dep=0 uid=0 oct=3 lid=0 tim=1541659831795488 hv=2021045085 ad='55a741cc0' sqlid='8hbw5jtw7dcux'

Select / * + index_ffs (tb_test idx_id) * / id from scott.tb_test where id = 100

END OF STMT

PARSE # 139755673927112 Phantom 2808 Magistracy 2974 pamphlet 10 crackers 11 pendant 11 cutlery Mitchell 1 pamphlet 0 depended 0 Magistracy Ogosphere 1 prima facie 3381169862 moment timeline 1541659831795487

EXEC # 139755673927112Phantom 16momentum 16momentum pencyclophora crunch0memorio cutting0Mishi 0memorialdepict0memorialoglinglmagical13811698662Query timbre 1541659831795676

WAIT # 139755673927112: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3400 obj#=91056 tim=1541659831795714

WAIT # 139755673927112: nam='db file sequential read' ela= 10 file#=5 block#=186 blocks=1 obj#=91057 tim=1541659831795799

FETCH # 139755673927112 Phantom 116recorder pendant 1recinct crackers 4handlers cuttings 0memorials Mishi 0memorialritoriallaw depended 0memorialoglemagrical plains 3381169862 moment 1541659831795853

WAIT # 139755673927112: nam='SQL*Net message from client' ela= 130 driver id=1650815232 # bytes=1 p3400 obj#=91057 tim=1541659831796029

WAIT # 139755673927112: nam='db file scattered read' ela= 6 file#=5 block#=189 blocks=2 obj#=91057 tim=1541659831796106

FETCH # 139755673927112Phantom 107 pamphlet 2pyrrhen3 cuttle0phasic 0MIZOMIZOCHEREOCHERALIZER depended 0MICROOGRAPHY 1 Magi plhages 3381169862 DemorialTime 1541659831796169

STAT # 139755673927112 id=1 cnt=1 pid=0 pos=1 obj=91057 op='INDEX FAST FULL SCAN IDX_ID (cr=7 pr=3 pw=0 time=92 us cost=4 size=13 card=1)'

WAIT # 139755673927112: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3400 obj#=91057 tim=1541659831796258

You can see that the execution plan takes a fast full scan of the index at this time, and there is a single block read (the block head of the read index) and a multi-block read, and the object of the multi-block read happens to be the index:

SQL > select owner,segment_name,segment_type from dba_extents where file_id = 5 and 189 between block_id and block_id+blocks-1

OWNER SEGMENT_NAME SEGMENT_TYPE

SCOTT IDX_ID INDEX

Note:

1. The average wait time should not exceed 20ms, otherwise it proves that there is contention for storage.

The 2.OLTP system should avoid this wait event.

3. Optimization methods: increase SGA, avoid full table scanning, improve storage performance, do cache for frequent access, etc.

4. Increase database parameters: db_file_multiblock_read_count can reduce the number of waits, but it is not valid if it is greater than 1MB/blocks_size.

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.