DB File Sequential Read - Wait Event in Oracle

 Part:1

The db file sequential read event signifies that the user process is reading data into the SGA buffer cache and is waiting for a physical I/O call to return. 

It corresponds to a single-block read. Single block I/Os are usually the result of using indexes.

• Top reported wait
• Single block read
• block accessed via
•  index 
•  rowid
•  rollback 

Example :select * from emp where empno=99;

where there is an index on emp(empno)

algorythm
search buffer cache for block by rowid, 
if fail, read block off  disk via file# and block#

Note: "sequential" means a sequence as in rowid

Part:2

👉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 minimize 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 .

Part:3

db file sequential read - A single-block read (i.e., index fetch by ROWID) 

The db file sequential read event signifies that the user process is reading the buffers into SGA(Database buffer cache) and is waiting for physical I/O to return or complete. It reads the blocks in to contiguous memory space and these single block reads or I/Os uses 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.

For reduce the db file sequential read wait event various tuning aspects will need to lookup like tuning the SQL statements, checking indexes selectivity and chained rows etc.

Check Session causing DB file Sequential Read wait in Oracle

SELECT sid, total_waits, time_waited FROM v$session_event
WHERE event='db file sequential read' AND total_waits>0 ORDER BY 3,2;

Some tips to reduce the DB file Sequential Read wait Event in Oracle

1. Check the table has analyzed recently otherwise gather stats

-- Check Schema analyzed dates
select owner,min(last_Analyzed), max(last_analyzed) from dbA_tables where owner = 'HR' group by owner order by 1;


--Check the table analyzed dates
SELECT table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') "LASTANALYZED" FROM DBA_TABLES WHERE owner='HR' and table_name = 'DEPT';

2. Tune the SQL Query comes in TOP section of query in AWR like SQL ordered by Physical Reads or SQL ordered by User I/O Wait Time or gets per execution. Run SQL tuning advisory or try to tune the SQL so that it will use less bytes in execution plan.

3. Try with proper setting of parameter OPTIMIZER_MODE. Fetch the Execution plan at session level and check consistent gets is reduced in execution plan for top queries by changing this parameter.

SQL> show parameter optimizer_mode
NAME            TYPE    VALUE
--------------- ------  --------
optimizer_mode  string  ALL_ROWS

--Set at session level and check execution plan of top SQL queries.
Alter session set optimizer_mode=all_rows;
Alter Session set optimizer_mode=first_rows;
alter session set optimizer_mode=first_rows_100;
alter session set optimizer_mode=first_rows_n;
n - no of rows
4. Sometime tables have many index which cause the execution plan to choose the wrong index which cause unnecessary I/O. SQL queries may be using unselective indexes causing unnecessary I/O activity. We need to check index Selectivity on basis of which column used in SQL Query. Use hint to provide better selectivity index to the SQL query.
Index selectivity:
Selectivity of an index is a measure of the index's usefulness in reducing the I/O required by queries against the index's table.
Index selectivity is the ratio of number of distinct values in the indexed columns(s) to the number of records in the table. 

Note: 
The best possible selectivity is 1.00 or 100% ( Unique indexes on non-nullable columns always have a selectivity of 100%).

Example: To calculate index selectivity:
If a table has 10000 rows, and an indexed column on that table has 8000 distinct values, then the selectivity of that index is 8000/10000 
or 0.80 or 80%

--Create index on Firstname of table employees;
Select count(distinct firstname) from EMPLOYEES;
8000

--Total no of rows in Employees table
Select count(*) from EMPLOYEES;
10000

Index Selectivity = 8000/10000 = 0.80 or 80%
5. We can do the partitioning of table for further reduce the I/O for the data.

Part:4
When performance problems happen it's important to find out where Oracle/Application is spending most of the time. One of the tools that you can use for diagnosing the problem is script that is querying V$SESSION_WAIT view. V$SESSION_WAIT displays the resources or events for which active sessions are waiting.

I've made script that you can use for displaying wait events for which active sessions are currently waiting
col SID for 99999
col STATE for a9
col EVENT for a48
col P1_TXT for a20
col P2_TXT for a20
col p3_TXT for a25
col SECONDS_IN_WAIT for 999999999
col WAIT_CLASS for a15

SELECT  sw.sid sid,
CASE
WHEN sw.state != 'WAITING'
THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE
WHEN sw.state != 'WAITING'
THEN 'On CPU / runqueue'
ELSE event
END AS event                                            ,
sw.seconds_in_wait                                         ,
DECODE(sw.p1text,NULL,'n/a',sw.p1text ||'=' ||sw.p1) p1_txt,
DECODE(sw.p2text,NULL,'n/a',sw.p2text ||'=' ||sw.p2) p2_txt,
DECODE(sw.p3text,NULL,'n/a',sw.p3text ||'=' ||sw.p3) p3_txt,
en.wait_class
FROM    v$session_wait sw,
v$event_name en
WHERE   sw.event = en.name
AND sid     IN
(SELECT sid
FROM    v$session
)
ORDER BY seconds_in_wait DESC;



RESULT:

SID STATE    EVENT   SECONDS_IN_WAIT P1_TXT     P2_TXT    P3_TXT      WAIT_CLASS
------ --------- ------------------------------------------------ --------------
373 WAITING db file sequential read 4 file#=4 block#=14943 blocks=1 User I/O
375 WAITING db file sequential read 3 file#=4 block#=827973 blocks=1 User I/O

The most significant wait event in this specified situation is db file sequential read so I removed other events from the result.

Db file sequential read wait event happens when process has issued an I/O request to read one block from a data file into the buffer cache, and is waiting for the operation to complete. 
This typically happens during an index lookup or a fetch from a table by ROWID when the required data block is not already in memory. Do not be misled by the confusing name of this wait event!

A sequential read is usually single block read, and you can easily find blocks being waited on using information from script above - querying V$SESSION_WAITS.

You can use this script below for searching segments by file# and block#.
column owner format a15
column tablespace_name format a35
column file_id format 999999999
column segment_name format a35
column block_id format 9999999999

ACCEPT FILEID PROMPT "[Enter File#]: "
ACCEPT BLCKNO PROMPT "[Enter Block#]: "

select owner,
tablespace_name,
file_id,
segment_name,
block_id
from dba_extents
where file_id=&&FILEID
and block_id between &&BLCKNO and &&BLCKNO+blocks;

RESULT:

OWNER TABLESPACE_NAME FILE_ID SEGMENT_NAME BLOCK_ID
--------------- ----------------------------------- 
SRVCEA    USERS       4       DEVICES      14945
SRVCEA    USERS       4       LOGS         14985


Here you can see what were the objects being waited for.

There are various operations that you can do to reduce "db file sequential read" wait events. 
You cannot avoid block reads, but you can minimize un-necessary IO. Usually the problem is application design and if that is the case you should concentrate on tweaking application if possible. 
Scripts above can help you in finding better solutions.
Besides that larger buffer cache might help or reorganising data.

Part:5
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 result 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 amount of blocks being visited
· Make sure optimizer statistics are up to date
· Relocate ‘hot’ datafiles
· Consider the usage of multiple buffer 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?
· 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 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 favour the nested loops operation and choose an index access path over a full table scan.