Re: Any Comments - StatsPack Report

On Apr 6, 7:15 pm, "Gerry Sinkiewicz" <sinki..._at_snet.net> wrote:
> "Mladen Gogala" <mgogala.SPAM-ME...._at_verizon.net> wrote in message
>
> news:pan.2007.04.05.20.01.31_at_verizon.net...
>
> > On Thu, 05 Apr 2007 05:42:39 -0700, Charles Hooper wrote:
>
> >> log buffer is a little less than 100MB
>
> > Allow me to express my gratitude in the name of Kingston Technology,
> > world's leading RAM manufacturer.
>
> > --
> >http://www.mladen-gogala.com
>
> You know I am looking for an opinion on log buffer size.
> I have heard of a software vendor that suggested a 500MB log buffer, yikes.
>
> Personally I use a 2MB redo log buffer and about 20 redo-log groups at 50MB
> each for a busy OLTP oriented
> instance.

The recommendations that I have seen in a couple performance tuning books and a couple articles suggests that a log buffer of 0.5MB to 1MB is typically sufficient to good, and much larger than 1MB is a waste of memory. On 10g, even if you specify a 1MB log buffer, Oracle may actually allocate nearly 16MB for the log buffer. Frequent commits in the database (in any session's transaction) mean that only a small portion of the log buffer will be used if the log buffer is large - the OP's system was committing 1.3 times per second and sending an average of 1,703 bytes at a time to the redo logs, so most of the 100MB allocated for the log buffer was wasted. Data in the log buffer only stays in the log buffer a short amount of time (3 seconds or less) before it is flushed to disk.

In my database, a 50MB redo log file is too small, but it may be fine in your database. Ideally, the redo logs should switch roughly every 20 to 30 minutes during times of typical to heavy activity. If Oracle is switching log files every 1-3 minutes, then you might want to reconsider the size of the redo log files (each switch causes a checkpoint). During batch processing runs, my database will burn through a 512MB log file in a couple minutes; since the batch processes only occur a couple times a day, I force log file switches at least once an hour. Take a look at the frequency of log switches in your database, and use that to determine the most appropriate size for the redo logs.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Fri Apr 06 2007 - 20:16:46 CDT