事件：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'.

Check the following V$SESSION_WAIT parameter columns:

• P1 - The absolute file number

• P2 - The block being read

• P3 - The number of blocks (should be greater than 1)

由引數P1與P2推得訪問的資料物件：

select s.segment_name, s.partition_name
  from dba_extents s
where between s.block_id and (s.block_id + s.blocks -1) and s.file_id =

 Finding the SQL Statement executed by Sessions Waiting for I/O

Use the following query to determine, at a point in time, which sessions are waiting for I/O:

SELECT SQL_ADDRESS, SQL_HASH_VALUE
  FROM V$SESSION 
 WHERE EVENT LIKE 'db file%read';  

SELECT row_wait_obj# 
  FROM V$SESSION 
 WHERE EVENT = 'db file scattered read';

SELECT owner, object_name, subobject_name, object_type
  FROM DBA_OBJECTS
 WHERE data_object_id = &row_wait_obj;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/195110/viewspace-679316/，如需轉載，請註明出處，否則將追究法律責任。