Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL

Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL

Oracle GoldenGate Integrated Replicat slow and does not use BATCHSQL.

The BATCHSQL is specified , though in the report file we do not see any

batchsql statistics:
BATCHSQL statistics:
Batch operations: 0
Batches: 0
Batches executed: 0
Queues: 0
Batches in error: 0
Normal mode operations: 11055655
Immediate flush operations: 0
PK collisions: 0
UK collisions: 0
FK collisions: 0
Thread batch groups: 0
Commits: 0
Rollbacks: 0
Queue flush calls: 0
Ops per batch: N/A
Ops per batch executed: N/A
Ops per queue: N/A
Parallel batch rate: N/A

and the replicat is very slow. Lag is 7 mins but could be max 30 secs

From the IR healthcheck report we can see that the BATCHSQL is enabled:

BATCHSQL = Y

Though from the AWR report we can see that by the goldengate operations the rows/exec is ~1 like:

Executions 	Rows Processed 	Rows per Exec 	Elapsed Time (s) 	SQL Module 		SQL  Text
============== ============== 	============= 	================ 	==========     	=================================
565,577         670,971 	0.19    	1,111.16 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
365,150 	361,452 	0.99 		586.7872.7 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
201,967 	215,429 	0.95 		482.7754.6 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...
201,195 	213,216 	0.87 		438.8554.5 	 	GoldenGate 	INSERT /*+ restrict_all_ref_c...

This is because, BATCHSQL iS directly related to the EAGER_SIZE when used in the Integrated Replicat. This is very important to note in here. We should always not assume as BATCHSQL will always improve the performance. Sometimes it degrades the performance as well. When used with Integrated Replicat process, we need to be even more careful.

For Integrated Replicat, the OGG BATCHSQL statistics are never maintained. It will always show as normal mode in the OGG report file whether BATCHSQL is enabled or not. The only way to determine if BATCHSQL is in use on the IR inbound server is to use AWR and look at the AWR SQL Executions report. If the Rows per Exec > 1, then BATCHSQL is in operation.

You can tell if BATCHSQL is configured (for the inbound server) by looking in the healthcheck summary at Replicat parameters section

Integrated Replicat
key parameters (Details).

APPLY_NAME Replicat
Name PARALLELISM MAX_PARALLELISM COMMIT_SERIALIZATION EAGER_SIZE BATCHSQL BATCH_SQL_MODE MAX_SGA_SIZE
OPTIMIZE_PROGRESS_TABLE
OGG$ROACDW ROACDW 4 50 DEPENDENT_TRANSACTIO NS 9500 Y INFINITE N

A bulk load implies that a single transaction was performed. If this is the case, then the inbound server will apply in EAGER mode. Meaning the transaction will begin applying in the inbound server before the entire transaction is received by the inbound server. Eagerly applied transactions cannot take advantage of BATCHSQL.

BATCHSQL iS directly related to the EAGER_SIZE when used in the Integrated Replicat. The default EAGER_SIZE of the in Oracle GoldenGate 12.1.x is 9500 and from OGG 12.2 is 15100.

So, if the number of operations in a Transaction is greater than 9500, then BATCHSQL will have no effect. So, the number of LCR’s should be below or lesser than 9500 (12.1) and 15000 (12.2) so that BATCHSQL will be used by the Integrated Replicat process.

If the number of transactions is greater then 9500 (12.1) or 15000 (12.2), then the Integrated Replicat will not apply the transactions in parallel and will apply it in a serialized way and hence we could see the “Rows per Exec = 1” in the “SQL ordered by executions” in AWR.

For example, if in your case, if you have most of the transactions having operations nearing to 1 Lakh, then it is better to increase the EAGER_SIZE to 100000. But please do remember, if you increase the EAGER_SIZE, you need to have sufficient amount of memory too. So, do increase the STREAMS_POOL_SIZE also.

Once you increase the EAGER_SIZE to 100000. Create a table and insert 90000 records and commit the transaction. Then update 80000 records and commit the transaction. Generate the AWR report and check if the “Rows per Exec > 1” in the “SQL ordered by executions”

Hope this clearly explained how to use BATCHSQL in Integrated Replicat process.

Cheers 🙂