Skip to content

DB File Sequential Read Analysis (health3.sql)

What This Script Does

Specialized health check that focuses on ‘db file sequential read’ wait events, which are typically associated with index-based access patterns and single-block I/O operations.

The Script

rem  health3.sql
rem
ttitle 'DB File Sequential Read Analysis'
rem
set pause off
set pagesize 200
set time on
rem
ALTER SESSION SET NLS_DATE_FORMAT = 'MON-DD-YYYY HH24:MI'
ALTER SESSION SET NLS_timestamp_FORMAT = 'MON-DD-YYYY HH24:MI'
rem
column v_logname print new_value v_logname format a50
rem
select 'dbfileseq_' || name || '_' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),6,3)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),10,2)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),1,4)  || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),13,2) || '-' ||
       substr(to_char(sysdate,'yyyy-MON-dd hh24:mi:ss'),16,2) ||
       '.txt'  v_logname
 from v$database
/
rem
spool &v_logname
rem
define start_dt=''
define end_dt=''
define instno=''
define nam='db file seq%'
rem
rem -- @dhsysev (System event analysis script)
rem
spool off
/

Focus Areas

Database file sequential read wait analysis
Single-block I/O performance
Index access efficiency
Storage subsystem performance for random I/O

health - Primary health check suite
health4 - Log file sync analysis