db file scattered read等待事件

db file scattered read等待事件

# http://www.askmaclean.com/archives/db-file-scattered-read.html

Waits on this event indicate the statement is performing a full table scan or an fast index full scan. This is often reduced by adding an index or making the index more efficient.

db file scattered read 又叫多块读等待，等待事件多发生在数据库正在进行全表扫描或者索引快速扫描时，参数db_file_multiblock_read_count设置数据库一次从磁盘上读取的块数，但是将数据散列的放在不连续的高速缓冲区中。

Solutions

Tune the SQL statement so that it uses an index rather than a full table scan if warranted. If the table is small, a full table scan could be more efficient that using an index so test the differences. Use the Ignite Objects tab to determine the most costly full table scan if there are more than one table in the query.

Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.

Slow disks could be causing Oracle to spend time reading the data into the buffer cache. Review the ‘DB Multi Block Disk Read Time’ metric in Ignite to determine disk speeds from Oracle’s perspective. If the time to read data is above 30ms, that could indicate slow disks.

Update table and index statistics if they are stale so that Oracle understands the benfits of existing indexes.