Understanding Key Updates and Deletes & Memory Management in Oracle GoldenGate 12.2

Understanding Key Updates and Deletes & Memory Management in Oracle GoldenGate 12.2

Oracle GoldenGate 12.2 might not be replicating update and delete statements the way you think it is.  A single update statement that affects 500,000 rows does not get replicated as a single update statement, but as 500,000 individual update statements each referenced by the row’s primary key.  If you are doing bi-directional replication and capturing before images you will be collecting 500,000 before images and 500,000 update statements resulting in 1,000,000 entries in the trail file in one transaction.

Get more Oracle GoldenGate Tips and Tricks here!

So, where is all of this done?  In memory of course.  GoldenGate can potentially take a significant amount of memory to pull together this transaction before adding it to the trail file.  In fact, by default GoldenGate can use as much as 64 GB of RAM before it starts paging out.  The amount of memory that GoldenGate will allocate is set when the process starts.  For an extract GoldenGate looks at the amount of Virtual memory available (RAM + Paging File) and configures itself from that.

This information can be found in the extract report file.

CACHEMGR virtual memory values (may have been adjusted)
CACHEPAGEOUTSIZE (default):               8M
PROCESS VM AVAIL FROM OS (min):         128G
CACHESIZEMAX (strict force to disk):     96G

The PROCESS VM AVAIL FROM OS (min) is used to internally set the CACHMGR memory.  If this is insufficient you will begin paging within GoldenGate and you will experience performance problems.

In some older versions of GoldenGate and some older operating systems GoldenGate will be paged out by the OS.  If there is insufficient swap space it can be disastrous.

The GoldenGate memory manager will page out to the ./dirtmp directory unless otherwise directed to.  The size of the GoldenGate memory usage and where it pages to is defined by the CACHEMGR parameter.  If you want to set the cache size to 4 GB (it has to be a power of 2) with the paging files to go to ./dirpage you will use the following parameters:

CACHEMGR CACHESIZE 4GB, CACHEDIRECTORY ./dirtmp

If you wanted to split the cache directory into two different mount points you will use the following parameters:

CACHEMGR CACHESIZE 4GB, CACHEDIRECTORY /u02/GoldenGate/dirtmp 16GB, CACHEDIRECTORY /u03/GoldenGate/dirtmp 16GB

Since the entire transaction is held in memory for the duration of the transaction and because transactions can become very large, especially if a large number of rows are being updated or deleted and if before images are being captured.

It is important to know how GoldenGate might consume large amounts of memory and be prepared for it.  Remember, the default for CACHEMGR is 64GB.  That is a lot of RAM.

By |2017-09-20T08:12:54+00:00April 25th, 2016|Performance Wire|Comments Off on Understanding Key Updates and Deletes & Memory Management in Oracle GoldenGate 12.2