How to tune the DB file sequential read wait event in AWR

What is sequential Read?
A sequential read is usually a single-block read typically used when reading indexes, although it is possible to see sequential reads for more than one block.

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.