block change tracking buffer space

Wait is related to the sizing / usage of the CTWR dba buffer in the Large Pool memory structure more …

Solutions

Problem: 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.

Solution 1: 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.

Solution 2: Consider changing the value of Large_pool_size or the hidden parameter _bct_public_dba_buffer_size to a larger value. NOTE: 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).

Solution 3: Turn off the BCT feature so that it does not require the CTWR dba buffer.

Expanded Definition

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. 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%’;