Gather the statistic of Archive log generation by hour

Use the following SQL to gather the statistic of archive log generation by hour, this information could help to you to monitor the database instance which hour is most frequency change the data in a day.


SQL> select
  2  to_char( a.COMPLETION_TIME, 'dd-mm-yyyy')as generation_date,
  3  to_char( a.COMPLETION_TIME, 'hh24')||':00' as start_time,
  4   round(sum(a.BLOCKS * a.BLOCK_SIZE)/1048576, 0) as Total_size_in_MB,
  5   count(a.recid) as no_of_archived_log
  6   from v$archived_log a
  7   group by to_char( a.COMPLETION_TIME, 'dd-mm-yyyy'),  to_char( a.COMPLETION_TIME, 'hh24')
  8   order by 1, 2, 3 desc, 4 desc;

GENERATION START TOTAL_SIZE_IN_MB NO_OF_ARCHIVED_LOG
---------- ----- ---------------- ------------------
01-07-2016 04:00               66                  2
01-07-2016 09:00               64                  2
01-07-2016 14:00               64                  2
01-07-2016 19:00               64                  2
01-07-2016 22:00               96                  2
02-07-2016 02:00               64                  2
02-07-2016 06:00               81                  2
02-07-2016 09:00               64                  2
02-07-2016 11:00               66                  2
02-07-2016 14:00               74                  2
02-07-2016 18:00               68                  2