Updates and Inserts (or mainly DMLs) in GG are taking long time to execute
The top few reasons for DMLs to run long are,
- Heavy transactions are being processed at Source
- DB performance may be a reason too
- Unique/Primary Keys missing
To Overcome the above issues, there are couple of possible solutions, which I’ve implemented and noticed a significant improvement in performance
- Enable BATCHSQL in Replicat processes
- Configure Multiple Replicat processes for a single Trail file
Just a little detailed explanation of BATCHSQL and Multiple Replicat Processes,
- By default Replicat operates in Normal mode
- Replicat batches together SQL statements that affect the same table
- Operation type supported by BATCHSQL is Insert, Update or Delete
- In contrast, using the BATCHSQL parameter with a default OPSPERBATCH value of 1200
- Oracle recommends regularly checking the process report file and statistics to make sure few transactions are reverting back to normal mode if an exception occurs
Oracle claims that the performance of BATCHSQL is even more if we use Intergrated Replicat feature.
We could configure multiple replicat processes reading the same Trail files and split the work load. This is a very good performance booster.
These pictures are taken from Oracle Documentation.