Monitor Archive Logs Generated

Query on v$sysstat

Redo generated since instance started:

SELECT name, value
FROM v$sysstat
WHERE name like ‘%redo%’
order by name
/
redo blocks read for recovery 0
redo blocks written 6075463
redo buffer allocation retries 21
redo entries 11244851
redo log space requests 25
redo log space wait time 278
redo log switch interrupts 0
redo ordering marks 161393
redo size 2987873528
redo subscn max counts 0
redo synch time 5684
redo synch writes 96011
redo wastage 18833908
redo write time 43250
redo writer latching time 6
redo writes 72039

Daily Count and Size of Redo Log Space (Single Instance):

  • Number of log switches
  • Average log in MB generated per day
SELECT A.*,
 Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
 FROM
 (
 SELECT
 To_Char(First_Time,'YYYY-MM-DD') DAY,
 Count(1) Count#,
 Min(RECID) Min#,
 Max(RECID) Max#
 FROM
 v$log_history
 GROUP
 BY To_Char(First_Time,'YYYY-MM-DD')
 ORDER
 BY 1 DESC
 ) A,
 (
 SELECT
 Avg(BYTES) AVG#,
 Count(1) Count#,
 Max(BYTES) Max_Bytes,
 Min(BYTES) Min_Bytes
 FROM
 v$log
 ) B;

Total Redo Log Size:

select sum(value)/1048576 redo_MB
from sys.gv_$sysstat
where name = 'redo size';

 REDO_MB
-----------------------
1938.911716461181640625

 Find the number of archivelogs generated each hour of the day:

col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999

select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

Segments causing redo log  generaion:

SELECT to_char(begin_interval_time,'YY-MM-DD HH24') snap_time,
 dhso.object_name,
 sum(db_block_changes_delta) BLOCK_CHANGED
 FROM dba_hist_seg_stat dhss,
 dba_hist_seg_stat_obj dhso,
 dba_hist_snapshot dhs
 WHERE dhs.snap_id = dhss.snap_id
 AND dhs.instance_number = dhss.instance_number
 AND dhss.obj# = dhso.obj#
 AND dhss.dataobj# = dhso.dataobj#
 AND begin_interval_time BETWEEN to_date('12-02-12 08:00','YY-MM-DD HH24:MI')
 AND to_date('12-02-13 08:00','YY-MM-DD HH24:MI')
 GROUP BY to_char(begin_interval_time,'YY-MM-DD HH24'),
 dhso.object_name
 HAVING sum(db_block_changes_delta) > 0
ORDER BY sum(db_block_changes_delta) desc ;

Also Check:

  • SQL causing most redo log generation

Reference / Source Links :

Leave a Reply

Your email address will not be published. Required fields are marked *