Home > fundamental > sequential read vs scattered read(知识点整理)

sequential read vs scattered read(知识点整理)

November 12th, 2011 Leave a comment Go to comments

之前一直对这两个概念比较模糊,最近整理了一下sequential read vs scattered read的知识点。

sequential read 跟scattered read是oracle将磁盘block分配到buffer cache的两种方式,是站在oracle分配内存的角度讲的,而不是指读取磁盘block的方式。

sequential read :每次物理IO对应单个block read(usually a single-block read with p3=1, but can be multiple blocks)。比如index range scan+回表的时候,因为需要访问index一条记录,再根据rowid找到table中的记录,依次循环。这种方式就是所谓”顺序读”。因为每次都是单个block IO,所以会产生磁盘的random access。这个event往往表明disk contention on index reads.

scattered read :每次物理IO对应多个block read(p3=>2)。比如FTS、index FFS的时候,利用muliblock read的特点,每次IO最多可以读取MIN(db_file_muliblock_read_count , OS’s max IO)数量的db block,然后将得到的blocks离散分配(scatter)到buffer cache,就是所谓”离散读”。this event genarally indicates disk contention on full table scan.

这两个概念perfomence tuning跟tom都有很好的解释:

performence tuning guide:

db file scattered read

This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A db file scattered read issues a scattered read to read the data into multiple discontinuous memory locations. A scattered read is usually a multiblock read. It can occur for a fast full scan (of an index) in addition to a full table scan.

The db file scattered read wait event identifies that a full scan is occurring. When performing a full scan into the buffer cache, the blocks read are read into memory locations that are not physically adjacent to each other. Such reads are called scattered read calls, because the blocks are scattered throughout memory. This is why the corresponding wait event is called ‘db file scattered read’. multiblock (up to DB_FILE_MULTIBLOCK_READ_COUNT blocks) reads due to full scans into the buffer cache show up as waits for ‘db file scattered read’.

db file sequential read

This event signifies that the user process is reading a buffer into the SGA buffer cache and is waiting for a physical I/O call to return. A sequential read is a single-block read.

Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache. These waits would also show up as ‘db file sequential read’.

asktom:

sequential read:

if the query plan was "index range scan, table access by index rowid", it will use db file sequential reads, read index, read table block, read index, read table block – all single block IO.
there are many blocks being read – sequentially from index to table, index to table. That is what db file sequential read ‘means’, sequentially from index to table. db file sequential read is the wait even for SINGLE BLOCK IO. A block at a time, block by block, from index to table.

scattered read:

it would probably employ multiblock reads – which if done using physical IO would wait on db file scattered reads – meaning read a bunch of blocks and SCATTER them in the buffer cache.

至于什么时候会产生sequential ,scattered event,tom也有一个简单的code:

we use db file scattered reads when we know we are going to read more than one block. we use db file sequential reads when we know we are going to read only one block.

if (blocks to read = 1) 
then 
   read that single block
   add time to the wait event db file sequential read
elsif (blocks to read > 1 ) 
then
   read those blocks
   add time to the wait even db file scattered read
end if;

performance tuning 上也有event的图解:

Figure 10-1 depicts the differences between the following wait events:

  • db file sequential read (single block read into one SGA buffer)

  • db file scattered read (multiblock read into many discontinuous SGA buffers)

  • direct read (single or multiblock read into the PGA, bypassing the SGA)

Figure 10-1 Scattered Read, Sequential Read, and Direct Path Read

 

Description of Figure 10-1 follows

 

上面引用performence tuning也提到了,FTS有时候也会产生sequential read:

1. 达到了extent的边界。

2. 部分blocks已存在于buffer cache中了。

此外,scattered read并不是每次都读db_file_multiblock_read_count个数据块,以下3种情况会降低实际的I/O大小。

•I/O的上限限制了单次磁盘读的最大IO。比如将db_file_multiblock_read_count设成1000的情况。

•每次磁盘读不能跨越区的边界(extent boundary)。也就是每次I/O只能在一个extent内发生。

•如果buffer cache中已经有合适的版本块了,那么oracle不会为该块产生一个物理读。

trace的文件类似于(p3并不总是16):

WAIT #47: nam=’db file scattered read’ ela= 0 p1=12 p2=71980 p3=9

WAIT #47: nam=’db file scattered read’ ela= 0 p1=12 p2=71992 p3=4

WAIT #47: nam=’db file scattered read’ ela= 0 p1=12 p2=71997 p3=16

reference见:

http://www.johnnydb.com/2011/10/points/

Categories: fundamental Tags:
  1. No comments yet.
  1. No trackbacks yet.