Oracle Golden Gate - Integrated or Classic mode - 12.2 - Tuning
Find lag for integrated extract:
SELECT capture_name, (86400*(available_message_create_timecapture_message_create_time)) lag_in_seconds FROM GV$GOLDENGATE_CAPTURE;
Find lag for integrated replicate:
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;
GG Error log:
You can add lag parameters to the manager parameter file to capture lag alerts in the ggserror.log file
LAGREPORTMINUTES 5
LAGINFOMINUTES 5
LAGCRITICALMINUTES 15
Best Practices: Configuring Oracle GoldenGate for the Best Performance :
CPUs to enable parallelism :
Integrated Extract/Replicat- MAX_SGA_SIZE and PARALLELISM
Extract:
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)
Replicat:
DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)
STREAM_POOL_SIZE :
alter system set STREAMS_POOL_SIZE=3G scope=both;
By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB.
MAX_SGA_SIZE * (Number of Extract)* (Parallelism in Extract) *1.25
A high-throughput network with the optimized TCP configurations:Example:
RMTHOST gg_hostname, MGRPORT 7840, TCPBUFSIZE 3000000, TCPFLUSHBYTES 3000000
Refer:
Excessive LAG on OGG Extract Data Pump Sending Data over WAN, Calculate TCP TCPBUFSIZE TCPFLUSHBYTES (Doc ID 1071892.1)
Compressed trail files if the network is constrained:
RMTHOST gg_hostname, MGRPORT 7840,TCPBUFSIZE 3000000, TCPFLUSHBYTES 3000000,COMPRESS
Best Practice:
Enable supplemental logging –TRANDATA or SCHEMATRANDATA
If using oracle DBs, configure integrated extract and replicate
Use PASSTHRU in data pump when no filtering is used
Default packet size of data sent to RMTHOST :30,000 byes
Use table level filters to extract required tables
Perform data filter at pump level so less data is sent over the network
Perform data conversion on the replicate side
Do not use DDL INCLUDE ALL until required
Fast Disk I/Os to speed up the trail files read/write
Batch Transactions:
Replicat applies transactions one by one
Check AWR for high waits on LogMinerprocess
By default, GG extract will eat up 80% of SGA size. This can cause performance issues in the source database.
Limit the amount of SGA to be used by GG extract using below in extract parameter file
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2560, PARALLELISM 2)
Parallelism 2 will configure 2 log miner service. Default is 2
TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 2560, PARALLELISM 3)
Parallelism 3 will configure 3 log miner service. Default is 2
RANGE and THREAD/THREADRANGE Coordinated Delivery - 12c:
Splitting Large Table with Coordinated Delivery:
replicat.prm
MAP jay.trade, TARGET rpt.trade, THREADRANGE(1-3, TRADEID));
The key benefits of coordinated replication are:
A single parameter file
Automatic control of the replication order and to ensure the correctness of the transaction splitting process.
Enhancements to facilitate error recovery.
OR
splitting large tables into multiple Delivery processes using RANGE:
REP1.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (1, 3, TRADEID));
REP2.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (2, 3, TRADEID));
REP3.PRM:MAP jay.trade, TARGET rpt.trade,FILTER (@RANGE (3, 3, TRADEID));
Note: THREAD and THREADRANGE may be used with wildcard MAP statements.
Reference:
Note 1448324.1 OGG Integrated Healthcheck Script
Note 1488668.1 GoldenGate Performance Data Gathering
Note 1596493.1 GoldenGate Integration with AWR
Note 1485620.1 Best Practices Downstream Capture
MAA white paper Oracle GoldenGate Performance Best Practices
https://www.oracle.com/technetwork/database/availability/maa-gg-performance-1969630.pdf
