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:
select time,
snap_id,
round(time_delta/1e3/nullif(waits_delta,0), 1) avg_wait_ms,
waits_delta num_of_waits,
round(time_delta/1e6) total_seconds
from
(
select sn.snap_id,
sn.begin_interval_time time,
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,
dba_hist_snapshot sn
where e.snap_id = sn.snap_id
AND e.event_name = 'log file sync'
) ev
WHERE ev.time_delta > 0
order by time desc
Oracle Diagnostician 