Acme Sprockets

My clients are typically concerned about batch process run times. As their business has grown, they want to process more and more data. Or, the users are now more savvy and want that data sooner. Failing to make SLAs is bad, bad, bad.

Generally, a batch stream is made up of a number of serially run jobs. And each job may have have a number of serial activities. But which ones to focus on?

Experience has shown that the largest opportunities are generally found by identifying the largest percentage contributors to the overall processing time. After all, making a 10% improvement in a 20-minute job is only 2 minutes, while a 10% gain in a three-hour job saves 18 minutes.

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.

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.