Oracle GoldenGate uses LogMiner [1] for both Integrated Capture and Delivery. Let's talk about how you optimize the logminer for Oracle GoldenGate replications.
STREAM_POOL_SIZE
STREAM_POOL_SIZE [4] is a database initialization parameter and is part of Oracle Database SGA (System Global Area). It is the memory used by logminer server. Therefore, you need to allocate enough memory size Oracle GoldenGate Replication. By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB. As an Oracle GoldenGate best practice, it is recommended to keep an additional 25% of memory for other processing.[3][5] Therefore, if you can calculate the STREAM_POOL_SIZE as follows:
STREAM_POOL_SIZE
STREAM_POOL_SIZE [4] is a database initialization parameter and is part of Oracle Database SGA (System Global Area). It is the memory used by logminer server. Therefore, you need to allocate enough memory size Oracle GoldenGate Replication. By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB. As an Oracle GoldenGate best practice, it is recommended to keep an additional 25% of memory for other processing.[3][5] Therefore, if you can calculate the STREAM_POOL_SIZE as follows:
- MAX_SGA_SIZE * (Number of Extract)* (Parallelism in Extract) *1.25
Integrated Extract/Replicat- MAX_SGA_SIZE and PARALLELISM
The following are two extract parameters that can be used to optimize the logminer processing:
The following are two extract parameters that can be used to optimize the logminer processing:
- MAX_SGA_SIZE – controls the amount of shared memory used by the LogMiner server. The default value is 1GB and, in most cases, this is adequate.
- PARALLELISM – controls the number of LogMiner preparer (LMP) server processes used by the LogMiner server. The default value for Oracle Database Enterprise Edition is 2 and is adequate for most workloads.
The following is an example, we have two extracts configure each has parallelism as 2 and the MAX_SGA_SIZE to be 1 GB:
Extract: TRANLOGOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2) Replicat: DBOPTIONS INTEGRATEDPARAMS (MAX_SGA_SIZE 1024, parallelism 2)
The STREAM_POOL_SIZE is:
- 1GB * 2 * 2 * 1.25 = 5GB
You can change the STREAM_POOL_SIZE as follows:
SQL> alter system set STREAMS_POOL_SIZE=5000M scope=both;
Resources
- Oracle Documentation (11.2) - 19 Using LogMiner to Analyze Redo Log Files
- Oracle GoldenGate Performance Best Practices, November 2014
- Memory requirement for Oracle GoldenGate Integrated Extract, May 14, 2013
- Oracle Database 12.2 Reference - 1.314 STREAMS_POOL_SIZE
- Oracle GoldenGate Documentation 12.2 - 3.5 Managing Server Resources