[ db file sequential read vs db file scattered read ]

타락천사 http://blog.naver.com/darkturtle/50014865473
[ db file sequential read vs db file scattered read ]
- 원문 : Why are Oracle’s Read Events “Named Backwards”?
- 출처 : www.hotsos.com
The event names db file sequential read and db file scattered read describe how blocks are stored in memory, not how they are read from disk.
The two Oracle read event names are analogous to two classes of UNIX read calls,
Represented by the functions read() and readv(). The UNIX read() function reads a contiguous portions read and stores it contiguously in a single memory area.
The readv() function reads a contiguous portion of a file and stores pieces of that data in
Different areas of memory dictated by an array of memory references.
Oracle disk-reads executed through calls like read() are recorded as db file sequential read events, and disk-reads executed through calls like readv() are recored as db file scattered read events

A db file sequential read occurs when the memory receiving the contents of a disk read is contiguours.
A db file scattered read event occurs when the memory receving the contents of a disk
Read is not guarateed to be contiguous.




The following table describe cirsumstances that generate Oracle’s db file sequential read and db file scattered read events.
Oracle Event
Operations that generate
Single-block read events
(p3 =1 )
Operations that generate
Multi-block read events
( p3 > 1 )
Db file sequential read
Index Scans,
Full-table sacns that skip
Blocks within an extent,
Table access by rowed
Temporary segment reads
Db file scattered read
Never
Full-table scans,
Fast full-Index scans…
v$session p1 : file#, p2:Starting Block#, p3:블록수

!! db file sequential read event 의 경우 disk 의 block 을 Data buffer 내에
연속되게 올린다(load)는 의미에서 그 이름이 유래 되었다.
– 주로 index scan,
하나의 extent 내의 table scan 의 경우도 ( block 이 하나일 경우)
반대로 db file scattered read 의 경우 disk 의 block 을 data buffer 내에
불연속적으로 올린다.
- 주로 Full table sacn, 상대적으로 full table scan 의 경우 data buffer 로
Loading 할 data 량이 많기 때문에 연속된 data buffer cache 를 찾는 것이
비효율적이라 scatter(마구 뿌려서) 하게 올린다는 의미이다.