Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Checkpoints extreeeemely slow?

Checkpoints extreeeemely slow?

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: 2000/04/05
Message-ID: <874s9guq3f.fsf@HSE-Montreal-ppp33181.qc.sympatico.ca>#1/1

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
Received on Wed Apr 05 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US