Oninit
Talk to an expert →

onstat -g ckp Checkpoint History and Configuration Recommendations

The onstat -g ckp command displays checkpoint history and configuration recommendations.

Due to the width of the output from onstat -g ckp an example is located here

Output Description

Option Description
Auto Checkpoints Indicates if the AUTO_CKPTS configuration parameter is on or off
RTO_SERVER_RESTART Displays the RTO time in seconds. Zero (0) means that RTO is off.
Estimated recovery time ## seconds Indicates the estimated recovery time if the data server stops responding. This value only appears if RTO_SERVER_RESTART is active.
Interval Checkpoint interval ID
Clock Time Clock time when checkpoint occurred.
Trigger Event that triggered the checkpoint. An asterisk (*) indicates that the checkpoint requested was a transaction-blocking checkpoint. [For more details see below]
LSN Logical log position where checkpoint is recorded
Total Time Total checkpoint duration, in seconds, from request time to checkpoint completion
Flush Time Time, in seconds, to flush bufferpools
Block Time Time a transaction was blocked, in seconds, by a checkpoint that was triggered by a scarcity of some needed resource. For example, running out of physical log, or wrap-around of the logical log.
# Waits Number of transactions blocked waiting for checkpoint
Ckpt Time Time, in seconds, for all transactions to recognize a requested checkpoint
Wait Time Average time, in seconds, that transactions waited for checkpoint
Long Time Longest amount of time, in seconds, a transaction waited for checkpoint
# Dirty Buffers Number of dirty buffers flushed to disk during checkpoint
Dskflu/sec Number of buffers flushed per second
Physical Log Total Pages Total number of pages physically logged during checkpoint interval
Physical Log Avg/Sec Average rate of physical log activity during checkpoint interval
Logical Log Total Pages Total number of pages logically logged during checkpoint interval
Logical Log Avg/Sec Average rate of logical log activity during checkpoint interval
Max Plog pages/sec Maximum rate of physical log activity during checkpoint interval
Max Llog pages/sec Maximum rate of logical log activity during checkpoint interval
Max Dskflush Time Maximum time, in seconds, to flush bufferpools to disk
Avg Dskflush pages/sec Average rate bufferpools are flushed to disk
Avg Dirty pages/sec Average rate of dirty pages between checkpoints
Blocked Time Longest blocked time, in seconds, since the database server was last started

Trigger Description

Option Description
Admin Administrator related tasks. For example:
  • Create, drop, or rename a dbspace
  • Add or drop a chunk
  • Add or drop a log file
  • Change physical log size or location
  • After "shrink" operation on partition
  • Turn on or off mirroring
Backup Backup related operations. For example:
  • Fake backup
  • Start of an archive
  • After the completion of a physical restore
CDR ER subsystem is started for the first time, or is restarted after all of the replication participants were removed.
CKPTINTVL When the checkpoint interval expires. The checkpoint interval is the value specified for the CKPTINTVL parameter in the ONCONFIG file.
Conv/Rev Conversion reversion checkpoint. After the check phase of convert and before the actual conversion of disk structures. After the reversion is completed also triggers a checkpoint.
HA High availability. For example:
  • A new RSS or SDS node is added to a High Availability cluster
  • A secondary server is promoted to a primary server
  • The physical log file is low on a secondary server
HDR High-Availability Data Replication. For example:
  • The mode of the server is changed
  • The start of the first transfer after HDR is setup
  • There is the potential for a physical log overflow on primary or secondary servers
Lightscan Before the look aside is turned off on partitions.
Llog Running out of logical log resources.
LongTX Long Transaction. If a long transaction has been found but not stopped, a checkpoint is initiated to stop the transaction. During rollback, a checkpoint is initiated in the rollback phase if a checkpoint has not already happened after long transaction has been aborted.
Misc Miscellaneous events. For example: A dbspace or chunk is being brought down because of I/O errors During rollback when the addition of the chunk is being undone. For example, when removing the chunk.
Pload When the High-Performance Loader starts in the Express mode.
Plog Physical log has one of the following conditions:
  • Physical log is 75% full
  • The amount of physical log used plus the number of dirty partitions is more than 90% of physical log size
Restore Pt Restore Point. Checkpoints at the start and end of a restore point. The restore point is (used by conversion guard) CONVERSION_GUARD configuration parameter is enabled and a temporary directory is specified in the RESTORE_POINT_DIR configuration parameter.
Recovery During a restore, at the start of a fast recovery.
Reorg At the start of online index build.
RTO Maintaining the Recovery Time Objective (RTO) policy. During normal operations, when the restart time after a crash might exceed the value set for the RTO_SERVER_RESTART configuration parameter.
Stamp Wrap Checkpoint timestamp. If the new checkpoint timestamp appears to be before the last written checkpoint, then the timestamp is advanced out of interval between checkpoints. Another checkpoint is triggered.
Startup At the startup of the database server.
Uncompress Uncompress commands that are issued on a table or partition. This applies only for checkpoints on tables or databases that are not logged.
User A checkpoint request is submitted by the user.

Performance and Tuning

When instance detects a configuration issue, a performance advisory message with tuning recommendations is displayed, and the same message is entered into the message log. For example

Physical log is too small for bufferpool size. System performance may be less than optimal.
Increase physical log size to at least XX Kb

Physical log is too small for optimal performance.
Increase the physical log size to at least XX Kb.

Logical log space is too small for optimal performance.
Increase the total size of the logial log space to at least XX Kb.

Transaction blocking has taken place. The physical log is too small.
Please increase the size of the physical log to XX Kb

Transaction blocking has taken place. The logical log space is too small.
Please increase the size of the logical log space to XX Kb

Notes

The 'Block Time' and '# Waits' are the important columns, in the ideal world they both should be zero.