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