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 1000select trunc(COMPLETION_TIME,'DD') Day, thread#, round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives_Generated from v$archived_loggroup by trunc(COMPLETION_TIME,'DD'),thread# order by 1;
Archive log generation on an hourly basis:
set pages 1000select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,count(*) Archives from v$archived_loggroup by trunc(COMPLETION_TIME,'HH'),thread# order by 1 ;
Sample output:
HOUR THREAD# MB ARCHIVES
------------------- ---------- ---------- ----------
2013 08 20 12:00:00 1 31268 339
2013 08 20 13:00:00 1 4994 55
2013 08 20 14:00:00 1 4412 48
2013 08 20 15:00:00 1 4805 52
2013 08 20 16:00:00 1 3364 37
2013 08 20 17:00:00 1 22 1
2013 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"fromv$log_historywhere to_date(first_time) > sysdate - 8 -- or else change it you want for more days to viewGROUP byto_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-13 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-13 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-13 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-13 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-13 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-13 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-13 0 0 1 7 0 0 1 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0 0 0
