In a previous post, “db file sequential read” wait event, I have explained the db file sequential read wait which is the most common wait event in Oracle that is associated with I/O Wait). In this post I will describe the second most common event that I see when system suffer from I/O waits: db file scattered read.This important information is taken from the Oracle performance white paper written by Bartal Vindzberg and myself (which will be posted in a future blog article).
The db file scattered read wait event represents a wait for a physical read of multiple Oracle blocks from the disk (unlike db file sequential read which represents reading a single block). It is usually caused by scanning the entire or a sub-range of a table, index, table partition, or index partition. The following SQL statement illustrates accessing a table via full table scan:
The following diagram illustrates an Oracle shadow process that scans multiple blocks from the disk and places them in the buffer cache:
Adjacent blocks are read from disk allowing Oracle to read many blocks by performing large I/Os. Thus, from the storage prospective, db file scattered read is a large sequential read with block size that can get up to 1 MB.
db file scattered read is common for applications with a high amount of large reads (such as full or range scans). This is true for BI, DWH and DSS workload environments.
In order to improve db file scattered read, a storage system needs to provide both low latency and high throughput. Kaminario K2 is an ideal storage system for Oracle because it can speedup both single (db file random read) and multiple block access (db file scattered read).