If you see high db file sequential read waits, it usually means sessions are doing single-block physical reads (typically index lookups, nested loop joins, row-by-row access, or reading blocks that are not in the buffer cache).
The objective is:
- Identify which SQLs are generating the reads.
- Identify which users/sessions are running them.
- Verify whether the issue is SQL, indexing, buffer cache, or storage latency.
Step 1: Check Current Sessions Waiting on db file sequential read
SELECT
s.sid,
s.serial#,
s.username,
s.machine,
s.program,
s.sql_id,
s.event,
s.seconds_in_wait,
s.state,
sw.p1 "FILE#",
sw.p2 "BLOCK#"
FROM v$session s
JOIN v$session_wait sw
ON s.sid = sw.sid
WHERE s.event = 'db file sequential read'
ORDER BY s.seconds_in_wait DESC;
This tells you:
- Who is waiting
- Which SQL_ID is involved
- Which file/block Oracle is reading
Step 2: Find SQLs Causing Most Physical Reads
Current cache:
SELECT *
FROM
(
SELECT
sql_id,
parsing_schema_name,
executions,
buffer_gets,
disk_reads,
ROUND(disk_reads/NULLIF(executions,0),2) reads_per_exec,
SUBSTR(sql_text,1,100) sql_text
FROM v$sql
WHERE disk_reads > 0
ORDER BY disk_reads DESC
)
WHERE ROWNUM <= 20;
Focus on:
disk_reads
reads_per_exec
High values indicate likely contributors.
Step 3: Identify Top Users Generating Physical Reads
SELECT
s.username,
SUM(q.disk_reads) disk_reads,
COUNT(*) sql_count
FROM v$session s
JOIN v$sql q
ON s.sql_id = q.sql_id
WHERE s.username IS NOT NULL
GROUP BY s.username
ORDER BY disk_reads DESC;
Step 4: Check Historical SQLs from AWR
Top SQLs causing reads:
SELECT *
FROM
(
SELECT
sql_id,
SUM(disk_reads_delta) disk_reads,
SUM(executions_delta) executions,
ROUND(
SUM(disk_reads_delta) /
NULLIF(SUM(executions_delta),0),
2
) reads_per_exec
FROM dba_hist_sqlstat
GROUP BY sql_id
ORDER BY SUM(disk_reads_delta) DESC
)
WHERE ROWNUM <= 20;
Get SQL text:
SELECT sql_id,
sql_text
FROM dba_hist_sqltext
WHERE sql_id = '&SQL_ID';
Step 5: Find SQLs Waiting on db file sequential read
Historical AWR:
SELECT
ash.sql_id,
ash.user_id,
COUNT(*) samples
FROM dba_hist_active_sess_history ash
WHERE ash.event = 'db file sequential read'
GROUP BY ash.sql_id, ash.user_id
ORDER BY samples DESC;
Resolve user names:
SELECT user_id, username
FROM dba_users;
Step 6: Current ASH Analysis
If licensed for Diagnostics Pack:
SELECT
sql_id,
session_id,
user_id,
COUNT(*) samples
FROM v$active_session_history
WHERE event='db file sequential read'
GROUP BY sql_id, session_id, user_id
ORDER BY samples DESC;
Top SQL causing waits appears at the top.
Step 7: Check Storage Latency
Sometimes the SQL is fine but storage is slow.
SELECT
file_no,
filetype_name,
small_read_reqs,
ROUND(
small_read_servicetime /
NULLIF(small_read_reqs,0),
2
) avg_read_ms
FROM v$iostat_file
ORDER BY avg_read_ms DESC;
Guidelines:
< 1 ms Excellent
1-5 ms Good
5-10 ms Acceptable
>10 ms Investigate
>20 ms Critical
Step 8: Check Which Objects Are Being Read
From ASH:
SELECT
current_obj#,
COUNT(*) samples
FROM v$active_session_history
WHERE event='db file sequential read'
GROUP BY current_obj#
ORDER BY samples DESC;
Object details:
SELECT
owner,
object_name,
object_type
FROM dba_objects
WHERE object_id=&OBJECT_ID;
This often identifies a hot table or index.
Step 9: Check Execution Plan
For the top SQL:
SELECT *
FROM TABLE(
DBMS_XPLAN.DISPLAY_CURSOR(
'&SQL_ID',
NULL,
'ALLSTATS LAST'
));
Look for:
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN
INDEX UNIQUE SCAN
NESTED LOOP
These are the most common sources of db file sequential read.
Step 10: Determine the Root Cause
Case 1: High disk reads + poor index
Check missing indexes:
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID'));
Symptoms:
- Millions of rows visited
- Many index lookups
Action:
- Create/modify index
- Review predicates
Case 2: High Reads per Execution
disk_reads/executions
Example:
Disk Reads : 10,000,000
Executions : 50
Reads/Exec : 200,000
Action:
- Tune SQL
- Reduce rows accessed
Case 3: Good SQL but Slow Read Time
Check:
SELECT event,
ROUND(time_waited_micro/total_waits/1000,2) avg_ms
FROM v$system_event
WHERE event='db file sequential read';
If:
avg_ms > 10
Investigate:
- Storage
- ASM
- SAN
- Multipathing
- Exadata Cells
One Query I Use First as a DBA
SELECT
ash.sql_id,
u.username,
COUNT(*) ash_samples
FROM dba_hist_active_sess_history ash
LEFT JOIN dba_users u
ON ash.user_id = u.user_id
WHERE ash.event = 'db file sequential read'
GROUP BY ash.sql_id, u.username
ORDER BY ash_samples DESC
FETCH FIRST 20 ROWS ONLY;
Then:
SELECT sql_text
FROM dba_hist_sqltext
WHERE sql_id='&SQL_ID';
This quickly identifies which SQL and which user are responsible for the majority of db file sequential read waits.
Stale Statistics
SELECT owner,
table_name,
stale_stats
FROM dba_tab_statistics
WHERE stale_stats='YES';
Poor Buffer Cache Hit Ratio
SELECT name, value
FROM v$sysstat
WHERE name IN (
'physical reads',
'db block gets',
'consistent gets'
);
SELECT
ROUND((1 - (
physical_reads /
(db_block_gets + consistent_gets)
))*100,2) hit_ratio
FROM v$buffer_pool_statistics;
Hot Objects
Find objects frequently involved:
SELECT
o.owner,
o.object_name,
COUNT(*) samples
FROM v$active_session_history ash,
dba_objects o
WHERE ash.current_obj# = o.object_id
AND ash.event='db file sequential read'
GROUP BY o.owner,o.object_name
ORDER BY samples DESC;
Slow Storage
Query:
SELECT
file_no,
filetype_name,
ROUND(
small_read_servicetime/
NULLIF(small_read_reqs,0),
2
) avg_read_ms
FROM v$iostat_file
ORDER BY avg_read_ms DESC;
Decide:
- Missing index → Create/tune index
- Bad plan → Gather stats / SQL tuning
- High reads per execution → SQL rewrite
- High latency → Storage team
- Frequent rereads → Increase cache
No comments:
Post a Comment