Skip to Main Content
  • Questions
  • Commit point optimization and Golden Gate

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, lh.

Asked: February 19, 2021 - 12:06 pm UTC

Last updated: March 04, 2021 - 3:50 am UTC

Version: rdbms: 19.8

Viewed 100+ times

You Asked

Hi

We have a rather complicated cleaning process. We have implemented it so that we divide key space to 4 separate number spaces and run them concurrently. Each of the 4 processes make decision if that row is to be deleted and if so deletes the row and dependent rows by cascade delete. Because there were occasional deadlocks each deletion is in its own transaction. Code is PL/SQL, so no waits for writing redo logs should be happening (commit point optimization).

However in some point of time replication of database was changed from materialized view to Golden Gate (volumes got much bigger and materialized views had serious difficulties in handling them).

Now we have encountered issues with Golden Gate, lags of replication when large deletes are done.

My question is: if in source system commit point optimization is used, it is also applied in target system ? Should we try to increase interval between commits so number of waits in redo logs writes in target system would get smaller ? Something else ?


lh

and we said...

I don't think this is going to (solely) related to commit point optimisation, but more that GoldenGate is not going to see batched deletes as "batches" because the redo stream is not going to reflect that.

A single delete removing 1000 records will yield 1000 redo records each being a "delete row", which is how they will be manifested at the target node.

You could explore the BATCHSQL mode to see if this helps.

https://docs.oracle.com/en/middleware/goldengate/core/12.3.0.1/gwurf/batchsql.html#GUID-2ED88418-6ACB-484D-B140-364232EC419A

or perhaps isolate this table(s) into its own group so that its replication latency doesn't create system wide latency.

Historically, a common technique for large batch operations was to temporarily halt GG, apply the batch on both source and target, and then resume GG. MOS note 1451675.1 has some information and controlling structures around that process.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database