Problem solve Get help with specific problems with your technologies, process and projects.

Monitoring daily archive log switches: Number, size and frequency

This script gives you the daily number of redo log switches, archive log disk space needed, and average log switches/hour.

Putting a database in archive log mode has a drawback: disk space usage. Moreover, the redolog switch is a costly...

action that causes additional disk and CPU overhead. The following script gives you in one shot the daily number of redolog switchse, archive log disk space needed, and average number of log switches per hour.

In my experience, an average number of log switches per hour greater than 20 causes non-negligable performance issues. This can be solved, for example, by increasing the redologs size. Note that the number of redo log switch entries in the v$loghist view is set by the parameter MAXLOGHISTORY. It can be changed by recreating the control files.

SELECT trunc(first_time) DAY,
   count(*) NB_SWITCHS,
   trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
   to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size

Here is an output example:

DAY        NB_SWITCHS TOTAL_SIZE_KB AVG_SWITCHS_PER_HOUR
---------- ---------- ------------- --------------------
09/04/2002 138        694140        5.8
10/04/2002 439        2208170       18.3
11/04/2002 11         55330         .5
12/04/2002 9          45270         .4
13/04/2002 7          35210         .3

Reader Feedback

Robert O. writes: This was a nice script, but it doesn't work on Oracle 7. Please see the differences:

Oracle 7 syntax

SELECT trunc(to_date(first_time,'mm/dd/yy hh24:mi:ss')) DAY,
     count(*) NB_SWITCHS,
     trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
     to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,(select avg(bytes) log_size from v$log)
GROUP BY trunc(to_date(first_time,'mm/dd/yy hh24:mi:ss')),log_size
/

Oracle 8 syntax

SELECT trunc(first_time) DAY,
     count(*) NB_SWITCHS,
     trunc(count(*)*log_size/1024) TOTAL_SIZE_KB,
     to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,(select avg(bytes) log_size from v$log)
GROUP BY trunc(first_time),log_size
/

The problem is that in Oracle 7, the field "first_time" is VARCHAR2, in Oracle 8 it is DATE. Minor, but may help others.

Alex V. writes: The script is good, but the MAXLOGHISTORY parameter makes sense only with OPS. See this quote from SQL Reference: "Note: This parameter is useful only if you are using Oracle with the Parallel Server option in parallel mode, and archivelog mode enabled."

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close