Log buffer space is a simple, yet frequently misunderstood wait event. The main reason for that is probably its name. It sounds as if it points immediately to the answer: if space in the log buffer is the issue, then increasing it surely should resolve it. Well, unfortunately even though log buffer space is simple, it’s not that simple.
Continue reading “Log buffer space”
Two most popular causes for log file sync waits are redo filesystem I/O and CPU (or CPU scheduling). I’d like to share an example of a third scenario.
A few days ago, I noticed a weird spike in log file sync average duration on a production 11.2 database:
round(time_delta/1e3/nullif(waits_delta,0), 1) avg_wait_ms,
e.total_waits - lag(e.total_waits) over (partition by e.event_name order by e.snap_id) waits_delta,
e.time_waited_micro - lag(e.time_waited_micro) OVER (PARTITION BY e.event_name ORDER BY e.snap_id) time_delta
from dba_hist_system_event e,
where e.snap_id = sn.snap_id
AND e.event_name = 'log file sync'
WHERE ev.time_delta > 0
order by time desc
Continue reading “Log file sync from neither I/O nor CPU”