Eliminating DB File Scattered Reads of Oracle Wait Event
In Oracle, a DB file scattered read normally indicates a full table scan. A DB file scattered read is the same type of wait event as DB file sequential read, except that Oracle is reading multiple data blocks. Depending on the setting for the DB_FILE_MULTIBLOCK_READ_COUNT parameter in your parameter file, multi-block reads are typically used on full table scans. The name “scattered read” is somewhat misleading, but it refers to the fact that multiple blocks are read into the database block buffers “scattered” throughout Oracle’s buffer memory.
Some data structures are well designed by database designers and database runs very smoothly. But during daily operations, there are chances to add more tables in the database without indexes or useless indexes. Due to this reason full table scan is performed. Sometimes, the table doesn’t have indexes and doesn’t give any performance drawback but while you are adding more and more data during daily operations then those tables become problematic due to full table scans. It happens due to a lack of accurate indexes. If a full table scan is performed on a small table then it is not problematic but when it occurs on a large table then obviously performance becomes degraded. The scattered read information is kept in the Oracle dynamic data dictionary view v$system_event, and you can easily interrogate scattered reads by selecting all values from this view.
Whereas full table scans are acceptable and even desirable, in cases where Oracle is scanning a small table or retrieving most of the rows in a table in a traditional Oracle environment. If a small table contains a primary key index then Oracle might degrade performance because of scanning more than one segment. If the table contains an index but doesn’t use then it is a problem with either poor table structure or SQL query writing. In both cases, you need to understand the data structure first and rectify the issue.