GoldenGate: How to manage the Streams Pool in the Oracle Database?

April 29, 2024 | 4 minute read
Volker Kuhr
Senior Principal Product Manager
Text Size 100%:

Rather than addressing the Streams Pool explicitly, use Automated Memory Management.  

With integrated Extract and Replicat, there are additional components within the Oracle Database that needs database attention. One of those was about the Streams Pool, a portion of SGA memory used for processing Logical Change records.

Recently, there were changes of how GoldenGate utilizes the Streams Pool: Now, it is only needed by Downstream Capture, Integrated Replicat, and XStream.

For Per-PDB Extract and Parallel Replicat, there is no need to set the Streams Pool parameter (streams_pool_size) in the Oracle Database explicitly. Rather than setting the streams_pool_size, it is more efficient to do not explicitly set the parameter to eliminate any wasting of memory.  

The following table summarizes components and the use of Streams Pool:

Process Type

Use of
Streams Pool

Remark  

Integrated Extract(*)
(Per-PDB-Level)

NO

Per-PDB Extract is the recommended Extract to use.

Integrated Extract
(Root-Level)

YES

Root-Level-Extract is only needed by Downstream Capture.
By default, up to 1G is allocated from the Streams Pool.

Classic Replicat

NO

 

Coordinated Replicat

NO

 

Parallel Replicat
(non-integrated mode)

NO

 

Parallel Replicat
(integrated mode)

NO

 

Integrated Replicat

YES

It is recommended to use Parallel Replicat rather than integrated Replicat.
If Integrated Replicat still in use:
  Set parameter MAX_SGA_SIZE to limit Streams Pool utilization.

XStream-Out

YES

Set parameter MAX_SGA_SIZE to limit Streams Pool utilization.

XStream-In

YES

Set parameter MAX_SGA_SIZE to limit Streams Pool utilization.

(*) Oracle 19c: Oracle Database Release Update 19.23
      Oracle 21c:  Oracle Database Release 21c with bug fix 35364793.

  • With Automatic (Shared) Memory Management enabled (recommended), no minimum memory is needed for the Streams Pool – There is no need to set the STREAMS_POOL_SIZE database parameter for GoldenGate.
  •  Without Automatic (Shared) Memory Management enabled, the Streams Pool can allocate up to 10% of the Shared Pool if the STREAMS_POOL_SIZE is not set and Root-Level Extract, Integrated Replicat or XStream are in use.

If using Root-Level Extract or integrated Replicat, the size of the Streams Pool depends on the MAX_SGA_SIZE parameter defined in the Extract and Replicat parameter file:

  • For Root-Level Extract, the default MAX_SGA_SIZE is 1G.
  • For Integrated Replicat, the default MAX_SGA_SIZE is unbound (INFINITE).
    Especially if there are multiple processes, it is recommended to set the MAX_SGA_SIZE for each Integrated Replicat.
  • For XStream-Out, you set the MAX_SGA_SIZE with dbms_capture_adm.set_parameter and dbms_apply_adm.set_parameter to 1G each.
    For XStream-In, you set MAX_SGA_SIZE with
     dbms_apply_adm.set_parameter to 1G.

The size of Streams Pool size should be 25% higher than the sum of the MAX_SGA_SIZE settings of Root-Level Extract, Integrated Replicats, and XStream processes.
 

Volker Kuhr

Senior Principal Product Manager

Volker is a Senior Principal Product Manager working in the GoldenGate Development group.
His primary focus is on the GoldenGate Core Product, mainly GoldenGate for Oracle. Key topics are Performance, High Availability, Security, and Resilience.
Volker has worked for more than 20 years in the field of database technology and data replication.
He has supported customers worldwide in different industries to develop & manage distributed database systems/applications and build Data Integration Solutions.


Previous Post

OCI GoldenGate: Capture a topic data from Confluent Cloud to Autonomous Database

Madhu Kumar S | 12 min read

Next Post


Hands-On Guide with Security: Provisioning GoldenGate/Database in a Private Subnet and Access via Bastion Service

Ravi Gupta | 2 min read