关于Thread cannot allo…

A common problem for people with very active systems who use filesystems to store their datafiles is the error, “Thread cannot allocate new log, sequence ; Checkpoint not complete” The most commonly recommended remedies for this situation are either to use larger or more online redologs.

Unfortunately, if ‘checkpoint not complete’ is a chronic problem, neither of these solutions will eliminate the problem. They may forestall, or even reduce the frequency of the error, but the problem will not be solved.

‘Checkpoint not complete’ is a result of an instance filling and switching through all available online redologs before one checkpoint can complete. Because Oracle must always be able to recover from instance failure from the last checkpoint forward, the rules of recovery prevent an instance from reusing any online redolog that contains changes newer than the last checkpoint.

A checkpoint is the writing of all dirty blocks in the buffer cache to the datafiles as of a particular SCN. In general, if a checkpoint cannot complete in a timely fashion, it is a result of slow I/O to the datafiles. The possible solutions to this problem seek to eliminate the I/O bottleneck, or compensate for a slow I/O subsystem.

The problem with the recommendations to increase the size or number of redologs is that if the rate of DML activity is so high that checkpoints cannot keep up, there is no reason to think that by increasing the amount of online redologs, that it will make the I/O subsystem any more able to keep up. That is to say, it will take a longer time to fill up all the online logs, but the basic problem of not being able to write out dirty blocks as fast as the database is changing will still be there.

The first step in diagnosing ‘checkpoint not complete’ is to determine if the problem is chronic or not. If the error appears in the alert log many times a day, or consistently during peak hours, then the problem is chronic. If the error appears at the same time every day or every week, or if the problem is only occasional, it is not chronic.

Non-chronic ‘checkpoint not complete’ probably doesn’t require any re-engineering of the systems architecture. It is most likely the result of a single application suddenly making a large amount of DML (inserts, updates, deletes) to the database in a short time. The best way to solve this problem is to find out if the application can reduce its generation of redo by performing its changes ‘nologging.’ Any bulk inserts can be done using append mode unrecoverable, and generate no significant redo. Deletes that clear a whole table or a whole class of records can be converted to truncates of the table or of a partition. It very least, the application can be modified to throttle the rate of change back to a rate that the I/O subsystem can keep up with. Even the crude solution of increasing the number or size of redologs may solve sporadic, non-chronic occurrences of ‘checkpoint not complete.’

Chronic ‘checkpoint not complete’ is a more complicated problem. It means that overall, the rate of DML of the instance is higher than the I/O subsystem can support. In systems with chronically slow I/O, application performance will be degraded, because the buffer cache is not purged of dirty blocks fast enough or frequently enough. Such systems show relatively long time_waited for the “buffer busy wait” and “write complete wait” events in v$system_event. The solution to such a problem is either to compensate for the problem by making the checkpoint more aggressive, or to solve the problem by making the I/O more efficient.

To understand the solution to this problem, it is first necessary to understand something about how checkpoints work. When a periodic checkpoint is being performed, a certain portion of the database writer’s capacity, or “write batch,” is made available for the checkpoint to use. If the checkpoint can’t complete in time, it is valid to infer that Oracle is not using enough of the database writer’s write batch for the checkpoint, and that it should probably use a larger portion. Note that none of this has anything to do with the CKPT background process. Checkpoints are performed by the database writer. CKPT just relieves the log writer from updating file header SCNs when checkpoints complete.

In Oracle8, a new feature, sometimes called “incremental checkpoint” or “fast start instance recovery” was introduced. This feature is enabled with the initialization parameter FAST_START_MTTR_TARGET in 9i (FAST_START_IO_TARGET in 8i), and completely changes the behavior of Oracle checkpointing. Instead of performing large checkpoints at periodic intervals, the database writer tries to keep the number of dirty blocks in the buffer cache low enough to guarantee rapid recovery in the event of a crash. It frequently updates the file headers to reflect the fact that there are not dirty buffers older than a particular SCN. If the number of dirty blocks starts to grow too large, a greater portion of the database writer’s write batch will be given over to writing those blocks out. Using FAST_START_MTTR_TARGET is one way to avoid ‘checkpoint not complete’ while living with a chronically slow I/O subsystem.

In Oracle7, although there is no incremental checkpoint feature, there is an “undocumented” initialization parameter that can be set to devote a larger portion of the write batch to checkpoints when they are in progress. The parameter is _DB_BLOCK_CHECKPOINT_BATCH, and to set it you need to find out the size in blocks of the write batch and the current checkpoint batch. This can be obtained from the internal memory structure x$kvii.

Another way to compensate for slow I/O is to increase the number of database writers. By dedicating more processes to writing the blocks out, it may be possible to allow checkpoints to keep up with the rate of DML activity on the database. Bear in mind that certain filesystems, such as AdvFS on Compaq Tru64 Unix, obtain no benefit, from multiple database writers. Such filesystems exclusively lock a file for write when any block is written to that file. This causes multiple database writers to queue up behind each other waiting to write blocks to a particular file. Oracle has provided notes on Metalink regarding such filesystems.

If you are more inclined to address the root cause of the problem than to compensate for it, then there are a few measures you can take. Oracle supports asynchronous I/O on most platforms, if datafiles are stored in raw or logical volumes. Conversion to raw or LVs requires significant engineering, but is much easier than totally replacing the storage hardware. Using asynchronous I/O also relieves the aforementioned file-locking bottleneck on certain types of filesystems.

I/O hardware upgrade or replacement is the most complex approach to solving the problem of slow I/O. Using RAID disk arrays allows data to be “striped” across many disks, allowing a high rate of write-out. Caching disk controllers add a battery-protected cache for fast write-out of data.