Re: Redo Log problem

On Wed, 21 Oct 1998, Chuck Hamilton wrote:
>
> On Mon, 19 Oct 1998 22:06:21 +0800, Horace Chow <horacecw_at_netvigator.com> wrote:
>
> >Another workaround is to add more group of redo log. As usual, 3 or
> >more groups of redo logs are enough for a small system.
>
> Larger log files is a better solution. Remember that when you switch
> log files, you also perform an i/o intensive checkpoint. Larger log
> files will reduce checkpointing and solve the other problem too.
>
> I'd start with three 10mb logs and go from there. If you see you're
> checkpointing too often still, increase the size further.

Remember that with less frequent checkpointing, you may incur several detrimental effects:

• You will experience increased mean time to recovery (MTR) for crash recovery, because crash recovery has to start application of redologs from the point in time of the most recent checkpoint. The longer ago that was, the more recovery you will have to do.
• You will notice an increased incidence of "busy buffer" waits and "write complete" waits for sessions, because more buffers in the block buffer cache are dirty at any given time.
• It will increase the number of dirty buffers that must be written out for a checkpoint, making the checkpoint take longer.

There are smoother ways of tuning checkpoints to complete quickly than adjusting the size of your redologs. Feel free to make your redologs a reasonable size so that your MTR for crash recovery is acceptible, and so that the database switches logs often enough not to have the block buffer cache get too dirty. Some people like to say that this means every 15 minutes. Actually this figure should be a factor of your redo write rate, your MTR requirements and your block buffer cache size.

If, once you have settled on a log size that you like, checkpoints are not completing in time for the next log switch, you should take a look at the x$kvii table. Specifically, log in as sys and look at:

select kviidsc, kviival
from x$kvii
where kviitag in ('kcbswc','kcbscc');

This will show you the number of DB blocks in a "write batch" on your instance, and the number of *those* blocks allocated to checkpoints (when they are happening). For example:

KVIIDSC                        KVIIVAL
--------------------------- ----------
DB writer IO clump                8192
DB writer checkpoint clump           1

The DBW0 (or DBWR for Oracle7) process on this instance batches up datafile writes in clumps of 8192 DB blocks. Each of those write batches takes a certain amount of time to complete. During the writing of those batches, if a checkpoint is under way, only one block of each batch can be used by the checkpoint to write out dirty buffers. If the I/O system is slow, and if asynchronous I/O is not enabled, the checkpoint may take a long time to complete, or it may not complete before the next log switch, and a new checkpoint begins.

To make sure the checkpoint completes before the next log switch, it is possible to crank up the value of the "DB writer checkpoint clump" so that checkpoints complete faster, by increasing the parameter "DB_BLOCK_CHECKPOINT_BATCH." The value of "DB writer IO clump," on the other hand, is not directly adjustable except by the now deprecated parameter _DB_BLOCK_WRITE_BATCH. As of Oracle 7.3, the write batch size is a derived value equal to (DB_FILES*DB_FILE_SIMULTANEOUS_WRITES)/2, so you can adjust it by adjusting those parameters.

You could theoretically increase the value of DB_BLOCK_CHECKPOINT_BATCH to one half, three quarters, or even 100% of the write batch. If you did something like this, you would certainly complete the checkpoint before the next log switch, but during the checkpoint, sessions would incur many "busy buffer" waits and "write complete" waits, because the checkpoint would be hogging the DB writer. Having this kind of very aggressive checkpoint is similar to typing "alter system checkpoint;" This command forces a checkpoint to happen *right away* by hogging some large amount of the write batch for the checkpoint. I think it may even use all of the write batch.

--
Jeremiah Wilton http://www.wolfenet.com/~jeremiah Received on Wed Oct 21 1998 - 22:24:19 CDT