Re: Poor performance during log switches

If you are filling 3 - 20MB logs per hour this would be 1MB per minute. Typically this would be a low rate of updates assuming a fairly robust I/O subsystem. If you are generating log 20MB-40MBs of logs in under 1 minute then _some_ application/process is crunching data somewhere.

There is no true log size setting, though some folks might say 1 log switch per hour (typically) is a good number. However if you have 2-3 switches per hour 24 hours per day you _could_ consider double the log sizes to 40MB. I would hazzard a guess that 20MB sounds like an ok number for this instance.

Relative to the the high number of log switches (for your scenario) during this peak time you might want to ensure that you have at least 4 log groups and possibly 5 or 6. How many groups is very relative to the I/O load being placed on the LOG volume and ARCHIVE volume. If the log and archive disks are shared by other _active_ applications or other Oracle db files performance will be negatively impacted.

If the database can't find an available/flushed log group and has to wait while the logs are being flushed to archives, then Oracle stuff will pretty much come to halt.

Hopes this help a little. Performance tuning in this area is very relative w/o knowing the I/O subsystem layout, memory, etc, blah, blah. <g>

Tim...

Dana Smith wrote:

> We periodically experience poor performance (read this as everything stops)
> during log switches. During this period of time (4-5 minutes,) we will
> notice several new log files being created which implies a high volume of
> data changes within the database although we have not identified a
> consistent concurrent job or transaction executing. Eventually, after 4-5
> minutes, everything frees up and functions execute normally. Afterward, we
> will notice the date of creation of the new log files as being separated by
> 1 minute and sometimes even zero minutes!
>
> We have our archive log file set at 20M and we see typically 2-3 log
> switches per hour, which seems to be an optimal number.
>
> Does this 20M size seem reasonable and is there some other configuration
> setting we should be looking at to to correct this behavior?
>
> Thanks for any help in this area.
>
> Mr. Dana Smith
Received on Thu Jan 07 1999 - 17:48:26 CET