You can learn more about the basics of Integrated Replicat in the Integrated Replicat blog.
In Integrated Replicat, operations in one transaction are applied by a single applier. Therefore, there is no parallelism within a transaction. In other words, multiple smaller transactions work better that a single large transaction in Integrated Replicat. To optimize the parallel processing, you then would need to avoid large transactions in the first place.
However, if you can't break up the large transactions, you can use the Eager Apply feature in Oracle GoldenGate. In the DBOPTIONS INTEGRATEDPARAMS, you can configure EAGER_SIZE (in the number of LCRs) [5]. This is the threshold when Oracle GoldenGate inbound server starts to apply transactions before the commit record is received. The default EAGER_SIZE threshold is 9500. With the EAGER_SIZE configured, you technically break up large transactions into smaller ones, which are also called eager transactions. Integrated Replicat can create additional apply servers to handle outstanding eager transactions automatically and thus improves the performance.
Please also make sure you the STREAM_POOL_SIZE is properly configured on the target database to avoid waiting for memory issues when having large transactions.
Answer: No, Oracle GoldenGate Integrated Replicat keeps the transaction boundaries. However, the transaction committed orders are not maintained. Integrated Replicat applies transactions asynchronously. Transactions that do not have inter-dependencies can be safely executed and committed out of order to achieve fast throughput. Transactions with dependencies are guaranteed to be applied in the same order as on the source.[1]
You can learn more about BATCHSQL in the How to Use BatchSQL.
For Oracle GoldenGate Integrated Replicat, the following activities lead to direct apply [1]:
- DDL Changes
- Sequence Operations
- SQLEXEC
- Event Marker Interface(EMI) [3]
- Target Table with Unique Functional-based Indexes [2]
- UDT without using USENATIVEOBJSUPPORT to capture
When this happens, you will see the delivery process slows down. To improve the performance, you first would prevent the barrier operations from happening at the beginning. Please evaluate the solution with your application developers and find out if those operations are really necessary. Second, to minimize the impacts, use smaller transactions. Third, you can create a new replicat to apply the barrier transactions separately. In summary, a large number of barrier transactions in an integrated replicat process will cause performance issues because of the serializing the apply process. Try to avoid it, put it into small transactions or a separate replicat. All of these will help in this case.
Answer: Yes, this is common. You can try EAGER_APPLY and BATCHSQL to tune the performance. Try to avoid direct apply operations in large transactions.
- Making smaller transactions or use the eager apply feature
- Optimizing the processing within the transaction with BATCHSQL
- Avoiding having direct apply
- Putting direct apply operations into smaller transaction or grouping them into a separate replicat.
Let me know if you have anything to add.
- Oracle GoldenGate Documentation (12.2.0.1): 5.3 Deciding Which Apply Method to Use
- High dependency waits on updates on table with unique function based indexes (Doc ID 2242789.1)
- EVENTACTIONS in Table | MAP
- Understand Integrated Replicat Performance using the GGSCI STATS Command
- Oracle GoldenGate Documentation (12.2.0.1): A.2 Additional Parameter Options for Integrated Replicat