Re: Need help with redo log writer

empete2000_at_yahoo.com (Pete's) wrote in message news:<6724a51f.0201180542.296dae2e_at_posting.google.com>...
> Having the redo logs on Raid5 is not recommended even by Oracle. If
> they are so worried about reliability, try to at least get the logs
> multiplexed across mirrored disks with at least 2 logs per group.
>
> HTH,
> Pete's
>
>
> Flyfish <d_at_deja.com> wrote in message news:<MPG.16b0efd75e2bf6cf98968a_at_News.CIS.DFN.DE>...
> > In article <DKF*lvCep_at_news.chiark.greenend.org.uk>,
> > andrewm_at_chiark.greenend.org.uk says...
> > > Flyfish <d_at_deja.com> wrote:
> > > >I have a DSS system on a HP9000 (K 460) running HPUX 11.0, Oracle
> > > >8.1.6.0.
> > > >
> > > >Redo log write time is "high" per Spotlight on Oracle monitoring.
> > > >Averages between 60-120+ ms.
> > > >
> > > >Redo logs are on a striped logical (3 physical drives, 10K rpm scsi-2)
> > > >volume with low disk I/O according to sar (7-10% utilized)
> > > >
> > > >The redo buffer is set to 1536K.
> > > >
> > > >The database is not in archivelog mode.
> > > >
> > > >What can I do to reduce the redo log write time? Would increasing or
> > > >decreasing the size of the redo log buffer have any impact?
> > >
> > > Firstly, is "log sync wait" or "log parallel write" a major wait event
> > > on your system? If not, it's probably not important to tune.
> > >
> > > When you say "striped", do you mean RAID-0 or RAID-5 or something
> > > else? For low to moderate redo rates single disks are fine for redo.
> > > RAID-5 should never be used for redo.
> > >
> > > What does sar report the average service time as? This will indicate
> > > if the disks are a problem or something else.
> > >
> > > If the disks aren't the bottleneck, it might be LGWR is being kept off
> > > the CPU by other processes. Is the CPU load very high?
> > >
> > > Is your redo buffer that large because you need it? 1.5MB log buffer
> > > means that LGWR won't start writing in the background until 512kB
> > > uncommitted redo is in the buffer, so if your application is producing
> > > smaller transactions than this, it's likely that the redo for the whole
> > > transaction needs to be written on a commit. Try a smaller log buffer.
> > >
> > >
> >
> > The wait is log parallel write.
> >
> > The striping is in fact, Raid 5, unfortunately not subject to change as
> > the system owner 'requires' raid 5 for reliability.
> >
> > Average service time is low, CPU time is all over the place but it's
> > never pegged over 75-80% for any amount of time at all, usually running
> > 30-50% of total CPU.
> >
> > The size of the redo buffer was set before I got involved, it's not
> > clear to me why it's so big. I will try to set it smaller.

The fact that it is a DSS system implies periodic large data loads. If that is the case, you may want to have different tuning for those times, probably big redo logs and buffers (remember, SGA gets flushed for a log switch) for loads and small (or more carefully tuned) for DSS times. It also implies your load is going to be all over the place because you have little control over queries. However, I would wonder that your cpu is never pegged. If you compress a gigabyte offline file, does that peg a cpu? If not, you probably have some serious i/o tuning issues.

Check your redo log buffer like so:

select name||' = '||value||chr(10)
from v$sysstat
where name = 'redo log space requests'
/

Make it bigger until this gets near zero. If it is already near zero, consider making it smaller until it goes up, then back off.

If you are being forced to use raid-5, find a powerful hp-sysadmin-mage to "tune your system for reliability," and tell him your redo log requirements.

jg

--
Sometimes a pro can fix what the experts mess up.