Archiving headroom

Summary 

Compares the time taken for each archival with the time to the log switch that reused the file and reports the least headroom. If the headroom has been less than 50%, then you should either tune archival performance, or you should consider increasing the number of log files, or both.

select substr(to_char(100 * headroom, '999999999.0'), 2) || '%' min_headroom,
round(arch_time * 24 * 60, 2) archival_minutes,
round(arch_window * 24 * 60, 2) archival_window
from ( select b.next_time - a.next_time arch_window,
a.completion_time - a.next_time arch_time,
(b.next_time - a.next_time) / (a.completion_time - a.next_time) - 1 headroom
from sys.v_$thread t, sys.v_$archived_log a, sys.v_$archived_log b
where a.completion_time > a.next_time and
b.sequence# = a.sequence# + t.groups - 1
order by 3)
where rownum = 1;