Home > oracle, Scripts, SQL > A script to check the frequency of Oracle log switches

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

Advertisements
Categories: oracle, Scripts, SQL Tags: , ,
  1. John Ospino
    May 11, 2010 at 2:44 pm

    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
    /

    • Testy
      January 31, 2014 at 11:05 am

      Very helpful !!

  2. May 12, 2010 at 6:17 am

    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.

  3. Ram Nanubalu
    July 11, 2012 at 11:47 am

    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

  1. June 29, 2012 at 6:15 am
  2. February 17, 2016 at 5:01 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: