The below query comes handy to understand the archivelog generation of an Oracle database on an hourly /daily basis, per thread – in case of RAC databases.
Archivelog generation on a daily basis:
set pages 1000
select trunc(COMPLETION_TIME,’DD’) Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives_Generated from v$archived_log
group by trunc(COMPLETION_TIME,’DD’),thread# order by 1;
Archive log generation on an hourly basis:
set pages 1000
select [...]