Query to check if your Oracle redo changing is too frequently

Everywhere is easy to find some paper regarding the frequency that redo should be filled up.

Personally I like the 1/2 hour changing, it doesn´t put a log of pressure at checkpoint, giving us a good balance between recovery time and wait of redo sync.

The query bellow will show if any changing during the working time (7AM to 7PM) is happening more often then twice an hour.

(It will check just the last ten days)

select to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#) sequencia,
to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) time ,
to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) time2
from v$archived_log t1,v$archived_log t2
where
to_number(to_char(t1.FIRST_TIME,’ddmm’)||t1.thread#||t1.sequence#)=to_number(to_char(t2.FIRST_TIME,’ddmm’)||t2.thread#||t2.sequence#-1) and
(to_number(to_char(t2.FIRST_TIME,’hh24′))*60+ to_number(to_char(t2.FIRST_TIME,’mi’)) )
–
(to_number(to_char(t1.FIRST_TIME,’hh24′))*60+ to_number(to_char(t1.FIRST_TIME,’mi’)) )
<30  –redo changing more than 2 times per hour
/*  begining of time validation,
aiming to avoid batch operation */
and to_number(to_char(t1.FIRST_TIME,’hh24′))  between 7 and 19 and
to_number(to_char(t2.FIRST_TIME,’hh24′))  between 7 and 19
— end of time validation
and t1.first_time>sysdate-10
and t2.first_time>sysdate-10
;