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 trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log group by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Sample output:
HOUR THREAD# MB ARCHIVES ------------------- ---------- ---------- ---------- 2009 08 20 12:00:00 1 31268 339 2009 08 20 13:00:00 1 4994 55 2009 08 20 14:00:00 1 4412 48 2009 08 20 15:00:00 1 4805 52 2009 08 20 16:00:00 1 3364 37 2009 08 20 17:00:00 1 22 1 2009 08 20 21:00:00 1 9 1
Also, the following script is useful to find the archivelog switches on an hourly basis that happened in the past one week, I got this from http://kubilaykara.blogspot.com/2008/02/redo-log-generation.html and is quite an useful one.
SELECT to_date(first_time) DAY, to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00", to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01", to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02", to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03", to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04", to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05", to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06", to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07", to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08", to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09", to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10", to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11", to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12", to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13", to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14", to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15", to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16", to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17", to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18", to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19", to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20", to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21", to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22", to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23" from v$log_history where to_date(first_time) > sysdate -8 GROUP by to_char(first_time,'YYYY-MON-DD'), to_date(first_time) order by to_date(first_time) /
Sample output:
DAY 00 01 02 03 04 05 06 09 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 --------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- - 01-NOV-09 0 1 1 0 1 0 1 0 1 4 1 1 0 1 0 1 1 0 1 1 1 1 1 1 02-NOV-09 0 1 1 1 1 0 1 1 1 1 0 1 1 7 1 1 1 1 1 2 1 1 1 1 03-NOV-09 1 2 2 1 1 1 1 1 1 1 1 2 1 1 1 1 1 2 1 1 2 1 1 2 04-NOV-09 1 1 8 1 7 2 1 1 1 2 1 1 2 1 2 1 2 1 2 1 2 1 2 2 05-NOV-09 2 1 2 1 2 2 1 2 1 2 2 1 2 2 1 2 2 2 1 2 2 2 2 2 06-NOV-09 2 1 2 2 2 2 1 2 2 2 2 2 1 2 2 1 0 2 0 0 0 0 1 0 09-NOV-09 0 0 1 7 0 0 1 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0
