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