• 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

SQL Data-Type conversion functions

SQL conversion functions are single row functions designed to alter the nature of the data type of a column value, expression, or literal. TO_CHAR, TO_NUMBER, and TO_DATE are the three most widely use

Automatic SQL Tuning in Oracle

Database optimizer runs very fast and must select the best execution plan for a query within fraction of seconds. Due to time constraint (under normal query execution), sometimes optimizer will choose

Top resource intensive SQL

Database performance is a major concern for a DBA. SQLs are the ones which needs proper DB management in order to execute well. At times the application team might tell you that the database is runnin

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback