Re: Checkpoints extreeeemely slow?

It isn't necessarily a bad thing if a checkpoint takes a long time to complete, so long as it does complete and you don't end up with
'unable to reuse logfile checkpoint not complete' messages that stop the database completely.

Oracle 8.1 has a new checkpoint algorithm that reduces the probability of that problem.

The problem is inherent in having a large buffer and lots of processes making blocks dirty very quickly. The problem with going to multiple db_writers (which is quite a good idea, as it allows multiple processes to run dirty chains) is that you could flood the I/O system on the checkpoint, and slow down the ability of the system to stream the log out.

There is a parameter named something like 'db_max_dirty_target' which defaults to 4billion which is the limit on the number of dirty blocks that Oracle will allow in the buffer. If you reduce this (try 25% of the buffer to start with) you get more writes on a more regular basis, but less writes needing to be done for the checkpoint.

It's pretty application dependent as to what a good value of this parameter is, so up to 50% down to 5% in stages to see what the side effects are.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Greg Stark wrote in message
<87og7n47k2.fsf_at_HSE-MTL-ppp43091.qc.sympatico.ca>...


>

>"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