A script to check the frequency of Oracle log switches
I was looking for a script that showed me the frequency of my redo log switching through a SQL query. Although I can use Grid Control to monitor my redo logs, and use his several advisors, I prefer to use SQLPlus when it’s possible to keep the control of what I’m doing.
What I found is a fancy script written by Donald K. Burleson, that can be found in the following link
http://www.remote-dba.cc/oracle_tips_log_switches.htm
The script, in resume will show the frequency of your redo logs by hour, but unfortunately the view v$log_history has changed since the time that this script was done (I’m using Oracle 10g), so the select fails with the following error:
group by substr(time,1,5)
*
ERROR at line 27:
ORA-00904: “TIME”: invalid identifier
So I tried to fix the script to adapt it to Oracle 10g, but being unable to make the conversion, I decided to make a new script.
This is the resultant script:
COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24′) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ASC;
And there is an output example:
DAY HOUR TOTAL
————— —- —–
2010-04-30 01 24
2010-04-30 02 1
2010-04-30 13 1
2010-04-30 22 1
2010-05-01 00 1
2010-05-01 01 23
2010-05-01 03 1
2010-05-01 22 1
2010-05-02 00 1
2010-05-02 01 23
2010-05-02 22 1
2010-05-03 00 1
2010-05-03 01 23
2010-05-03 11 1
2010-05-03 22 1
2010-05-04 00 1
2010-05-04 01 24
2010-05-04 02 1
2010-05-04 12 1
2010-05-04 14 1
2010-05-05 00 1
2010-05-05 01 24
2010-05-05 02 1
2010-05-05 12 1
2010-05-05 22 1
2010-05-06 00 1
2010-05-06 01 23
2010-05-06 02 1
2010-05-06 12 1
2010-05-06 16 1
2010-05-07 00 1
2010-05-07 01 24
2010-05-07 02 1
2010-05-07 12 1
2010-05-07 22 1
2010-05-08 01 24
2010-05-08 03 1
2010-05-09 00 1
2010-05-09 01 24
2010-05-10 00 1
2010-05-10 01 24
2010-05-10 12 1
2010-05-10 22 1
2010-05-11 01 24
2010-05-11 02 1
Please rebuild to be usefull in RAC environment.
Por favor, sería bonito verlo también para los logswitch en un RAC.
Here you have a good one with minutes between logswitch
Aquí va uno que además te da los minutos entre los logswitch:
select b.recid,
to_char(b.first_time,’dd-mon-yy hh24:mi:ss’) start_time, a.recid,
to_char(a.first_time,’dd-mon-yy hh24:mi:ss’) end_time,
round(((a.first_time-b.first_time)*25)*60,2) minutes
from v$log_history a,
v$log_history b
where a.recid = b.recid+1
and a.first_time between to_date(‘2010-04-14:00:00:00′,’yyyy-mm-dd:hh24:mi:ss’) and to_date(‘2010-04-15:00:00:00′,’yyyy-mm-dd:hh24:mi:ss’)
order by a.first_time asc
/
Very helpful !!
Thanks for your script John, it looks interesting. I’ll work with it and try to make a new and improved script with also gives useful information for RAC logswtich.
select to_char(first_time,’YYYY-MON-DD’) day,
to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’999′) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’999′) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’999′) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’999′) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’999′) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’999′) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’999′) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’999′) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’999′) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’999′) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’999′) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’999′) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’999′) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’999′) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’999′) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’999′) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’999′) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’999′) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’999′) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’999′) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’999′) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’999′) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’999′) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’999′) “23”
from v$log_history
group by to_char(first_time,’YYYY-MON-DD’);
Ram Nanubalu