Acme Sprockets

The time it takes to archive redo logs in an Oracle database can be a performance issue for a variety of reasons (not discussed here).

The start and finish time of each archive operation is recorded in the Oracle alert log. Unfortunately, this isn't an easy format to analyze directly. By feeding the Oracle alert log (or sections of it) to the Ruby script in the attached zip file below, a set of tab-delimited data (start time, end time, minutes, log sequence#) for each log archived is produced. 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 (archive_times.rb), do something like the following:

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

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

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

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

Note: This was tested and used against an Oracle 9i alert log. I have not validated (yet) that it works correctly on 10g or 11g.

Enjoy!