Memory requirement for Oracle GoldenGate Integrated Extract

Introduction

 This document outlines the memory requirement for GoldenGate Integrated Extract.

Main Article

The shared memory that is used by Integrated Extract(log mining server) comes from the “STREAMS POOL” portion of the System Global Area (SGA) in the database. Therefore, you must set  it high enough to keep enough memory available for the number of Extract processes that you expect to run in Integrated  mode against a given database instance. Note that “STREAMS POOL” is also used by other components of the database (like AQ and Streams), so make certain to take them into account when sizing the “STREAMS POOL” for Oracle GoldenGate. This database parameter setting must be saved to disk in the init.ora file or an spfile.

By default, one Integrated Extract requests the logmining server to run with MAX_SGA_SIZE of 1GB. Thus, if you are running three Extracts in Integrated mode in the same database instance, you need at least 3 GB of memory allocated to the “STREAMS POOL”. As an Oracle GoldenGate best practice, it is recommended to keep an additional 25 percent of memory in  the “STREAMS POOL” available for other components.

 

Example 1:

In this example assumes a 1 GB default memory allocation for three Extracts in Integrated mode:

(# of Extracts)*(1 GB) = Minimum OGG Required “STREAMS POOL” Memory

3*(1 GB) = 3 GB

(OGG Required “STREAMS POOL” Memory) * .25 = 25% additional Memory

3 GB * .25 = .75 GB

3 GB + .75 GB = 3.75 GB (total)

To set this value use the following statement:

SQL> alter system set STREAMS_POOL_SIZE=3750M scope=both;

 

Example 2:

In this example the max memory allocation per Integrated Extract is being set to 2 GB per process and therefore requires the use of an additional Oracle GoldenGate parameter (MAX_SGA_SIZE) in the extract parameter file

(# of Extracts)*(2 GB) = Minimum OGG Required “STREAMS POOL” Memory

3*(2 GB) = 6 GB

(OGG Required “STREAMS POOL” Memory) * .25 = 25% additional Memory

6 GB * .25 = 1.5 GB

6 GB + 1.5 GB = 7.5 GB (total)

To set this value use the following statement:

SQL> alter system set STREAMS_POOL_SIZE=7500M scope=both:

Note: MAX_SGA_SIZE is defined in megabytes here.

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 2048, parallelism 3)

 

Also please note that this parameter “MAX_SGA_SIZE” is not the same as the database parameter “SGA_MAX_SIZE”  and that this parameter “MAX_SGA_SIZE”  is used to only define how much of the SGA pool to use for the Oracle GoldenGate Integrated Extract process. 

 

If the STREAMS_POOL_SIZE is set smaller than 1 GB then Integrated Extract will consume 75% of the memory allocated by this setting.

If the STEAMS_POOL_SIZE is not set then the MAX_SGA_SIZE takes 10% of the SHARED_POOL_SIZE up to a maximum of 1 GB.

 

If dynamic SGA is turned on, query the “V$SGA_DYNAMIC_COMPOMENTS” view to get the allocated memory value for “STREAMS POOL”. Likewise for all other memory component of SGA.

For example:

Select current_size from v$sga_dynamic_components where component = ‘streams pool’;

 

Summary

As part of this best practice always configure the SGA component “STREAMS_POOL_SIZE”  if you are using Integrated Extract. The memory allocation for Integrated Extract ultimately depends on the GoldenGate parameter “MAX_SGA_SIZE” which gets its allocated memory from the “STREAMS POOL” or “SHARED POOL” depending on if the “STREAMS POOL” value is configured.

Add Your Comment