Re: log buffer space vs. redo log space requests

"Scott Watson" <rscottwatson_at_hotmail.com> wrote in message news:mGiR6.18515$UH5.955932_at_wagner.videotron.net...
> Thanks, for the input guys! Here is some more information...
>
> My log buffer is at 2Megs when I was observing these waits.
>
> There are 4 groups of online redo logs of 20 megs each. When the app was
> running under a regular load I noticed that the logs were switching at
> around 45 seconds. So I have since bumped by log files up to 200Megs each
> and waiting to see what this will give. I was thinking that the log
> switches were causing the LGWR to fall behind.
>
> If this fails I will try increasing my log buffer again.

I wouldn't do that. I'd be increasing the size of the redo logs to something more like 2Gb. You want the rate of log switching to come down (usually to something like once per hour. My own preference is for once per day. Obviously the rate of switching affects the time taken to perform instance recovery, though).

> What do you think about decreasing the checkpoint timeout/ interval so
 there
> is less work to do once a log switch does come around.
>

The more mini-checkpoints you introduce, the lower your overall performance. But better a steady low performance than one that roller-coasters up and down as wait events are encountered. Use them as a last resort, in my book.

Regards
HJR
> Thanks,
> scott.
>
> "Mark J. Bobak" <mark.NOSPAM_at_bobak.net> wrote in message
> news:20010530.203754.1244316437.24699_at_bobak.net...
> > In article <9f3b7i0cci_at_drn.newsguy.com>, "Thomas Kyte"
> > <tkyte_at_us.oracle.com> wrote:
> >
> > > In article <ip7R6.748$m76.424872_at_news20.bellglobal.com>, "Scott
 says...
> > >>
> > >>I am a little confused as to the values I am getting from these two
> > >>statistics.
> > >>
> > >>log buffer space in V$SYSTEM_EVENT shows a value for waits which leads
> > >>me to believe that the log buffer is not large enough however the redo
> > >>log space requests from V$SYSSTAT shows a value of zero.
> > >>
> > >>Therefore one stat tells me there is not enough space while the other
> > >>tells me everything is ok. I think I am misinterpreting these
> > >>statistics. Can someone set me straight.
> > >>
> > >>Oracle 8.1.6 running on solaris.
> > >>
> > >>thanks,
> > >>Scott.
> > >>
> > >>
> > >>
> > >>
> > >>
> > > log buffer space is a wait for space in the redo log buffer (in the
> > > SGA). you'll get this when your log buffer is too small or LGWR is not
> > > able to empty it fast enough (slow IO)
> > >
> > > redo log space is a wait for space in the physical online redo log
 file
> > > (on disk). you might get this on a "checkpoint not complete".
> > >
> > > The are somewhat un-related.
> > >
> > > Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and
> > > such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
 Oracle
> > > Magazine: http://www.oracle.com/oramag Opinions are mine and do not
> > > necessarily reflect those of Oracle Corp
> > >
> >
> > In addition to what Tom said, if you have log buffer space waits,
> > this could be due to excessive log switches, which cause a backlog
> > in the log buffer.
> >
> > How big are your log files?
> > How big is your log buffer?
> > How often are you seeing log switches?
> >
> > Also, Steve Adams has some good info on log buffer and log file
> > tuning, check out http://www.ixora.com.au/
> >
> > Hope that helps,
> >
> > -Mark
>
>
Received on Thu May 31 2001 - 04:30:29 CDT