Re: Checkpoints extreeeemely slow?

Greg Stark wrote:
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> writes:
>
> > You don't say how big the db_block_buffer is -
> > a checkpoint takes time because it is writing
> > all dirty blocks to disc. For a very large
> > buffer, and lots of blocks, and a single writer
> > process the time required could get into this
> > ballpark.
> >
> > At 100 blocks per second, allow 6,000 blocks
> > per minute, then 15 minutes is only 90,000
> > blocks. You may simply need to tune your
> > db writer strategy.
>
> Sorry I meant to include db_block_buffers. 192,000 with 8k blocks.
>
> So are long checkpoints just a danger of having an overly large buffer cache?
> Would starting more db writers help keep the number of dirty blocks down? Is
> it preferable to start extra db writers or use db writer io slaves?
>
> The data is in many data files (41 I think) and they're in veritas quickio
> files. So Oracle should be able to use asynchronous parallel writes
> efficiently.
>
> --
> greg

It sounds like you've got some big processing going on here. Although quickio is very good, for this kind of load you may want to look at raw for maximum i/o throughput.

Then its a case of ensuring that the write batch size isn't too excessive. Ensure that 'db_files' is set not too high, or look at db_block_max_dirty_target as Johnathan suggests.

If you are getting large log file sync waits, then your log buffer may possibly be too large, or you may need to tweak about with _log_io_size.

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse