The Block Change Tracking Buffer wait event is related to the sizing and usage of the CTWR dba buffer in the Oracle Large Pool memory structure. 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.
How block change tracking works
When data blocks change, shadow processes track the changed blocks in a private area of memory at the same time that they generate redo. When a commit is issued, the Block Change Tracking (BCT) information is copied to a shared area in Large Pool called ‘CTWR dba buffer.’ At the checkpoint, a new background process called 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.
How to check whether CTWR is enabled
By default, the CTWR process is disabled because it can introduce some minimal performance overhead on the database. 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.
How to address a block change tracking buffer wait event
When a session waits on the ” Block Change Tracking Buffer ” wait 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.
Leave a Reply