Acme Sprockets

How often an Oracle database switches the online redo logs can have a large impact on database performance. Each log switch is a synchronization point in the database and also initiates a checkpoint operation whereby the buffer cache is flushed to the data files. Oracle tends to recommend log switches of not more than every 15 minutes during peak activity periods. Realistically, high transaction volume databases may switch more often, but I don't like to see anything more frequent than 5-10 minutes.

While the log switch intervals are available by SQL query of the V$LOGHIST or V$LOG_HISTORY views (depending on DB version), sometimes access to the database is not available. In those cases, one can obtain the information from the alert log. In a similar fashion to the way I extract archive log times from the alert log, I wrote a Ruby script to extract the log switch times to a simple set of tab-delimited data (start_time, end_time, minutes, sequence#) for each redo log used by the database. This output data is then easy to analyze with gnuplot (as shown below), or with a spreadsheet such as Open Office Spreadsheet or Excel.

Using gnuplot, I graphed start time vs. time to archive as an XY graph (using a logarithmic Y scale)
and got the following:

To use the extraction script (log_switch_times.rb), do something like the following:

$ ./log_switch_times.rb < alert_TEST.log > log_switch_times.txt

Or if you're on Windows, you'll need to invoke ruby directly so the pipes work. For example:

c:\tmp> ruby log_switch_times.rb < alert_TEST.log > log_switch_times.txt

Note: This has been tested and used against Oracle 9i and 10g alert logs. I have not validated (yet) that it works correctly on 11g.

Then, you can use the gnuplot with the script (log_switch_times.plt) to plot the data.

Both files are in the attached zip file.

Update 2007-11-27
The script works fine against an Oracle 10g alert log. Below is plot I generated after extracting the log switch times from a 10g database on which I've been doing some performance work. As you can see, I have been using it periodically….

Enjoy!