Log switches :
==============
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
;
set lines 120;
set pages 999;
SELECT
to_char(first_time,'YYYY-MON-DD') 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
GROUP by
to_char(first_time,'YYYY-MON-DD');
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,NULL)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,NULL)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,NULL)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,NULL)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,NULL)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,NULL)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,NULL)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,NULL)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,NULL)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,NULL)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,NULL)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,NULL)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,NULL)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,NULL)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,NULL)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,NULL)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,NULL)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,NULL)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,NULL)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,NULL)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,NULL)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,NULL)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,NULL)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,NULL)),'99') "23",
count(to_char(first_time,'HH24')) "ToT"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD')
order by to_date(to_char(first_time,'YYYY-MON-DD'),'YYYY-MON-DD')
Sessions generating the high redo/chnages :
=====================================
select sid, value
from v$sesstat s, v$statname n
where n.statistic# = s.statistic#
and n.name = 'redo size'
order by value desc;
Current redo log file : How full it is
=================================
SELECT le.leseq "Current log sequence No",
100*cp.cpodr_bno/le.lesiz "Percent Full",
cp.cpodr_bno "Current Block No",
le.lesiz "Size of Log in Blocks"
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8 ;