Streams Pool

Sometimes conventional wisdom isn’t so conventional, or common. As a case-in-point DBAs may believe that the STREAMS pool is reserved strictly for streams processes. That isn’t the case as other Oracle utilities, such as Data Pump and GoldenGate, use that pool. Of course opting to use dynamic management will automatically allocate the required memory when a demand is made, however that memory must come from somewhere. Oracle will ‘steal’ what it needs from the buffer cache, and it won’t be replaced immediately. Let’s look at an example proving this, using Data Pump.

The ‘victim’ will be an Oracle 12.1.0.2 database configured with the streams_pool_size set to 0 (since Streams isn’t configured the expectation is the pool won’t be used) and Automatic Shared Memory Management configured (the sga_target and sga_max_size parameters are set to non-zero values):

SQL> --
SQL> -- The streams pool is NOT just for
SQL> -- Streams
SQL> --
SQL> -- Data pump and GoldenGate both use
SQL> -- it
SQL> --
SQL> -- Not setting a size for the streams
SQL> -- pool can cause problems when it is
SQL> -- first used
SQL> --
SQL> --
SQL> -- Looking at the database parameters
SQL> -- check the sga parameters
SQL> -- for sizing
SQL> --
SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     TRUE
sga_max_size                         big integer 600M
sga_target                           big integer 600M
unified_audit_sga_queue_size         integer     1048576

Checking the V$SGA_DYNAMIC_COMPONENTS view for components with non-zero current sizes the following results are returned:

SQL> 
SQL> column component format a29
SQL> set linesize 300 numwidth 12
SQL> 
SQL> select component, current_size, min_size, max_size, user_specified_size user_spec_sz,
  2  oper_count, last_oper_type, last_oper_mode, last_oper_time, granule_size
  3  from v$sga_dynamic_components
  4  where current_size > 0;

COMPONENT                     CURRENT_SIZE     MIN_SIZE     MAX_SIZE USER_SPEC_SZ   OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------- ------------ ------------ ------------ ------------ ------------ ------------- --------- --------- ------------
shared pool                      176160768    146800640    176160768            0            6 GROW          DEFERRED  15-OCT-19      4194304
large pool                         8388608      8388608    125829120            0            1 SHRINK        DEFERRED  15-OCT-19      4194304
java pool                          4194304      4194304      4194304            0            0 STATIC                                 4194304
DEFAULT buffer cache             411041792    301989888    419430400            0            8 SHRINK        DEFERRED  15-OCT-19      4194304
Shared IO Pool                    20971520            0     20971520            0            1 GROW          IMMEDIATE 15-OCT-19      4194304

SQL> 

Verifying that the streams_pool_size is set to 0:

SQL> 
SQL> --
SQL> -- Verify the streams pool is set to
SQL> -- 0
SQL> --
SQL> show parameter streams

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size                    big integer 0
SQL> 

A Data Pump export is executed and, afterwards, the dynamic memory components are checked for size:

SQL> 
SQL> --
SQL> -- Run an Data Pump export task
SQL> -- and see what happens to the streams
SQL> -- pool size
SQL> --
SQL> !expdp parfile=expdp_test.par

SQL> 
SQL> column component format a29
SQL> set linesize 300 numwidth 12
SQL> 
SQL> select component, current_size, min_size, max_size, user_specified_size user_spec_sz,
  2  oper_count, last_oper_type, last_oper_mode, last_oper_time, granule_size
  3  from v$sga_dynamic_components
  4  where current_size > 0;

COMPONENT                     CURRENT_SIZE     MIN_SIZE     MAX_SIZE USER_SPEC_SZ   OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------- ------------ ------------ ------------ ------------ ------------ ------------- --------- --------- ------------
shared pool                      197132288    146800640    197132288            0           11 GROW          IMMEDIATE 15-OCT-19      4194304
large pool                         8388608      8388608    125829120            0            1 SHRINK        DEFERRED  15-OCT-19      4194304
java pool                          4194304      4194304      4194304            0            0 STATIC                                 4194304
streams pool                       8388608            0      8388608            0            2 GROW          IMMEDIATE 15-OCT-19      4194304
DEFAULT buffer cache             381681664    301989888    419430400            0           15 SHRINK        IMMEDIATE 15-OCT-19      4194304
Shared IO Pool                    20971520            0     20971520            0            1 GROW          IMMEDIATE 15-OCT-19      4194304

6 rows selected.

SQL> 

Notice that the DEFAULT buffer cache size was reduced to 381681664 from an initial setting of 411041792, partly to help ‘fund’ the Streams pool. Testing that idea the streams_pool_size is set to 8M (the value Oracle set it to dynamically) and, to make the tests as equal as possible, the database is shut down and started:

SQL> 
SQL> --
SQL> -- Set the streams_pool_size to the current
SQL> -- value
SQL> --
SQL> -- Shutdown and startup the database
SQL> --
SQL> alter system set streams_pool_size=8M scope=spfile;

System altered.

SQL> 
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area    629145600 bytes
Fixed Size                    2927528 bytes
Variable Size               289408088 bytes
Database Buffers            331350016 bytes
Redo Buffers                  5459968 bytes
Database mounted.
Database opened.

The dynamic memory parameters checked for starting values:

SQL> 
SQL> --
SQL> -- Check dynamic sizing of SGA components
SQL> --
SQL> column component format a29
SQL> set linesize 300 numwidth 12
SQL> 
SQL> select component, current_size, min_size, max_size, user_specified_size user_spec_sz,
  2  oper_count, last_oper_type, last_oper_mode, last_oper_time, granule_size
  3  from v$sga_dynamic_components
  4  where current_size > 0;

COMPONENT                     CURRENT_SIZE     MIN_SIZE     MAX_SIZE USER_SPEC_SZ   OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------- ------------ ------------ ------------ ------------ ------------ ------------- --------- --------- ------------
shared pool                      155189248    146800640    155189248            0            2 GROW          IMMEDIATE 15-OCT-19      4194304
large pool                       125829120    125829120    125829120            0            0 STATIC                                 4194304
java pool                          4194304      4194304      4194304            0            0 STATIC                                 4194304
streams pool                       8388608      8388608      8388608      8388608            0 STATIC                                 4194304
DEFAULT buffer cache             327155712    327155712    335544320            0            2 SHRINK        IMMEDIATE 15-OCT-19      4194304

SQL> 
SQL> --
SQL> -- Remove the previous dump file
SQL> --
SQL> !/bin/rm /u01/app/oracle/admin/orcl/dpdump/scott.*

Run the Data Pump job again with the adjusted memory pool settings:

SQL> 
SQL> --
SQL> -- Run an Data Pump export task
SQL> -- and see what happens to the streams
SQL> -- pool size
SQL> --
SQL> !expdp parfile=expdp_test.par

SQL> 
SQL> column component format a29
SQL> set linesize 300 numwidth 12
SQL> 
SQL> select component, current_size, min_size, max_size, user_specified_size user_spec_sz,
  2  oper_count, last_oper_type, last_oper_mode, last_oper_time, granule_size
  3  from v$sga_dynamic_components
  4  where current_size > 0;

COMPONENT                     CURRENT_SIZE     MIN_SIZE     MAX_SIZE USER_SPEC_SZ   OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER GRANULE_SIZE
----------------------------- ------------ ------------ ------------ ------------ ------------ ------------- --------- --------- ------------
shared pool                      197132288    146800640    197132288            0           12 GROW          IMMEDIATE 15-OCT-19      4194304
large pool                         8388608      8388608    125829120            0            1 SHRINK        DEFERRED  15-OCT-19      4194304
java pool                          4194304      4194304      4194304            0            0 STATIC                                 4194304
streams pool                       8388608      8388608      8388608      8388608            0 STATIC                                 4194304
DEFAULT buffer cache             381681664    264241152    381681664            0           14 GROW          DEFERRED  15-OCT-19      4194304
Shared IO Pool                    20971520            0     20971520            0            1 GROW          IMMEDIATE 15-OCT-19      4194304

6 rows selected.

SQL> 

Notice that the DEFAULT buffer cache was increased, not decreased as in the prior example. No memory was ‘stolen’ from the buffer cache so performance did not suffer from the dynamic shifting of resources. A possible problem with setting the streams_pool_size to 0 can be performance degradation at the moment the streams pool is allocated because the buffer cache underwent a shrink at the same time the streams pool was growing. This can be especially noticeable in systems where the user load is rather heavy to begin with.

As mentioned earlier GoldenGate also uses the streams pool and, due to the heavy commit activity at the time an extract process starts, can exhibit possibly alarming degradation in service that lasts until the extract process has finished its startup activities. [Other processes spawned by GoldenGate contribute to the slowdown such as a global log file sync to flush committed data to the redo logs.] One system has suffered so badly when an extract process was started that operating system logins were unable to complete in the allotted time, causing third-party monitoring software to report that the databases running on that server were no longer available. Setting the streams_pool_size to a non-zero value contributed greatly in improving the overal performance when extract processes were started.

Common knowledge can be a double-edged sword; for every case where common knowledge holds true there might be one or more cases where it doesn’t. The only real solution is to test such ‘wisdom’ to verify it’s accuracy. It is far better to affect a test, development or ‘sandbox’ system with such investigations rather than take such ‘knowledge’ as ‘gospel’ only to discover the assumptions on which that ‘wisdom’ was based were in error. Knowing is better than guessing; a little time spent with an investigation can reap huge benefits when it comes time to implement a new process involving Oracle.

ref:https://www.databasejournal.com/oracle/dont-let-the-streams-pool-fool-you/