Acme Sprockets

During a recent install of Oracle Grid Infrastructure 11.2.0.2 on Linux x64, the installer found the LUNs as candidate disks, then later erroneously reported that the storage devices were not available on all nodes while running the final checks before the actual installation/configuration. Here are the steps I followed to ensure that Oracle/ASMLIB could in fact access the devices from all nodes. This allowed me to (mostly) confidently ignore the installer "error" and proceed with the installation which then succeeded.

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.

Sometimes the simple way really is the best...

Recently I looked at a custom SQL script running during a client's batch processing. They needed to update one column on approximately 300,000 rows out of 9,000,000 on a base table. The new values were coming from another staging table that had the same primary key as the base table. Best-case, the SQL was running for several minutes; worst case (like on the Saturday I took a quick look), it was over 30 minutes. The extra long run was due to bad stats, but the SQL was an overly complex update statement with subqueries, joins, update by rowid, etc -- too complex and definitely inefficient. Setting aside the question about why a staging table at all, I suggested using an update to an Oracle inline, key preserved, view.

By using this approach, what used to run in 5-30 minutes on a large AIX server and SAN became less than a minute on my much smaller two-core Linux test server running 11gR2 (with the database on a single SATA drive).

A few Oracle-related books I recommend:

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...