Re: Checkpoints extreeeemely slow?

"Barry Evans" <barry_at_promaxis.com> writes:

> A 01624 means a checkpoint did not occur. And with the way you set you
> LOG_CHECKPOINT parameters a checkpoint can only occur at a log switch. This
> is not recomended because a transaction that continues into the second log
> will prevent it.
>
> So set LOG_CHECKPOINT_TIMEOUT for say 60 ( paramerter is in seconds)
> Also it is recomended to use a minimum of 3 redo log groups this would also
> help your problem.
> I would suggest 4 redo log groups with a 100Mb redo log in each. ( if you
> can you should mirror them )

We had four with a log_checkpoint_interval set. But we observed spikes in performance every 20 minutes. I found that Oracle had chosen to proceed through the logs by doing disk1,disk1,disk2,disk2. That was causing serious contention between the checkpoint and the new writes from new transactions.

I'm worried by what you say, if a transaction modifies more blocks than fit in one redo log it's going to completely wedge the database until it finishes and the checkpoint can run? That would be a disaster.

My current thinking is that if I add a db writer or three and set the max_db_dirty_block, and eventually speed up the data files (by adding spreading over more disks) and bring down the time to checkpoint that would allow me to experiment with more redo logs without running into performance problems like we had.

-- 
greg