Re: Checkpoints extreeeemely slow?

Sorry, but I can't agree that a 200M redo log is ridiculous. You don't want to be doing checkpoints too often or you take too big of a hit. At 25M we were doing 4 log switches a minute during peak processing so we bumped our logs up to 100M to get to something reasonable.

Geoffrey Bray

In article <954995316.17785.3.pluto.d4ee154e_at_news.demon.nl>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
> A redo log of 200M is ridiculous. 20-25M should be sufficient.
> And the disk array: let me guess, RAID-5?
> Means a performance penalty for any write activity, let alone
 sequential
> writes, as RAID-5 has parity checks.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> Greg Stark <greg-spare-1_at_mit.edu> wrote in message
> news:874s9guq3f.fsf_at_HSE-Montreal-ppp33181.qc.sympatico.ca...
> >
> > I'm not sure I'm interpreting these numbers correctly, but they seem
 to be
> > implying that the cause of our performance problems is checkpoints
 that
 are
> > taking upwards of 15 minutes. Is that even possible? One question I
 have
 is if
> > 8.1.6 has a CKPT process running does that necessarily mean it's
 using it?
 The
> > CHECKPOINT_PROCSES parameter seems to be gone in 8i.
> >
> > I've been experimenting with the redo logs, at this point i have the
 least
> > variables I can expect to have: two redo logs on two different
 disks. Each
 is
> > 200Mb, it takes about 20m to switch. The log_checkpoint_timeout is 0
 and
> > log_checkpoint_interval is 999999999.
> >
> > The intent is that checkpoints occur only on log file switches and
 always
 read
> > from the _other_ disk from the current active redo log.
> >
> > What's actually happening is that the checkpoint seems to go on and
 on for
> > about 15 minutes, nearly long enough to bring everything crashing to
 a
 halt as
> > it wraps around and runs into the active redo log. I believe the
 checkpoint is
> > happening because the following query continues to show two values
 with a
> > difference of one for this duration and because when I was dropping
 and
 adding
> > log files I received a 01624 on the not current log file.
> >
> > SQL> select * from v$sysstat where name like 'background
 checkpoints%';
> >
> > STATISTIC# NAME CLASS VALUE
> > ----------

• ---------- ----------
  > > 129 background checkpoints started 8 253
  > > 130 background checkpoints completed 8 252
  > >
  > > 15m seems like an incredibly long time to be doing a checkpoint even
  for 200M.
  > > I was under the impression that checkpoints should take seconds, not
  tens of
  > > minutes. There is no heavily used data on the disks with the redo
  logs and the
  > > disk array they're on should be blazingly fast. So I'm very
  frustrated that
  > > they seem to be very slow.
  > >
  > > And the redo logs do seem to be a bottleneck. When I select from
  > > v$session_wait I nearly always see at least one session and often
  two or more
  > > waiting on "log file sync". I'm worried this indicates a serious
  problem with
  > > the redo logs.
  > >
  > > --
  > > greg
  >
  >
  

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 06 2000 - 00:00:00 CDT