Golden Gate Performance Tuning

Golden Gate Performance Tuning is the process of optimizing the performance of Oracle Golden Gate replication. It can be done by adjusting the configuration parameters, using the right hardware, and optimizing the data flow. 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

• Quick Tips To Improve Golden Gate Performance

• Determine Current Golden Gate Performance

• Configure Golden Gate Lag Alerts

• Batch Transactions

• Range Splitting

Quick Tips To Improve Golden Gate Performance

• 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 the network

• Perform data conversion on the replicat side

• Do not use DDL INCLUDE ALL until required

• 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. The default size is 30,000 bytes

RMTHOST targetserver, MGRPORT 7809, TCPBUFSIZE 10000000, COMPRESS

Determine Current Golden Gate Performance

The first step is to identify what is the current performance standards of extract and replicat. Let's query to find lag for integrated extract

SELECT capture_name, 
(86400*(available_message_create_timecapture_message_create_time)) lag_in_seconds
FROM GV$GOLDENGATE_CAPTURE;

Query to find lag for integrated replicat

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

Configure Golden Gate 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

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 Log Miner process

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 trnx can be split into a range. 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));