Folgende SQL-Statements liefern eine gute Übersicht das zeitliche Verhalten der Oracle Redo-Log-Switches.
— Berechnet die Zeit zwischen redo log switches
col start_time format a18
col end_time format a18
select b.recid,
to_char(b.first_time,’dd-mon-yy hh:mi:ss‘) start_time, a.recid,
to_char(a.first_time,’dd-mon-yy hh:mi:ss‘) end_time,
round(((a.first_time – b.first_time)*24)*60,2) minutes
from v$log_history a, v$log_history b
where a.recid = b.recid + 1
order by a.first_time asc;
—
— liefert als Ergebnis
—
RECID START_TIME RECID END_TIME MINUTES
———- —————— ———- —————— ———-
1 18-mar-10 05:43:14 2 18-mar-10 05:46:03 2.82
2 18-mar-10 05:46:03 3 18-mar-10 05:47:03 1
3 18-mar-10 05:47:03 4 18-mar-10 05:47:59 .93
4 18-mar-10 05:47:59 5 18-mar-10 05:48:40 .68
5 18-mar-10 05:48:40 6 19-mar-10 08:46:19 897.65
6 19-mar-10 08:46:19 7 24-mar-10 12:49:03 7442.73
7 24-mar-10 12:49:03 8 24-mar-10 01:23:49 34.77
—
— Übersicht Redo-Log switches pro Tage/Stunde
—
set lines 200;
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‘)
ORDER by day;
—
— Ergebnis
—
DAY 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
———– — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
2010-AUG-01 0 0 0 9 0 0 3 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1 2
2010-AUG-02 0 0 0 0 0 0 0 1 0 0 0 1 0 1 0 0 0 0 0 0 0 1 0 1
2010-AUG-03 0 0 0 6 0 1 0 0 0 1 0 0 0 0 0 0 1 0 0 0 0 0 1 0
2010-AUG-04 0 2 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 1 0 0
2010-AUG-05 0 1 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 1 0
2010-AUG-06 0 0 0 0 0 1 0 0 0 0 1 0 0 0 0 0 0 2 0 0 0 0 1 0
2010-AUG-07 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 7 0 0 0 0 0 0 0
2010-AUG-08 1 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 1 0 2
2010-AUG-09 0 0 0 0 0 0 0 0 8 0 1 0 0 0 0 0 0 1 0 0 0 1 1 0
2010-AUG-10 0 0 0 0 0 0 1 0 4 0 1 0 0 0 0 0 5 0 0 0 0 1 0 2
2010-AUG-11 0 0 0 5 0 1 0 1 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
2010-AUG-12 0 0 0 0 0 1 0 0 7 0 0 0 0 0 0 0 0 0 1 0 0 0 0 1
2010-AUG-13 0 0 0 0 0 0 3 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 1 0