Integrated Replicat slow and does not use batchsql

(Doc ID 2347725.1)

Last updated on JANUARY 11, 2018

Applies to:

Oracle GoldenGate - Version 12.1.2.0.0 and later
Information in this document applies to any platform.

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...

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms