Oracle Block Change Tracking Buffer Wait Event

The Block Change Tracking Buffer wait event is related to the sizing / usage of the CTWR dba buffer in the Large Pool memory structure.

How Block Change Tracking (BCT) Works

When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time they generate redo. When a commit is issued, the BCT information is copied to a shared area in Large Pool called ‘CTWR dba buffer’. At the checkpoint, a new background process, Change Tracking Writer (CTWR), writes the information from the buffer to the change-tracking file. If contention for space in the CTWR dba buffer occurs, a wait event called, ‘Block Change Tracking Buffer Space’ is recorded. Several causes for this wait event are poor I/O performance on the disk where the change-tracking file resides, or the CTWR dba buffer is too small to record the number of concurrent block changes.

By default, the CTWR process is disabled because it can introduce some minimal performance overhead on the database.

How to Tune for BCT Wait Events

You can query V$BLOCK_CHANGE_TRACKING table to determine whether change tracking is enabled and CTWR dba buffer is being used:

SELECT status FROM v$block_change_tracking;

If the CTWR process is enabled, you can view the size of the CTWR dba buffer by looking at v$sgastat:

SELECT * FROM v$sgastat
 WHERE name like 'CTWR%';
There is a hidden parameter that can increase or decrease the size of the CTWR dba buffer. However, you should always check with Oracle Support before using a hidden parameter value as Oracle may not support these types of changes.
>_bct_public_dba_buffer_size = total size of all public change tracking dba buffers.

You can also try allocating more space to the large pool by increasing the large_pool_size. Currently, Oracle automatically assigns space to the CTWR dba buffer from the Large Pool free space.

Conclusion

When a session waits on the ” Block Change Tracking Buffer ” event, it indicates that there was a wait for space in the CTRW dba buffer. If this happens too often the performance of Backups and/or the entire database can suffer. There are several alternatives to fixing this if it occurs in your system:

• Review the location of the change-tracking file to ensure that it’s not co-located on disks with other heavily used or “HOT” files. For example, do not locate the change-tracking file on the same disk with your redo or archive files.
• Consider changing the value of Large_pool_size or the hidden parameter _bct_public_dba_buffer_size to a larger value.
• Turn off the BCT feature so that it does not require the CTWR dba buffer.