There are a number of factors that we need to keep in mind to be able to successfully tune a GoldenGate Setup. If you carefully design your system keeping in mind the end goal and apply some proven performance tuning measures you should be able to get real time replication from your GoldenGate setup.
Below are some of the key points that we found helpful when doing Performance Tuning for GoldenGate.
1) Have a very clear definition of Baseline
Before we can start tuning, we must record our baseline. See when load on your application peaks and take multiple readings of key database performance parameters like CPU/Memory/cursor usage /Transactions count/ etc. Generate and save AWR reports also for that time.
Involve your Application Stress testing team to simulate data and transactions in an exact Production replica.
2) Think “Parallel”
If you have a lot of tables to replicate, do not just put all of them in single extract/replicate process. Divide those large number of tables among parallel processes and trail files. This will certainly help the system in performing better.
We have also seen that increasing parallelism on the Replicat (target) side give better results than on the Extract (source) side.
You increase parallelism on the 12c GoldenGate by adding below kind of parameter in the parameter file:
DBOPTIONS INTEGRATEDPARAMS (parallelism 6)
And always keep in mind parallelism will increase the CPU/Memory consumption so keep monitoring those parameters closely.
3) Study and Optimize Online Redo Logs
It is very important that Redo logs should be arranged in such a way that GoldenGate performance can be optimized. Redo logs are constantly read and write making them first candidate of applying Tuning methods.
- Keep redo logs on fast disk (example SSD, Flash Disk) wherever possible
- Ensure adequate number of redo log groups are configured
- Read your AWR carefully, see if any redo log related events(example ‘log file sync’) are showing up.
- Incorrect RAID settings of Disk for Redo logs is another issue. Recommended is RAID1+0 (mirrored striped sets) for these files and RAID 5 is least recommended.
4) Understand the data in tables carefully
Does your table have any LOB (BLOB, CLOB, NLOB). LOB data types store large blocks of unstructured data in the binary or character format and it is a good idea that such tables having LOB type data should have separate Extract, data pump, and Replicat process group so that we can increase throughput.
Does your tables have primary key. Without a key on a table with multiple columns can definitely increase the redo log generation.
5) Avoid Contention at Trail file level
According to Oracle’s best practice for every remote trail file there should not be more than three Replicat processes that read the same remote trail file. So it is better to pair each Replicat with its own trail file and Extract process.
6) Verify if Network is your culprit
If network is performing poorly, you will get high latency, possible disconnections and poor performance. All this will lead to data pump process unable to write to the remote trail quickly enough.
Work with your network team, see if OS TCP socket buffers can be adjusted to higher value, TCP packet size can be changed etc. Usually OS default parameters are defined towards lower value and definitely not optimized for applications like GoldenGate.
7) Check if your applications are hosted on over-utilized hardware
If you have a very busy system which is already utilizing the hardware resources to the max, then it may prove helpful if some of those hardware parameters value can be increased to accommodate the GoldenGate resource demands. Closely monitor your CPU Consumption/ Memory usage / I/O contentions.
8) Plan to install latest GoldenGate Release
At the time of writing this post 12cR2 is latest GoldenGate release. This release has some new features like integrated Replicat which speed up the data apply feature and is a good performance improvement technique.
9) Use new Integrated Replicat features that are present in 12c version
You can configure the Replicat processes to commit asynchronously at the session level by using commit_wait = ‘NOWAIT’ parameter setting. This will ensure that Oracle will not wait for a commit to succeed before allowing the session to continue
That can be setup by using SQLEXEC statement in each parameter file:
SQLEXEC "alter session set commit_wait = 'NOWAIT'";
you can also explore some other parameters like BATCHTRANSOPS, BATCHSQL_MODE which also help boost the replicat speed.
Be aware of this parameter though. The GoldenGate 19c documentation states:
“Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.”
10) Calculate transaction size on the Source side and tweak parameters to increase throughput
Use GoldenGate’s LOGDUMP utility to identify the transaction size on source database
If required, Increase EAGER_SIZE inbound server parameter for the integrated Replicat to value slightly higher than the transaction size.
Example:
DBOPTIONS INTEGRATEDPARAMS (EAGER_SIZE 18000)
11) If you get stuck somewhere, don’t forget to trace
Tracing enables additional information to be gathered from a given process and do help sometimes when you are simply not able to figure out what process is slow or what table is bottleneck.
Oracle GoldenGate 12c offers a similar tracing mechanism through its trace and trace2 options. “trace” provides processing information, whereas “trace2” identifies the processes with wait events.
Trace will help you collect some key parameters like: table name, operation name/type/number, average/maximum waits.
References / Read More
Note 1557031.1 Recommended patches for Integrated Capture/Replicat
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
http://www.oracle.com/technetwork/database/availability/maa-gg-performance-1969630.pdf
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
Great pointers,
I would also suggest the following additions;
1) Use BATCHSQL
2) Use compress and passthrou parameterts on pump
3) use the lag GETTCPSTATS to assess the status of the network lags. Change values for TCPBUFSIZE and TCPFLUSHBYTES
I know this is an old post, but I would like to point out that modifying the “commit_wait” parameter to “NOWAIT” could lead to data loss. The GoldenGate 19c documentation states thusly:
“Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.”
Thanks. Updated the post.