Wednesday, November 3, 2010

How Checkpoints Affect Performance

From Oracle's notes:


Frequent checkpointing writes dirty buffers to the datafiles more often than otherwise, and so reduces cache recovery time in the event of an instance failure. If checkpointing is frequent, then applying the redo records in the redo log between the current checkpoint position and the end of the log involves processing relatively few data blocks. This means that the cache recovery phase of recovery is fairly short.
However, in a high-update system, frequent checkpointing can reduce runtime performance, because checkpointing causes DBWn processes to perform writes.
To reduce the checkpoint frequency and optimize runtime performance, you can do the following:
  • Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes. Small log files can increase checkpoint activity and reduce performance. You should make all logs the same size.
  • Set the value of the LOG_CHECKPOINT_INTERVAL initialization parameter (in multiples of physical block size) to zero. This value eliminates interval checkpoints.
  • Set the value of the LOG_CHECKPOINT_TIMEOUT initialization parameter to zero. This value eliminates time-based checkpoints.
  • Set the value of FAST_START_MTTR_TARGET (and FAST_START_IO_TARGET) to zero to disable fast-start checkpointing.


SQL> desc V$INSTANCE_RECOVERY
 Name                                                                                                                                  Null?    Type
 -----------------------------------------------------------------------------------------------------------------
 RECOVERY_ESTIMATED_IOS                                                                                             NUMBER
 ACTUAL_REDO_BLKS                                                                                                          NUMBER
 TARGET_REDO_BLKS                                                                                                           NUMBER
 LOG_FILE_SIZE_REDO_BLKS                                                                                              NUMBER
 LOG_CHKPT_TIMEOUT_REDO_BLKS                                                                                NUMBER
 LOG_CHKPT_INTERVAL_REDO_BLKS                                                                              NUMBER
 FAST_START_IO_TARGET_REDO_BLKS                                                                            NUMBER
 TARGET_MTTR                                                                                                                        NUMBER
 ESTIMATED_MTTR                                                                                                                 NUMBER
 CKPT_BLOCK_WRITES                                                                                                         NUMBER
 OPTIMAL_LOGFILE_SIZE                                                                                                      NUMBER
 ESTD_CLUSTER_AVAILABLE_TIME                                                                                    NUMBER
 WRITES_MTTR                                                                                                                        NUMBER
 WRITES_LOGFILE_SIZE                                                                                                       NUMBER
 WRITES_LOG_CHECKPOINT_SETTINGS                                                                           NUMBER
 WRITES_OTHER_SETTINGS                                                                                                 NUMBER
 WRITES_AUTOTUNE                                                                                                           NUMBER
 WRITES_FULL_THREAD_CKPT                                                                                        NUMBER

---------------------- ---------------- ---------------- ----------------------- ---------------------------

RECOVERY_ESTIMATED_IOS ACTUAL_REDO_BLKS TARGET_REDO_BLKS LOG_FILE_SIZE_REDO_BLKS LOG_CHKPT_TIMEOUT_REDO_BLKS LOG_CHKPT_INTERVAL_REDO_BLKS FAST_START_IO_TARGET_REDO_BLKS TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES OPTIMAL_LOGFILE_SIZE ESTD_CLUSTER_AVAILABLE_TIME WRITES_MTTR WRITES_LOGFILE_SIZE WRITES_LOG_CHECKPOINT_SETTINGS WRITES_OTHER_SETTINGS WRITES_AUTOTUNE WRITES_FULL_THREAD_CKPT
---------------------- ---------------- ---------------- ----------------------- ---------------------------
                    19               21           100000                 2764800                     4978564               100000                                           0             12           4086546                                    0                    0                        5966427                     0           62603                    2063




select ESTIMATED_MTTR  from V$INSTANCE_RECOVERY

SQL> select ESTIMATED_MTTR  from V$INSTANCE_RECOVERY;

ESTIMATED_MTTR
--------------
            12


SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES
FROM V$INSTANCE_RECOVERY;
  2
TARGET_MTTR ESTIMATED_MTTR CKPT_BLOCK_WRITES
----------- -------------- -----------------
          0             12           4086554


SQL> SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME IN ('physical reads','physical writes',
               'physical writes non checkpoint');
  2    3    4
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
physical reads                                                      5869523
physical writes                                                     6338680
physical writes non checkpoint                                      2252117

The first row shows the number of data blocks retrieved from disk. The second row shows the number of data blocks written to disk. The last row shows the number of writes to disk that would occur if you turned off checkpointing.

So we have the following values:
log_checkpoint_interval              integer     100000
log_checkpoint_timeout               integer     1800
fast_start_mttr_target                     integer     0

We have to make those zero.


No comments:

Post a Comment