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/