Re: Help me tuning this wait event:log file sync

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0207151402.18ca5b57_at_posting.google.com...
> chao_ping <chao_ping_at_163.com> wrote in message
news:<3d312489$1_at_usenetgateway.com>...
> > Yong Huang wrote:
> > > If you can't control the frequency of commits, reduce log_buffer.
How
> > > big is it?
> > now log buffer is 2M, In early time, it was 1M, and i see much wait time
> > in log buffer space,(once it was the 4th top wait event), so i enlarged
> > the log buffer size from 1m to 2m.
>
> Never use a log_buffer larger than 1M. It's useless over that limit.
>

Not quite true. On very heavy-transactional-load systems, a buffer uo to 5 or 6Mb *may* (or may not!) be appropriate. Had Oracle ever decided that anything bigger than 1Mb was utterly "useless", they would never have introduced the rule about LGWR flushing every 1Mb of uncommitted redo (because that rule doesn't get invoked until the buffer is bigger than 3Mb, because of the 'flush when 1/3rd full' rule).

> Do you have multiple log members per group? Use 1 member.

I definitely can't agree with this last suggestion. 1 member groups? A recipe for data loss.

You're right that it might help eliminate some performance woes, of course. Oracle has to do less work, so yeah, of course things work faster. But in the eternal trade-off between security and performance, you've just plumped squarely on the performance side of things. Which may or may not be acceptable to the original poster, but it's certainly not what I would want to recommend.

>
> If you got 40 commits per second, the only solution may be rewrite the
> application. If you can't, try a competitor's product.

If there are 40 commits per second, I might well try a 6Mb log buffer.

Regards
HJR
>
> Yong Huang
Received on Mon Jul 15 2002 - 17:30:32 CDT