Checkpoints and the physical log

The PHYSFILE configuration parameter specifies the size of the initial physical log. A checkpoint occurs when either the physical log becomes 75 percent full or a high number of dirty partitions exist.

The rate at which transactions generate physical log activity can affect checkpoint performance. To avoid transaction blocking during checkpoint processing, consider the size of the physical log and how quickly it fills.

You can enable the database server to expand the size of the physical log as needed to improve performance by creating an extendable plogspace for the physical log.

For example, operations that do not perform updates do not generate before-images. If the size of the database is growing, but applications rarely update the data, little physical logging occurs. In this situation, you might not need a large physical log.

Similarly, you can define a smaller physical log if your application updates the same pages. The database server writes the before-image of only the first update that is made to a page for the following operations:

  • Inserts, updates, and deletes for rows that contain user-defined data types (UDTs), smart large objects, and simple large objects
  • ALTER statements
  • Operations that create or modify indexes (B-tree, R-tree, or user-defined indexes)

Because the physical log is recycled after each checkpoint, the physical log must be large enough to hold before-images from changes between checkpoints. If the database server frequently triggers checkpoints because it runs out of physical log space, consider increasing the size of the physical log.

If you increase the checkpoint interval or if you anticipate increased update activity, you might want to increase the size of the physical log.

The physical log is an important part of maintaining RTO_SERVER_RESTART policy. To ensure that you have an abundance of space, set the size of the physical log to at least 110 percent of the size of all buffer pools.

You can use the onparams utility to change the physical log location and size. You can change the physical log while transactions are active and without restarting the database server.