Integrated Replicat slow and does not use batchsql (Doc ID 2347725.1)

Last updated on MAY 22, 2023

Applies to:

Symptoms

Oracle GoldenGate Delivery for Oracle
Version 12.1.2.0.0 17185003 18478409_FBO
Solaris, sparc, 64bit (optimized), Oracle 11g on May 1 2014 08:51:51

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 1.19 1,111.16 GoldenGate INSERT
/*+ restrict_all_ref_c...
365,150 361,452 0.99 586.78 72.7 GoldenGate INSERT
/*+ restrict_all_ref_c...
201,967 215,429 1.07 482.77 54.6 GoldenGate INSERT
/*+ restrict_all_ref_c...
201,195 213,216 1.06 438.85 54.5 GoldenGate INSERT
/*+ restrict_all_ref_c...

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.