Acme Sprockets

Original version Sun, 2007-11-04 17:21 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.

Randall has once again crafted a superb data visualization. His Radiation Dose Chart simply and effectively conveys a lot of information that is unfortunately very useful. I highly recommend that you take a look for the content, and for a great example of how to concisely and clearly represent comparative data.

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.

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.