Log switches per day/hour

set lines 120;
set pages 999;

alter session set nls_date_format = 'DD-MM-YY HH24:MI:SS';

spool dba_logswitch.lst

compute sum label "Total" of "Tot" on report

break on report

select substr(first_time,1,5) day,
   to_char(sum(decode(substr(first_time,10,2),'00',1,0)),'99') "00",
   to_char(sum(decode(substr(first_time,10,2),'01',1,0)),'99') "01",
   to_char(sum(decode(substr(first_time,10,2),'02',1,0)),'99') "02",
   to_char(sum(decode(substr(first_time,10,2),'03',1,0)),'99') "03",
   to_char(sum(decode(substr(first_time,10,2),'04',1,0)),'99') "04",
   to_char(sum(decode(substr(first_time,10,2),'05',1,0)),'99') "05",
   to_char(sum(decode(substr(first_time,10,2),'06',1,0)),'99') "06",
   to_char(sum(decode(substr(first_time,10,2),'07',1,0)),'99') "07",
   to_char(sum(decode(substr(first_time,10,2),'08',1,0)),'99') "08",
   to_char(sum(decode(substr(first_time,10,2),'09',1,0)),'99') "09",
   to_char(sum(decode(substr(first_time,10,2),'10',1,0)),'99') "10",
   to_char(sum(decode(substr(first_time,10,2),'11',1,0)),'99') "11",
   to_char(sum(decode(substr(first_time,10,2),'12',1,0)),'99') "12",
   to_char(sum(decode(substr(first_time,10,2),'13',1,0)),'99') "13",
   to_char(sum(decode(substr(first_time,10,2),'14',1,0)),'99') "14",
   to_char(sum(decode(substr(first_time,10,2),'15',1,0)),'99') "15",
   to_char(sum(decode(substr(first_time,10,2),'16',1,0)),'99') "16",
   to_char(sum(decode(substr(first_time,10,2),'17',1,0)),'99') "17",
   to_char(sum(decode(substr(first_time,10,2),'18',1,0)),'99') "18",
   to_char(sum(decode(substr(first_time,10,2),'19',1,0)),'99') "19",
   to_char(sum(decode(substr(first_time,10,2),'20',1,0)),'99') "20",
   to_char(sum(decode(substr(first_time,10,2),'21',1,0)),'99') "21",
   to_char(sum(decode(substr(first_time,10,2),'22',1,0)),'99') "22",
   to_char(sum(decode(substr(first_time,10,2),'23',1,0)),'99') "23",
   to_char(sum(decode(substr(first_time,10,2),'00',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'01',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'02',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'03',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'04',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'05',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'06',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'07',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'08',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'09',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'10',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'11',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'12',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'13',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'14',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'15',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'16',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'17',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'18',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'19',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'20',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'21',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'22',1,0)),'99') +
   to_char(sum(decode(substr(first_time,10,2),'23',1,0)),'99') "Tot"
from v$log_history
group by substr(first_time,1,5)
;

spool off

Reader Feedback

Rich S. writes: Here is a simpler solution that will allow you to view the amount of archived redo logs per day and hour:

SELECT DECODE(grouping(TO_CHAR(first_time,'DD')),1,'All
Days',TO_CHAR(first_time,'DD')) day, 
   DECODE(grouping(TO_CHAR(first_time,'HH24')),1,'All
Hours',TO_CHAR(first_time,'HH24')) hour, COUNT(*) cnt
FROM v$log_history
GROUP BY rollup(TO_CHAR(first_time,'DD'), TO_CHAR(first_time,'HH24'))

Or if you just want activity for the last 24 hours:

SELECT DECODE(grouping(TO_CHAR(first_time,'HH24')),1,'All
Hours',TO_CHAR(first_time,'HH24')) hour, COUNT(*) cnt
FROM v$log_history
WHERE first_time > (SYSDATE - 1)
GROUP BY rollup(TO_CHAR(first_time,'HH24'))

Rob L. writes: Thanks for the script, works very nice, especially to see peak log writing times. However, is there a limit to how many rows from v$log_history it can get. I have one instance that keeps returning an Oracle error:

SQL> @log_switch

Session altered.

ERROR:
ORA-01722: invalid number

The author responds: I did not run into this error when running this script on a database with 1018 records in v$log_history. I could reproduce this error by replacing the format '99' with '9', so I think that there is a period when you've had more than 100 switches in an hour. Change the '99' into '999' and I think you will be freed from this error.

For More Information

• Feedback: E-mail the editor with your thoughts about this tip.
• More tips: Hundreds of free Oracle tips and scripts.
• Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
• Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
• Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
• Best Web Links: Oracle tips, tutorials, and scripts from around the Web.