Acme Sprockets

A few Oracle-related books I recommend:

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.

Here are the results of a quick experiment to determine the relative costs in Oracle of:

• Regular vs. Direct-path (/*+append*/) inserts into
• Regular (heap-organized) vs. Temporary table
• In NOARCHIVELOG vs. ARCHIVELOG modes,
• With the regular table marked LOGGING vs. NOLOGGING

Here are a few simple but useful SQL scripts that I've developed and accumulated over the years of working on and around Oracle databases. More to come...