It is difficult to be certain because you have not attached any statistics, however I would guess that this reflects a failure to separate the log files onto separate disks. LGWR normally performs lots of very small writes. If the log files are not on dedicated disks, then the response time for those writes will be relatively poor and LGWR can easily become a bottleneck. However, there is normally a burst of intensive redo allocation after a log switch (because redo allocation is disabled during a log switch and a backlog develops). Because of this LGWR performs much larger writes than normal just after a log switch, and is thus less sensitive to I/O contention for a while. By using very small log files you inadvertently prevented much of the I/O contention that was making LGWR perform poorly, and although you wasted more time on log switches overall performance was much better.

In general, for data loads your log files should be on dedicated disks. They don't need to be stripped. They should be on raw devices. You should use a large log buffer and commit as infrequently as possible. If possible you should also drop any indexes and disable any triggers that would increase redo generation for the duration of the load.

We have developed some PL/SQL packages to perform initial data load into our Oracle 8.0.5 database (HPUX 11). The performance was not too impressive, before I asked the DBA to perform any tuning, 225,000 records were processed per hour (each record has about 10 inserts and 2 selects at most).

After the DBA took a look, he decided to increase the redo log group size from 32M to 50M. However, he made a mistake to change the redo log group size from 32M to 5M instead. However, the performance had been dramatically improved from 225,000 records to almost 500,000 records per hour. The only drawback was the log switching happened too frequent (about 10 seconds between log switch), also the control files size were also increased.

The DBA later tried to increase the redo log group size to 100M because he didn't agree to have such small redo log group size and the frequent log switching. As before, once the redo log group size was increased to 100M, the performance degraded instantly.

My question is how come decreasing the redo log group size from 32M to 5M improve the performance? Is it anything we should do in this regard?