DB File Sequential Read wait event is High on Oracle Database - Performance Engineering to Find out where is Bottleneck?

Found one interesting problem during performance testing , wanted to share here.

Observed there is a drop on server throughput, Correlated Load runner Throughput graph with Average Response time graph:

From the above Graph for the first 25mins ART was so high and that time throughput was low , after that both the graphs are stable.

So what happened on first 25mins of the load test , Lets go and do some analysis on server.

So we should start checking the servers ( web/app/DB ) to find out where is the problem , most likely throughput related issues occurs from DB side , lets start investigating from Data base server first.

Started checking with Database first and observed there is a high user I/O activity during high response time (low throughput).

So from OEM console, observed high user I/O.

So we can see the problematic SQL, which is making high User I/O.

So click on above SQL ID and see what event is making this high user I/O ( so by now at least we know which SQL is the culprit)

So above graph shows DB file sequential read event is more  , so get either AWR or ADDM report or Talk to DBA to know why this is so high?

So when I generate ADDM report for Oracle recommendations below is what I got.

The performance of some data and temp files was significantly worse than others. If striping all files using the SAME methodology is not possible, consider striping these file over multiple disks.

For file +DATA_XXXXXXXX/datafile/XXXX_tab_04.dbf, the average response time for single block reads was 184 milliseconds, and the total excess I/O wait was 4156 seconds.

Below would the mitigations we can follow to overcome this issue:

DB File Sequential Read wait event occurs when we are trying to access data using index and oracle is waiting for the read of index block from disk to buffer cache to complete.  A sequential read is a single-block read.Single block I/Os are usually the result of using indexes. Rarely, full table scan calls could get truncated to a single block call due to extent boundaries, or buffers already present in the buffer cache.Db file sequential read wait events may also appear when undo blocks are read from disk in order to provide a consistent get(rarely).

To determine the actual object being waited can be checked by the p1, p2, p3 info in v$session_wait .  A sequential read is usually a single-block read, although it is possible to see sequential reads for more than one block (See P3). This wait may also be seen for reads from datafile headers (P2 indicates a file header read) ,where p1,p2 and p3 gives the the absolute file number ,the block being read ,and  the number of blocks (i.e, P3 should be 1) respectively. 

Block reads are fairly inevitable so the aim should be to minimise un-necessary IO. This is best achieved by good application design and efficient execution plans. Changes to execution plans can yield orders of magnitude changes in performance.Hence to reduce this wait event follow the below points .

1.) Tune Oracle - tuning SQL statements to reduce unnecessary I/O request is the only guaranteed way to reduce "db file sequential read" wait time.
2.) Tune Physical Devices - Distribute(stripe) the data on diferent disk to reduce the i/o . Logical distribution is useless. "Physical" I/O performance is only governed by "independency of devices".
3.) Faster Disk - Buy the faster disk to reduce the unnecessary I/O request .
4.) Increase db_block_buffers - A larger buffer cache can (not will, "might") help.

Hope This helps :)