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 1000+ 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 Connor 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.

Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

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