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

The db file sequential 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 sequential 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 (this should be equal to 1).

When a process needs to access a block that does not exist in the SGA, the process waits for Oracle to read the block from the disk into the SGA. The wait event that occurs during this process is db file sequential read, as shown in the figure:

The db file sequential read wait event is a common cause of occurrence:

1. Index access and table return

two。 Access undo segment or rollback operation

3. Directly access the data in the table in ROWID mode

Here, let's verify the three cases respectively.

First, create the test table and index:

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

Table created.

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

Index 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 3836

Oracle pid: 22, Unix process pid: 3836, 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_3836.trc

Continuously observe the output of the tracking file

1. Access to the index

View the cumulative wait information of the current session:

SQL > SELECT INST_ID

2, SID

3, EVENT

4, TOTAL_WAITS WAITS_CNT

5, TIME_WAITED

6, AVERAGE_WAIT/100 "AVERAGE_WAIT (S)"

7, MAX_WAIT/100 "MAX_WAIT (S)"

8 FROM GV$SESSION_EVENT

9 WHERE SID=&INPUT_SID

10 ORDER BY TOTAL_WAITS DESC

Enter value for input_sid: 812

Old 9: WHERE SID=&INPUT_SID

New 9: WHERE SID=812

INST_ID SID EVENT WAITS_CNT TIME_WAITED AVERAGE_WAIT (S) MAX_WAIT (S)

-

1 812 db file sequential read 207 15. 0007. 01

1 812 SQL*Net message to client 29 0 0 0

1 812 SQL*Net message from client 28 215952 77.1256 680.52

1 812 Disk file operations I/O 6 0. 0001 0

1 812 log file sync 5 0. 0005 0

1 812 events in waitclass Other 3 5. 0156. 02

1 812 db file scattered read 1 0 0 0

Execute the following statement in the current session:

SQL > alter system flush buffer_cache

System altered.

SQL > select / * + index (tb_test,idx_id) * / age from scott.tb_test where id = 500

Track the contents of the file:

PARSING IN CURSOR # 140128573892064 len=72 dep=0 uid=0 oct=3 lid=0 tim=1541577174155088 hv=968314915 ad='55a3880b0' sqlid='d53f2unwvfn13'

Select / * + index (tb_test,idx_id) * / age from scott.tb_test where id = 500

END OF STMT

PARSE # 140128573892064 Paradise 94 Magistrate 93memoir paupery 0recinct crunch0pyritorius cuttle0memoriality0memorialronomic0depicition0memorialoglemagen1923716061PowerTime 1541577174155086

EXEC # 140128573892064 Drew cymbals 42 Magistracy ePhen42Different pamphlet crinkling 0Different Cutters 0MIZOLER 0MIZOLERICHY 0DIFFECTIVE 0MIQUOLING OGTHERONE 1923716061 moment 1541577174155379

WAIT # 140128573892064: nam='SQL*Net message to client' ela= 2 driver id=1650815232 # bytes=1 p3400 obj#=91021 tim=1541577174155417

WAIT # 140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=147 blocks=1 obj#=91022 tim=1541577174155531

WAIT # 140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=150 blocks=1 obj#=91022 tim=1541577174155602

WAIT # 140128573892064: nam='db file sequential read' ela= 11 file#=5 block#=134 blocks=1 obj#=91021 tim=1541577174155659

FETCH # 140128573892064 Phantom 227 Magnum eBay 239, pendant 3, crackers 3, cutlery 0, Mishi 0, law 1, depict, 0, ogteach, 1, magic, 1923716061, timeline, 1541577174155687

WAIT # 140128573892064: nam='SQL*Net message from client' ela= 154driver id=1650815232 # bytes=1 p3600 obj#=91021 tim=1541577174155878

FETCH # 140128573892064 Phantom 101Magistracy eBay 100law pamphlet 0memorialCutter 0MIZOMIZOLING 0MIXINOMIAND 0MIZOMIAND 0DIFFERETIOGRAPHY OGTHY 1MAL plhagias 1923716061

STAT # 140128573892064 id=1 cnt=1 pid=0 pos=1 obj=91021 op='TABLE ACCESS BY INDEX ROWID TB_TEST (cr=4 pr=3 pw=0 time=217 us cost=1 size=26 card=1)'

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

WAIT # 140128573892064: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3400 obj#=91021 tim=1541577174156117

SQL > select owner,object_name,object_type from dba_objects where object_id = 91022

OWNER OBJECT_NAME OBJECT_TYPE

SCOTT IDX_ID INDEX

Or

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

OWNER SEGMENT_NAME SEGMENT_TYPE

SCOTT TB_TEST TABLE

You can see that there are three db file sequential read waits, two of which are to access the index (one to access the index header block and the other to access the real data block) and one to return to the table

Check the cumulative wait information of the current session at this time:

INST_ID SID EVENT WAITS_CNT TIME_WAITED AVERAGE_WAIT (S) MAX_WAIT (S)

-

1 812 db file sequential read 210 15. 0007. 01

1 812 SQL*Net message to client 32 0 0 0

1 812 SQL*Net message from client 31 233915 75.4564 680.52

1 812 Disk file operations I/O 6 0. 0001 0

1 812 log file sync 5 0. 0005 0

1 812 events in waitclass Other 4 6. 0152. 02

1 812 db file scattered read 1 0 0 0

You can see that the information is consistent.

two。 Access undo or rollback

Modify scott.tb_test data in another session without submitting:

SQL > update scott.tb_test set age = 100,500 where id

1 row updated.

Execute on the current session:

SQL > alter system flush buffer_cache

System altered.

SQL > select age from scott.tb_test where id = 500

AGE

-

five hundred and one

PARSING IN CURSOR # 140128575016040 len=44 dep=0 uid=0 oct=3 lid=0 tim=1541577933156059 hv=2526155070 ad='55a80a9a0' sqlid='0x9dgqqb9449y'

Select age from scott.tb_test where id = 500,

END OF STMT

PARSE # 140128575016040 Phantom 125, pamphlet, crinkle, cutlery, mischievity, gentile, depravity, ogony, 1923716061, timeline, 1541577933156058

EXEC # 140128575016040 Phantom 49, pairwise, crunch0, cuttle0, Mishi, razor, depravity, oglemagen1, plhagia1923716061, timeline 1541577933156190

WAIT # 140128575016040: nam='SQL*Net message to client' ela= 3 driver id=1650815232 # bytes=1 p3room0 obj#=91021 tim=1541577933156224

WAIT # 140128575016040: nam='db file sequential read' ela= 18 file#=5 block#=147 blocks=1 obj#=91022 tim=1541577933156326

WAIT # 140128575016040: nam='db file sequential read' ela= 12 file#=5 block#=150 blocks=1 obj#=91022 tim=1541577933156396

WAIT # 140128575016040: nam='db file sequential read' ela= 11 file#=5 block#=134 blocks=1 obj#=91021 tim=1541577933156456

WAIT # 140128575016040: nam='db file sequential read' ela= 12 file#=3 block#=176 blocks=1 obj#=0 tim=1541577933156512

WAIT # 140128575016040: nam='db file sequential read' ela= 12 file#=3 block#=32616 blocks=1 obj#=0 tim=1541577933156594

FETCH # 140128575016040 Phantom 140Phen410, pendant 5, CRO5, cutlery, misunderstanding 0, law 0, deporation, law, law, science, science, chemistry, 1923716061, temporal 154151577933156658

WAIT # 140128575016040: nam='SQL*Net message from client' ela= 164driver id=1650815232 # bytes=1 p3400obj#=0 tim=1541577933156863

FETCH # 140128575016040 Phantom 88 Magistracy Epiphany 88 direction pamphlet 0pyrrology crackle 1handle cutlery Mishi 0memorialricial0depicition0deformedoglemagne 1923716061PowerTime 1541577933156988

STAT # 140128575016040 id=1 cnt=1 pid=0 pos=1 obj=91021 op='TABLE ACCESS BY INDEX ROWID TB_TEST (cr=6 pr=5 pw=0 time=409 us cost=1 size=26 card=1)'

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

WAIT # 140128575016040: nam='SQL*Net message to client' ela= 1 driver id=1650815232 # bytes=1 p3room0 obj#=0 tim=1541577933157090

As you can see, in addition to two index accesses and one return to the table, there are two db file sequential read waits that occur on file#=3,block# 176and 32616, where 176is the header block of the object. View this object (dba_extents or DBA_UNDO_EXTENTS):

SQL > select segment_name,segment_type from dba_extents where file_id = 3 and 176between block_id and block_id + blocks-1

2 union all

3 select segment_name,segment_type from dba_extents where file_id = 3 and 32616 between block_id and block_id + blocks-1

4

SEGMENT_NAME SEGMENT_TYPE

-

_ SYSSMU4_1254879796 $TYPE2 UNDO

_ SYSSMU4_1254879796 $TYPE2 UNDO

Indicates that access to the undo segment can cause db file sequential read to wait.

3. Accessing table data through ROWID is actually the same as indexing back to the table, so it is no longer validated separately.

Note:

1. For db file sequential read waits, most systems are unavoidable. It is not a bad thing for db file sequential read to wait too much. It depends on whether it is reasonable or not.

two。 The average wait time should not exceed 7ms, otherwise it proves that there is contention for storage.

3. Optimization methods: increase SGA, reduce overall physical reads, improve storage performance, select reasonable indexes, cache frequently accessed tables, etc.

Check the db file sequential read wait for each session:

Select a.sid

A.event

A.total_waits

A.time_waited

A.time_waited / c.sum_time_waited * 100pct_wait_time

Round ((sysdate-b.logon_time) * 24) hours_connected

From v$session_event a, v$session b

(select sid, sum (time_waited) sum_time_waited

From v$session_event

Where wait_class' Idle'

Having sum (time_waited) > 0 group by sid) c

Where a.sid = b.sid

And a.sid = c.sid

And a.time_waited > 0

And a.event ='db file sequential read'

Order by hours_connected desc, pct_wait_time

View the objects that db file sequential read is waiting for:

Select b.session_id

Nvl (substr (a. Object _ name)

'P1cards' | | b.p1 | | 'P2legs' | | b.p2 | | 'P3posts' | | b.p3) object_name

A.subobject_name

A.object_type

From dba_objects a, v$active_session_history b, x$bh c

Where c.obj = a.object_id (+)

And b.p1 = c.file# (+)

And b.p2 = c.dbablk (+)

And b.event ='db file sequential read'

Union

Select b.session_id

Nvl (substr (a. Object _ name)

'P1cards' | | b.p1 | | 'P2legs' | | b.p2 | | 'P3posts' | | b.p3) object_name

A.subobject_name

A.object_type

From dba_objects a, v$active_session_history b, x$bh c

Where c.obj = a.data_object_id (+)

And b.p1 = c.file# (+)

And b.p2 = c.dbablk (+)

And b.event ='db file sequential read'

Order by 1

View the average wait time for a single block read on the data file:

Select a.file#

B.file_name

A.singleblkrds-- Total number of waits

A.singleblkrdtim,-- Total waiting time (centiseconds)

A.singleblkrdtim/a.singleblkrds average_wait

From v$filestat a, dba_data_files b

Where a.file# = b.file_id

And a.singleblkrds > 0

Order by average_wait

All waiting events for a session since logging in:

SELECT INST_ID

SID

EVENT

TOTAL_WAITS WAITS_CNT

TIME_WAITED

AVERAGE_WAIT/100 "AVERAGE_WAIT (S)"

MAX_WAIT/100 "MAX_WAIT (S)"

FROM GV$SESSION_EVENT

WHERE SID=&INPUT_SID

ORDER BY TOTAL_WAITS DESC

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.