Wait Event:
DB file sequential reads
Possible Causes:
- Use of an unselective index
- Fragmented Indexes
- High I/O on a particular disk or mount point
- Bad application design
- Index reads performance can be affected by slow I/O subsystem and/or poor database files layout, which results in a higher average
wait time
Actions:
- Check indexes on the table to ensure that the right index is being used
- Check the column order of the index with the WHERE clause of the Top SQL statements
- Rebuild indexes with a high clustering factor
- Use partitioning to reduce the number of blocks being visited
- Make sure optimizer statistics are up to date
- Relocate ‘hot’ datafiles
- Consider the usage of multiple buffers pools and cache frequently used indexes/tables in the KEEP pool
- Inspect the execution plans of the SQL statements that access data
through indexes - Is it appropriate for the SQL statements to access data through
index lookups?
- Is the application an online transaction processing (OLTP) or decision support system (DSS)?
- Would full table scans be more efficient?
- Do the statements use the right driving table?
- The optimization goal is to minimize both the number of logical and physical I/Os.
Remarks:
- The Oracle process wants a block that is currently not in the SGA, and it is waiting for the database block to be read into the SGA from the disk.
- Significant DB file sequential read wait time is most likely an application issue.
- If the DBA_INDEXES.CLUSTERING_FACTOR of the index approaches the number of blocks in the table, then most of the rows in the table are ordered. This is desirable.
- However, if the clustering factor approaches the number of rows in the table, it means the rows in the table are randomly ordered and thus it requires more I/Os to complete the operation. You can improve the index’s clustering factor by rebuilding the table so that rows are ordered according to the index key and rebuilding the index thereafter.
- The OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING initialization parameters can influence the optimizer to favor the operation of the nested loop and choose an index access path over a full table scan.
- Tuning I/O-related waits for Note id 223117.1
- DB file sequential read Reference Note# 34559.1
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
