Memory requirement for Oracle GoldenGate Integrated Extract

In Oracle GoldenGate when using Integrated Mode, STREAMS_POOL plays an Vital role. The Integrated process, takes the shared memory from the “STREAMS POOL”.
STREAMS POOL is one of the memory components of the SGA. It is given as STREAMS_POOL_SIZE. The STREAMS_POOL_SIZE should be sized according to the number of Integrated Extracts used in the Database System. We should also take considerations of other process which uses the STREAMS POOL in the database.

 

By default, Each Integrated Extract process requests the logmining sever to run with MAX_SGA_SIZE of 1G. MAX_SGA_SIZE is the “STREAMS POOL SIZE”. While Configuring the Integrated Extract we use to give the below parameter.,

TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 100, parallelism 1)

In the above parameter, the max_sga_size represents the STREAMS POOL SIZE and not the SGA_MAX_SIZE parameter of the database.

There are three cases we need to consider while sizing the STREAMS POOL SIZE in a GoldenGate Integrated Mode Environment.

  1. If the STREAMS_POOL_SIZE is greater than 1G, then the max_sga_size equals to 1G. Else it will automatically take 75% of the STREAMS_POOL_SIZE.
  2. If the STEAMS_POOL_SIZE is not set then the MAX_SGA_SIZE (Streams Size) takes 10% of the SHARED_POOL_SIZE up to a maximum of 1 GB.
    So we need to consider the size of the SHARED POOL for this.
  3. If you are using the Dynamic SGA, then you can get the current utilization of the STREAMS POOL by querying the view V$SGA_DYNAMIC_COMPONENTS. Using this view you can
    know the current utilized size of all the Memory Components of the SGA.

 

A simple test example is given below for more understanding.

We are setting the value of the MAX_SGA_SIZE to 500M and the STREAMS_POOL_SIZE is 300M. The Integrated Extract ABENDS with the below error.,

2014-11-25 07:39:34 ERROR OGG-02050 Not enough database memory to honor requested MAX_SGA_SIZE of 500.
2014-11-25 07:39:34 ERROR OGG-01668 PROCESS ABENDING.

So edit the SGA_MAX_SIZE value to some around 250 and started the Extract. The Extract Process was running fine.

You can also get the below error, when you are using Multiple Integrated Extract processes and suddenly if you lag with the STREAMS_POOL_SIZE or when adding new Integrated Extract process
when the STREAMS_POOL_SIZE does not have enough space to allocate to the newly added Extract.,

2014-11-25 03:44:11 ERROR OGG-02077 Extract encountered a read error in the asynchronous reader thread and is abending: Error code 4031, error message: ORA-04031: unable to allocate bytes of shared memory (“”,””,””,””).

So When using the Integrated Mode, STREAMS_POOL_SIZE should be considered much.

SIZING the STREAMS_POOL_SIZE

If you are using 1 Extract and allocating 1G per Extract Process, then the STREAMS_POOL_SIZE should be as below.,

OGG_Min_Streams_Mem = (No of Extract) x (Size)
= 1 x 1
= 1GB

Additional Memory for other process

Add_Streams_Mem = (No of Extract) x .25
= 1 * .25 = .25
So Total_Streams_Memory = OGG_Min_Streams_Mem + Add_Streams_Mem
= 1 + .25 = 1.25 GB

In Similar way, If suppose you are using 3 Extracts and allocating 2G per Extract process, then the STREAMS_POOL_SIZE should be as below.,

OGG_Min_Streams_Mem = (No of Extract) x (Size)
= 3 x 2
= 6GB

Additional Memory for other process

Add_Streams_Mem = (No of Extract) x .25
= 3 * .25 = .75
Total_Streams_Memory = OGG_Min_Streams_Mem + Add_Streams_Mem
= 6 + .75 = 6.75 GB

Command to change the STREAMS_POOL_SIZE in database is below,

alter system set STREAMS_POOL_SIZE = 6912M scope=both;

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *