Skip to content

Log File Sync Analysis (health4.sql)

What This Script Does

Specialized health check that focuses on ‘log file sync’ wait events, which are critical for understanding commit performance and redo log I/O efficiency.

The Script

rem  health4.sql
rem
ttitle 'Log File Sync 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 'logfilesync_' || 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='log file sync%%'
rem
rem -- @dhsysev (System event analysis script)
rem
spool off
/

Focus Areas

Log file sync wait analysis
Commit performance monitoring
Redo log I/O efficiency
Transaction throughput assessment

health - Primary health check suite
health3 - DB file sequential read analysis