Log switches per day/hour
View the amount of archived redo logs per day and hour.
Here is a script that will allow you to view the amount of archived redo logs per day and hour:
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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.