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));