When Oracle executes a SQL statement, it is not constantly executing. Sometimes it has to wait for a specific event to happen before it can proceed.
Like : If Oracle wants to modify data , and the corresponding data block is not present in the Database BUFFER chache(SGA), Then it waits for that block to be available for modification.
You can describe the V$EVENT_NAME and you will see what all are the possible wait events that Oracle might face at different times or in different situation.
Today I will discuss about two main wait events, later on I will discuss more.
DBFILE SEQUENTIAL READ : (INDEX FETCH BY ROW ID)
The db file sequential read 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 sequential read is usually a single-block read. Single block I/Os are usually the result of using indexes.
How U can Reduce this Wait Time:
Block reads are fairly inevitable so the aim should be to minimize un-necessary IO. This is the best achieved by good application design and efficient execution plans.
· Kindly rebuild the indexes of the major queries related to the application. Consult the application team for the same.
· A less obvious issue which can affect the IO rates is how well data is clustered physically. Pre-sorting or re-organizing data can help to tackle this in severe situations.
DB FILE SCATTERED READ : (FULL TABLE SCAN)
A DB file scattered read issues a scatter-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 table scan is occurring. When performing a full table 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.
Oracle can read multiple blocks (up to the initialization parameter db_file_multiblock_read_count).
The parameter db_file_multiblock_read_count depends on the platform and the release of Oracle you are running . From Oracle 10g it is recommended to not set the value as it is dynamically taken up by Oracle.