The db file sequential read is a performance metric that measures the time it takes to execute single block IO. Ideally, it should correlate with the operating system’s IO statistics. Let’s look at some of the parameters that it measures. In particular, the db file sequential read should be able to complete single block IO in less than a second.

Object name resolution

When reading a database sequentially, a database will attempt to resolve references to objects in its definition. This will result in a delayed read, but it is perfectly normal and does not indicate an issue with the database or application. When a database attempts to resolve object names, it notifies the user if the object is nonexistent or has become invalid. It also marks the object as unusable if it has become dependent on a nonexistent object.

The db file sequential read wait event is triggered by SQL statements and single-block read operations. It normally ranks among the top five wait events. However, there are some considerations to consider when determining which objects are being read sequentially. First, check the size of the database being read.

Object name resolution is important to avoid db file scattered read waits. These can account for a significant portion of waits in a session. The objective is to minimize the number of physical and logical I/Os. See the article on db file sequential read events for examples of tuning methods.

Wait state

When reading a database sequentially, you’ll often encounter a wait state that is not expected. This happens because the Oracle process is trying to read a block that is not already in its SGA into the SGA. When you see a wait state for this event, you should check for two numbers: time_waited and average wait. If these numbers are significantly larger than you expect, the wait state is most likely an application issue.

The problem with the wait state when reading database sequentially is that it is hard to determine what is normal. Often, a wait state represents a significant portion of the overall response time for a process. It is difficult to define what is excessive and what isn’t. However, the following guidelines can help you determine if a wait is normal.

The first step to finding out the reason for the wait is to analyze the size of the database. If the size is small, then the wait state is low. If it is larger, it indicates a slow IO subsystem or buffer busy. The second step is to check db cache and IO subsystems to see if they are causing the wait. Then, use a histogram to see if there are any individual long waits.

Time spent

Time spent reading a database sequentially can be a significant source of wasted time. Because a single read is often accompanied by many other IOs, such as enqueue and latch free, it’s difficult to define exactly how much time the db file is spending reading sequentially. The timings for single block reads should generally correlate with operating system measured IO statistics.

A DBA can determine which SQL statement is taking up most of the time by tracing its execution time using Oracle event 10046, or interactive monitoring. If a specific SQL statement is taking the longest time to execute, the DBA can tune it to minimize both logical and physical I/Os.

A db file sequential read event occurs whenever a process issues an I/O request to read a block of data and waits for the operation to complete. A sequential read typically occurs during an index lookup or a fetch from a table by ROWID. If you want to see how much time is being wasted on sequential reads, you can query V$SESSION_WAITS for information on all objects that are waiting.

Average wait time

DB file sequential reads should not take more than 15 milliseconds on average. The wait time is a combination of cache and disk access time. If the wait time exceeds this number, it should be investigated. It should be correlated with other system events, such as IO statistics.

A lower average wait time will be more tolerant and will have a lesser impact on the response time of processes. Increasing PGA is another way to improve wait time. It should not exceed 5% of the total database time. If the wait time is higher than that, there are probably I/O problems.

A high wait time could be caused by a large number of short-running OLTP sessions or a small number of long-running batch processes. However, system-level statistics do not show which user made the I/O calls and which object they accessed. They also do not reveal the SQL statements that initiated the calls.

When the wait time is higher than 30%, it means that there may be a CPU bottleneck on the database server. Another cause of high wait events is that Oracle processes are displaced from the CPU by other processes. As a result, the lock period is extended. This happens when multiple processes serialize on the same lock.

If you can improve db file sequential reads by decreasing the average wait time, you can minimize the impact of these events on your system. You can also monitor the “average wait time” through a V$SESSION_EVENT view. A typical db file sequential read should take less than 10ms or 1cs. However, a SAN should take four to eight ms.

If wait time is too high, you should consider optimizing your database index. If your database has a large number of tables that are not indexed, then full table scans are likely the best option. You can cache the small tables so that they do not have to be read in repeatedly.

Wait events can also be a cause of poor database performance. They make up about 60% of the total response time in well-tuned systems, and can affect response time negatively.