| |
 |
|
Oracle Tips by Burleson |
Tuning Checkpoints
Checkpoints provide for concurrency in an Oracle database.
Checkpoints write out timestamp and SCN information as well as dirty
blocks to the database files.Pre-7.3.4 the checkpoint process was
optional, now it is required.
Checkpoints provide for rolling forward after a system crash. Data
is applied from the time of the last checkpoint forward from the
redo entries. Checkpoints also provide for reuse of redo logs. When
a redo log is filled the LGWR process automatically switches to the
next available log. All data in the now inactive log is written to
disk by an automatic checkpoint. This frees the log for reuse or for
archiving.
Checkpoints occur when a redo log is filled, when the INIT.ORA
parameter LOG_CHECKPOINT_INTERVAL ORACLE7 is reached (Total bytes
written to a redo log), or the elapsed time has reached the INIT.ORA
parameter LOG_CHECKPOINT_TIMEOUT expressed in seconds or every three
seconds, or when an ALTER SYSTEM command is issued with the
CHECKPOINT option specified.
While frequent checkpoints will reduce recovery time, they will also
decrease performance. Infrequent checkpoints will increase
performance but increase required recovery times. To reduce
checkpoints to only happen on log switches, set
LOG_CHECKPOINT_INTERVAL to larger than your redo log size, and set
LOG_CHECKPOINT_TIMEOUT to zero.
If checkpoints still cause performance problems, set the INIT.ORA
parameter CHECKPOINT_PROCESS to TRUE to start the CKPT process
running. This will free the DBWR from checkpoint duty and increase
performance. The INIT.ORA parameter PROCESSES may also have to be
increased. Note that on Oracle8 and greater the checkpoint process
is not optional and is started along with the other Oracle instance
processes.
Another new option with Oracle8i is the concept of fast-start
checkpointing. In order to configure fast-start checkpointing you
set the initialization parameter FAST_START_IO_TARGET. The
FAST_START_IO_TARGET parameter sets the number of IO operations that
Oracle will attempt to limit itself to before writing a checkpoint.
This feature is only available with Oracle 8i Enterprise Edition.
Other initialization parameters that control checkpointing are:
-
LOG_BUFFER_SIZE – should be set such
that there aren’t large numbers of small writes and the overall
write time isn’t too long, usually not more than 1 megabyte.
-
LOG_SMALL_ENTRY_MAX_SIZE (Gone in 8i)
sets the size in bytes for the largest copy to the redo buffers
that occurs under the redo allocation latch. Decreasing the size
of this parameter will reduce contention for the redo allocation
latch.
-
LOG_SIMULTANEOUS_COPIES (Gone to “_” in
8i) set to twice the number of CPUs to reduce contention for the
redo copy latches by increasing the number of latches.
-
LOG_ENTRY_PREBUILD_THRESHOLD( Gone to
“_” in 8.0, gone in 8i) sets the number of bytes of redo to
gather before copying to the log buffer. For multi-CPU systems
increasing this value can be beneficial.
This is an excerpt by
Mike Ault’s e-book:
Tuning
Third-party Vendor Oracle Systems
Tuning when you can't touch the code
ISBN:
0-9740716-3-3
http://www.rampant-books.com/ebook_vendor_tune.htm
|
