Waits on ‘db file sequential read’ normally occur during index lookups when the block is not in memory and must be read from disk. They are generally considered a ‘good’ read unless the index being used is not very efficient. In this case the query will read more blocks than necessary and possibly age out other good blocks from the cache.
Solutions
Tune the SQL statement so that it reads fewer blocks. If the top objects listed in the Ignite Object tab are indexes, determine if there is a more efficient index that can be used. If the top objects are tables, Oracle is going back to the table to get more data after the index lookup completes. That may indicate criteria in the WHERE clause that is not using a column in this index. Adding that to the index could help performance.
INSERT statements can also wait on this event because it is being forced to update inefficient indexes. Review the Ignite Object tab to determine which indexes are being waited for. If they are inefficient, Oracle is most likely not utlizing them in other SQL statements, so consider dropping them.
Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.
Slow disks could be causing Oracle to spend time reading the data into the buffer cache. Review the ‘DB Single Block Disk Read Time’ metric in Ignite to determine disk speeds from Oracle’s perspective. If the time to read data is above 20ms, that could indicate slow disks.
Leave a Reply