Is an Apply Server Performing Poorly for Certain Transactions?

This query returns the SQL statement that is currently being run by the specified Apply server. The SQL statement shows the table on which the transaction is being applied.

Is the Apply Process Waiting for a Dependent Transaction?

When the parallelism parameter for an Apply process is set to a value higher than 1 and the commit_serialization parameter of the Apply process is set to FULL, the Apply process may detect ITL contention if there is a transaction that is dependent on another transaction with a higher SCN. ITL contention occurs because some other session has a lock on the rows in the contested block and there are no free ITL slots.

When does this usually happen? One probable scenario is the case of a shared bitmap index fragment. The very nature of a bitmap index could cause this. A bitmap index fragment usually covers many rows in the table. When a particular session intends to update the rows, but the some other rows are controlled by the same bitmap index fragment, the other session has to wait until the lock is lifted. This kind of situation has a performance impact.

What can be done to reduce this kind of contention? Increasing the inittrans setting for the table helps to reduce the contention.  Another solution is to set commit_serialization to NONE for the Apply process, but this may not desirable because the idea is to adhere to commit serialization. Another method is to use the higher parallelism very selectively.