How to Tune Full Table Scan?

June 5, 2014 | By

Eliminating DB File Scattered Reads of Oracle Wait Event

full table scanIn 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 database without indexes or useless index. Due to this reason full table scan is performed. Sometimes, 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 scan. It happens due to lack of accurate indexes. If full table scan is performed on small table then it is not problematic but when it occurs on 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 small table contains primary key index then Oracle might degrade performance because of scanning more than one segment. If table contains index but doesn’t use then it is problem with either poor table structure or SQL query writing. In these both cases, you need to understand data structure first and rectify issue.

Tags: , ,

Category: oracle 9i dba training, Oracle performance tuning

Comments are closed.