Archiving headroom
                                                 Last update (2008-02-18 16:52:17)
                                                                                                                   Date added (2008-02-13 13:53:49)

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;

    
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search