Skip to content

Redo, trail volume, and apply lag by key strategy in Oracle GoldenGate: a controlled empirical study

I have written twice about what happens when a table replicated with GoldenGate has no primary key. I covered the common misconceptions about row identification and the risk of migrating tables with no primary key. Both explain why a missing key can be a real risk, but I often get asked what a missing primary key costs. Let’s address these concerns, with different key strategies, multiple metrics for analysis and different variables in the experiment to balance the analysis.

Four key strategies

Every scenario replicates the same table under one of four key strategies:

  • PK: the table has a primary key.
  • UNIQUE_INDEX: no primary key, but a unique index. GoldenGate selects it automatically as the key.
  • KEYCOLS: no primary key and no unique index, but the KEYCOLS parameter is added to name the columns to use as the key.
  • No key: scenario where no key can be identified. These are the bad_column cases of DBA_GOLDENGATE_NOT_UNIQUE, described in GoldenGate row identification and LOB replication and primary keys. The extract uses every column as a pseudo-key.

Variables changed

The baseline is a table replication with 50 columns, 100,000 rows before replication, and 10,000 transactions with a 33:33:34 insert:update:delete mix. Each comparison changes one variable and keeps the others at baseline. I decided to test four variables:

  • Table width: 5, 50 (baseline) and 250 columns, to check which metrics are impacted by the number of columns.
  • DML mix: insert-only (100:0:0), update-only (0:100:0), delete-only (0:0:100) and the 33:33:34 baseline, to check which operation type pays the cost of a missing key.
  • Transaction volume: 10,000 (baseline) and 100,000 transactions, to check whether the cost scales linearly with the amount of work.
  • Table size: 100,000 (baseline) and 1,000,000 rows, to check whether the number of existing rows impacts the replication cost.

Metrics observed

Each test run recorded four metrics:

Questions about your own setup? A dbi services consultant can look at your case.

Talk to an expert (opens in a new tab)
  • Redo generated: bytes of redo written by the workload session (redo size in V$MYSTAT). DBAs worry about the redo that replication adds to the source.
  • Redo entries: number of redo entries written by the same session (redo entries). It shows whether the redo is made of more entries or of larger ones.
  • Trail bytes: bytes the extract writes to the trail.
  • Apply lag: time the replicat needs to apply the whole workload, from its start to the end of the apply.

Of course, other metrics could be gathered and analyzed. I wanted to focus this analysis on the ones I usually get asked about. All tests were run sequentially, to avoid concurrency issues, and randomized, to make sure that no scenario benefits from a warm cache or from a quieter moment on the server. Each scenario and key strategy was run 10 times, for around 40 hours of compute time in total.

Methodology

  • Database: Oracle 19c, two CDBs on one host. The source PDB is in CDB01, the target PDB in CDB02.
  • GoldenGate: 26ai Microservices. Each run creates a dedicated extract, trail and replicat, then deletes them.
  • Workload: INSERT adds a row, UPDATE changes one non-key column, DELETE removes a row. Every operation is committed individually.
  • Supplemental logging: added with ADD TRANDATA on every table, through the REST API. PK and UNIQUE_INDEX log their key columns.
  • Tables without a key: every column is logged, because primary key supplemental logging falls back to all columns.
  • KEYCOLS: ADD TRANDATA also adds a log group on ID_COL, but the fallback still applies. KEYCOLS and no key generate the same redo. KEYCOLS only changes the trail content and how the replicat finds the row.
  • Repeats: each scenario and key strategy runs 10 times. One pass covers all of them before the next repeat.
  • Capture and apply: the replicat starts only once the extract has finished capturing, so capture and apply never overlap and the apply lag measures the apply phase alone.
  • Statistics: the graphs display the mean, the standard deviation and the minimum and maximum of the 10 runs of each scenario and key strategy.

Each chart has one row per key strategy. The box is the mean plus or minus one standard deviation, the whisker runs from the minimum to the maximum and the line is the mean.

Baseline: does key strategy matter?

Here are the metrics for the baseline: a table with 50 columns, 100,000 rows and 10,000 transactions with an equal DML mix.

Key strategyRedoRedo entriesTrailApply lag
PK15.0 MB33.2k11.1 MB11.1 s
UNIQUE_INDEX15.0 MB33.2k11.1 MB11.2 s
KEYCOLS15.5 MB23.1k15.2 MB21.3 s
No key15.5 MB23.1k19.4 MB21.2 s
  • PK and UNIQUE_INDEX give the same results on every metric, and so do KEYCOLS and no key, except for the trail volume.
  • Trail volume is the only metric where KEYCOLS and no key differ: KEYCOLS reduces it by 21%, but it stays 37% above the scenario where a PK is used.
  • KEYCOLS has no impact on the redo generated, which is the same as no key (see supplemental logging).
  • With a PK or UNIQUE_INDEX, apply lag is 47% lower and redo is 3% lower than with KEYCOLS or no key.
  • A PK or UNIQUE_INDEX adds 43% more redo entries than KEYCOLS or no key, because of the index maintenance.

With KEYCOLS or no key, the replicat takes about twice as long to apply the same workload. The trail is 37% larger with KEYCOLS and 75% larger with no key. The redo on the source barely changes.

Does a wider table cost more?

Here is each metric for three different tables: 5, 50 (baseline) and 250 columns.

ColumnsKey strategyRedoRedo entriesTrailApply lag
5PK10.6 MB30.3k3.3 MB11.1 s
5KEYCOLS8.5 MB20.2k3.6 MB11.2 s
5No key8.5 MB20.2k4.0 MB11.1 s
50PK15.0 MB33.2k11.1 MB11.1 s
50KEYCOLS15.5 MB23.1k15.2 MB21.3 s
50No key15.5 MB23.1k19.4 MB21.2 s
250PK34.7 MB46.1k45.7 MB12.4 s
250KEYCOLS46.5 MB36.7k67.2 MB83.2 s
250No key46.5 MB36.7k88.3 MB85.2 s

A missing key costs more as the table gets wider. At 5 columns it costs nothing on apply lag. At 250 columns the apply lag is about 7 times higher, the trail almost twice as large and the redo 34% higher.

Why the redo gap changes with the table width

When the table width increases, two costs go in opposite directions.

  • Index maintenance: INSERT and DELETE on a keyed table add or remove a B-tree entry. The cost is fixed per operation. UPDATE never changes the index here.
  • Full-row logging: a no-key table logs every column on each UPDATE. The cost grows with the column count.

At 5 columns the fixed index cost dominates and no key generates 20% less redo than PK. At 50 columns no key is 3% above, and at 250 columns it is 34% above.

The entry counts match. PK writes more entries than no key at every width, because of the index: 50% more at 5 columns, 43% at 50 and 26% at 250.

Apply lag at 250 columns is noisy for KEYCOLS and no key (coefficient of variation 38% to 47%). Their medians are about 6 times that of PK.

Which workload type pays for a missing key?

INSERT has no row to identify, so only the index maintenance cost applies. UPDATE and DELETE identify a row.

WorkloadKey strategyRedoRedo entriesTrailApply lag
Insert-onlyPK18.7 MB35.1k15.2 MB11.1 s
Insert-onlyKEYCOLS15.2 MB24.9k15.2 MB11.2 s
Insert-onlyNo key15.2 MB24.9k15.2 MB11.1 s
Update-onlyPK8.0 MB20.0k2.8 MB11.1 s
Update-onlyKEYCOLS16.4 MB20.2k15.4 MB30.4 s
Update-onlyNo key16.4 MB20.2k27.9 MB31.4 s
Delete-onlyPK17.9 MB42.8k15.1 MB11.1 s
Delete-onlyKEYCOLS14.5 MB22.8k15.1 MB31.4 s
Delete-onlyNo key14.5 MB22.8k15.1 MB31.6 s
  • Insert-only: a key costs 23% more redo, from the index entries. Trail bytes and apply lag are identical.
  • Delete-only: a key costs 24% more redo, for the same reason. Apply lag is almost 3 times higher without a key.
  • Update-only: no key generates twice the redo of a key. Trail bytes are 5.5 times larger with KEYCOLS and 10 times larger with no key than with PK.

INSERT pays nothing: apply lag and trail are the same with or without a key. DELETE makes the apply almost 3 times slower, and UPDATE is the worst case, with a trail 5.5 to 10 times larger and twice the redo. The cost of a missing key falls entirely on the statements that have to find an existing row.

Does the cost scale with the workload?

Each transaction is independent. Every metric should scale by 10x from 10,000 to 100,000 transactions.

Key strategyRedoTrailApply lag
PK10.23x9.99x1.9x
UNIQUE_INDEX10.23x9.99x1.9x
KEYCOLS10.20x10.00x6.8x
No key10.20x10.00x7.5x
  • Redo and trail bytes scale linearly for every key strategy.
  • Apply lag grows 7x for KEYCOLS, 7.5x for no key and 2x for PK.

The gap grows faster than the volume: with 10 times more transactions, apply lag only doubles with a key but is multiplied by 7 without one, so a missing key goes from about 2 times slower to about 7 times slower.

Does the number of rows change the cost?

Each run changes a fixed number of rows. The table size should not change redo, trail bytes or apply lag.

Key strategyRedo, 100k to 1M rowsTrail, 100k to 1M rowsApply lag, 100k to 1M rows
PK15.0 to 14.9 MB11.1 to 11.2 MB11.1 to 11.1 s
UNIQUE_INDEX15.0 to 14.9 MB11.1 to 11.2 MB11.2 to 11.1 s
KEYCOLS15.5 to 15.5 MB15.2 to 15.4 MB21.3 to 200.5 s
No key15.5 to 15.5 MB19.4 to 19.6 MB21.2 to 229.5 s
  • PK and UNIQUE_INDEX are unchanged, as expected.
  • Redo does not change with table size for any key strategy.
  • Apply lag is 9 times higher for KEYCOLS and 11 times higher for no key. The likely cause is that their target tables have no index on the key columns and are 10 times larger.

With a key, table size does not matter. Without one, a table 10 times larger makes the apply 9 times slower with KEYCOLS and 11 times slower with no key, because the replicat has to search the table for every row it changes.

Conclusion

  • As expected, PK and UNIQUE_INDEX are the same for GoldenGate: redo, trail and apply lag are the same in every scenario tested. A unique index is as good as a primary key.
  • No key costs apply lag and trail volume:
    • Compared with a PK, the apply is about twice as slow at baseline and the trail is 75% larger.
    • The gap grows with table width, workload volume and table size: the apply is up to 20 times slower, and the trail is 10 times larger on an update-only workload.
    • It comes entirely from UPDATE and DELETE: INSERT costs nothing on apply lag.
    • Redo stays within a few percent at baseline. It is 34% higher at 250 columns, but 20% lower on narrow tables and insert-only workloads, because there is no index to maintain.
  • KEYCOLS is not a workaround for a missing key:
    • Compared with a PK, the apply is about twice as slow at baseline and the trail is 37% larger.
    • Compared with no key, the only gain is a smaller trail (21% at baseline). Redo and apply lag do not improve.

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Julien Delattre

Julien Delattre

Julien Delattre, member of the Oracle Team Zurich, has more than five years of experience specializing in Oracle databases, plus expertise in MariaDB and MongoDB. Prior to dbi services, he was a Database Administrator at Atos in Angers, France, and a Data Scientist at Air France in Paris and Apurata in Lima. Julien holds a Master's degree in Computer Science, Data Science, and Statistics from Centrale Paris / Université Paris-Saclay.View Author posts

83 posts since October 2025

Most read in Oracle

The Oracle posts our readers open most: the all-time favourites, and what is trending right now.

Leave a Comment

Your email address will not be published. Required fields are marked *