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 :