PROBLEM:

Application Team is running lot of DML activities on the database and suddenly database became slow with lot of blocking sessions with
“Block Change Tracking Buffer” event.

Also, the alert log was filled with below error:

SOLUTION:

This issue is related to block change tracking. This wait event occurs when block change tracking file reached its maximum size and unable to extend it.

First, check whether block change tracking is enabled or not.

As we can see block change tracking is enabled. Now how will fix this issue.

Solution 1:

If large_pool_size is set in the database, Oracle usually allocated the size for block change tracking file from large_pool_size. So try increasing the size of the large_pool_size parameter.

Solution 2:

There is a hidden parameter “_bct_public_dba_buffer_size”, which defines the size of the block change tracking. You can try setting a higher value for this hidden parameter. However, hidden parameters shouldn’t be used unless suggested Oracle support.

Solution 3:

The easiest one is to disable the block change tracking. Sometimes this command can take around 1 to 2 minutes if the database is heavily loaded.

SQL>alter database disable block change tracking ;

database altered

SQL> select filename,status from v$block_change_tracking;

FILENAME
——————————————————————————–
STATUS
———-

DISABLED

Once one implement any of the solutions, this errors from the alert log and the wait events will be cleared. And database will come to normal.