• Arun Kumar

Golden Gate Performance Tuning Tips

In this article we will be looking at few of the Golden Gate performance tuning tips which will help you find the lag and fix it.


Things We Know

  • Use table level filters to extract required tables

  • Use column level filters to extract required columns

  • Run extract and replicat in Integrated mode if DBs are Oracle

  • Perform data filter at pump level so less data is sent over network

  • Perform data conversion on the replicat side

  • Do not use DDL INCLUDE ALL until required

Determine Current Performance


Query to find lag for integrated extract

SQL > SELECT capture_name, (86400*(available_message_create_timecapture_message_create_time)) lag_in_secondsFROM GV$GOLDENGATE_CAPTURE;

Query to find lag for integrated replicat

SQL > SELECT r.apply_name, (86400*(r.dequeue_time -c.lwm_message_create_time)) latency_in_secondsFROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# AND r.apply_name= c.apply_name


Configure Lag Alerts


You can add lag parameters to manager parameter file to capture lag alerts in ggserror.log file

  • LAGREPORTMINUTES 5

  • LAGINFOMINUTES 5

  • LAGCRITICALMINUTES 15

You can write shell script to read ggserror.log and send lag notifications


Few Best Practices

  • Enable supplemental logging –TRANDATA or SCHEMATRANDATA

  • If using oracle DBs, configure integrated extract and replicat

  • Use PASSTHRU in data pump when no filtering is used

You can increase the packet size of data sent to RMTHOST and also compress it while sending

  • Default size 30,000 byes

  • RMTHOST targetserver, MGRPORT 7809, TCPBUFSIZE 10000000, COMPRESS


Batch Transactions

  • Replicat applies transactions one by one

  • This can cause performance issues as replicat is not able to apply trnx as fast as extract is sending

  • Using BATCHSQL, you can group similar SQLs in batch and then apply together

  • Optimizing Stream Pool Usage by Golden Gate

  • Integrated Ext / Rep uses Oracle streams pool

  • Check AWR for high waits on LogMinerprocess

  • By default, GG extract will eat up 80% of SGA size. This can cause performance issues in source database

  • You can limit the amount of SGA to be used by GG extract using below in extract parameter file

TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2560, PARALLELISM 3)

Parallelism 3 will configure 3 log miner service. Default is 2


Range Splitting


A large table with high volume trnxcan be range split. You can assign these ranges to multiple extracts / replicat for load sharing


Replicat 1:


MAP SCOTT.SALES TARGET FOX.SALES, FILTER (@ RANGE (1, 2, WORKID));

Replicat 2:

MAP SCOTT.SALES TARGET FOX.SALES, FILTER (@ RANGE (2, 2, WORKID));


Recent Posts

See All

Install Oracle 12c Golden Gate on Oracle Linux

In this article we will look at the steps to install oracle 12c Golden Gate on Oracle Enterprise Linux 6.5. The steps involved are: Virtual Machine Setup Install Oracle 12c Database Install Oracle 12c

Single Table Replication – Same Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is same on both source and target database. Create Sample Table Create Fox user on both pr

Single Table Replication – Different Schema

In this article we will be looking at single table replication using Oracle Golden Gate where the schema name is different on source and target but table structure remains same. Create Sample Table Cr

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis