GoldenGate Performance Tuning: Extract, Pump, and Replicat Optimization

GoldenGate replication has three stages, and each one fails differently under load: Extract can fall behind on redo mining, the Pump/Distribution path can saturate the network, and Replicat can bottleneck applying changes to the target. Tuning the wrong stage wastes time and rarely moves the needle — the first job is always figuring out where the lag actually is.

1. Locate the Bottleneck Before Touching Parameters

Every tuning exercise starts with LAG and INFO, not with changing parameters blindly.

GGSCI> SEND EXTRACT EXT1, GETLAG
GGSCI> LAG EXTRACT EXT1
GGSCI> LAG REPLICAT REP1

GGSCI> STATS EXTRACT EXT1, TOTALSONLY *
GGSCI> STATS REPLICAT REP1, TOTALSONLY *

Two lag numbers matter, and they point at different problems:

In Microservices deployments, the Performance Metrics Service exposes the same data over REST, which is easier to feed into a dashboard than parsing GGSCI output:

curl -u oggadmin:Password1 \
  http://localhost:9011/services/v2/extracts/EXT1/statistics

2. Extract-Side Tuning

Integrated Extract Parallelism

On 19c+, Integrated Extract can mine redo in parallel via the logmining server. This is the single biggest lever for high-volume OLTP sources.

-- Check current mining session parallelism
SELECT CAPTURE_NAME, PARALLELISM, STATUS
FROM V$GOLDENGATE_CAPTURE;

-- Increase mining parallelism (source-side, requires DBA privilege)
BEGIN
  DBMS_CAPTURE_ADM.SET_PARAMETER(
    capture_name => 'OGG$CAP_EXT1',
    parameter    => 'parallelism',
    value        => '4');
END;
/

Reduce Unnecessary Capture Work

Every extra table or column captured costs mining throughput. Scope TABLE statements tightly and use COLSEXCEPT rather than capturing wide tables you don’t need in full:

TABLE hr.employees, COLSEXCEPT (large_notes_column, audit_blob);

Checkpoint and Read Buffer Tuning

-- ext1.prm
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 512, PARALLELISM 4)
CHECKPOINTSECS 10
DBOPTIONS ALLOWUNUSEDCOLUMN

CHECKPOINTSECS trades checkpoint overhead against recovery time on restart — 10-30 seconds is a reasonable middle ground for most production sources. Going too low adds I/O overhead for no real benefit; going too high increases how much Extract has to re-mine after a crash.

Undo Retention

Long-running transactions plus insufficient undo retention cause ORA-01555 on the mining session, which stalls Extract entirely, not just slows it down. If your source has long transactions, verify undo retention is sized for the longest expected transaction, not just the default:

SELECT tablespace_name, retention FROM dba_tablespaces
WHERE tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');

3. Trail Files and the Distribution Path

Trail File Sizing

Trail files that are too small generate excessive file-switch overhead; too large delays Pump/Replicat catching up after a restart because more data has to be reread.

EXTTRAIL ./dirdat/lt, MEGABYTES 500

500MB-1GB is a reasonable default for most OLTP workloads. Bump it to 2GB+ only for very high-throughput pipelines where file-switch frequency is itself measurable overhead.

Network Compression

For WAN-distributed topologies (cross-region DR, cross-cloud replication), compression on the Distribution Service or Pump extract materially reduces bandwidth cost at a modest CPU tax:

-- In the Distribution Service path, or classic Pump parameter file
RMTHOST target-host, MGRPORT 7809, COMPRESS

Benchmark this — compression helps most when the network is the bottleneck (cross-region), and helps least when CPU on the sending host is already the constraint (e.g., a heavily loaded Exadata compute node also running Extract).

Parallel Pump Paths

A single Pump process serializes everything flowing to a target. For high-throughput pipelines, splitting into multiple Pumps by table range or schema avoids a single-threaded distribution bottleneck:

GGSCI> ADD EXTRACT PMP1, EXTTRAILSOURCE ./dirdat/lt
GGSCI> ADD RMTTRAIL ./dirdat/rt, EXTRACT PMP1
-- PMP1 handles schema A, PMP2 handles schema B, etc.

4. Replicat-Side Tuning

This is where most real-world bottlenecks live, because apply on the target is usually more expensive than capture on the source (constraint checks, index maintenance, trigger overhead).

Parallel / Coordinated Replicat

Classic Replicat is single-threaded per process. Coordinated Replicat splits apply across multiple threads while preserving transactional dependency ordering:

-- rep1.prm
REPLICAT REP1
COORDINATED MAXTHREADS 8
MAP source.*, TARGET target.*;

On 19c+, Parallel Replicat goes further, using an in-memory dependency graph to apply non-conflicting transactions concurrently without manual thread-to-table mapping:

GGSCI> ADD REPLICAT REP1, PARALLEL, EXTTRAIL ./dirdat/rt

Parallel Replicat is generally the better default on modern versions — Coordinated Replicat still has a place when you need explicit control over which threads handle which tables (e.g., to avoid hot-row contention on a specific table).

BATCHSQL

Batches multiple DML operations into fewer round trips to the target database. Large win for high-volume inserts/updates with low conflict rates:

REPLICAT REP1
BATCHSQL BATCHTRANSOPS 1000, BATCHESPERQUEUE 10

Turn this off (or reduce batch size) if the target table has heavy trigger logic or foreign key constraints that behave poorly under batched apply — batching can obscure which individual statement failed, complicating troubleshooting.

Target-Side Indexing

Replicat apply performance is bound by target index maintenance as much as anything GoldenGate-side. Two common fixes:

• Ensure every table being replicated into has a primary key or unique index GoldenGate can use for UPDATE/DELETE row lookups — without one, GoldenGate falls back to full-table-scan-per-row logic (KEYCOLS can help if no real key exists).
• Drop non-essential secondary indexes during large initial loads, rebuild after.

-- Explicit key mapping when no PK/UK is usable
MAP source.orders, TARGET target.orders, KEYCOLS (order_id, order_date);

Handling Long-Running or Skewed Transactions

A single large source transaction (bulk update touching millions of rows) will serialize apply on that transaction regardless of parallelism settings elsewhere, since GoldenGate preserves transactional boundaries by default. There’s no tuning parameter that fixes this — it has to be addressed at the source, either by breaking large batch jobs into smaller committed chunks or by accepting that lag will spike during those windows and sizing SLAs accordingly.

5. Quick Reference: Parameter Impact

6. A Worked Example

A pipeline replicating an OLTP order-processing schema (200 tables, ~15M row changes/day) was showing steady Replicat lag growth during nightly batch windows only.

1. LAG confirmed Extract was flat; Replicat lag climbed from 09:00 UTC batch start.
2. STATS REPLICAT showed apply throughput capped well below Extract’s capture rate.
3. Target-side investigation found the order_line_items table had no usable key for 40% of the row volume — GoldenGate was falling back to full-row-image matching.
4. Adding explicit KEYCOLS mapping plus switching from Coordinated to Parallel Replicat closed the gap; lag during batch windows dropped from ~45 minutes to under 3.

The fix wasn’t a GoldenGate parameter in isolation — it was diagnosing that the bottleneck was target-side row lookup cost, which no amount of Extract tuning would have touched.

Summary

Tune in this order: confirm which side is actually lagging, fix Extract mining parallelism and undo retention if the source is the bottleneck, tune trail size and compression if the network path is the bottleneck, and reach for Parallel Replicat, BATCHSQL, and target-side keys/indexes if apply is the bottleneck. Changing Replicat parameters when Extract is the actual constraint (or vice versa) is the most common wasted-effort mistake in GoldenGate tuning.