Streams Pool Size in Oracle

I will explain Streams Pool Size in Oracle in this post.

Streams Pool in Oracle

If you use the Oracle’s Automatic Shared Memory Management feature, then Automatic Shared Memory Management manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value.

STREAMS_POOL_SIZE in Oracle

If the STREAMS_POOL_SIZE initialization parameter also is set to a nonzero value, then Automatic Shared Memory Management uses this value as a minimum for the Streams pool.

If the STREAMS_POOL_SIZE initialization parameter is set to a nonzero value, and the SGA_TARGET parameter is set to 0 (zero), then the Streams pool size is the value specified by the STREAMS_POOL_SIZE parameter, in bytes.

If both the STREAMS_POOL_SIZE and the SGA_TARGET initialization parameters are set to 0 (zero), then, by default, the first use of Streams in a database transfers an amount of memory equal to 10% of the shared pool from the buffer cache to the Streams pool.

You can check the Streams_pool_size , SGA and Shared_pool as follows.

SQL> show parameter stream

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL> 
SQL> 
SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 40G
sga_target big integer 40G
unified_audit_sga_queue_size integer 1048576
SQL>
SQL> 
SQL> 
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 1134139801
shared_pool_size big integer 16G
SQL>

How to check streams pool size in Oracle ?

You can use the V$STREAMS_POOL_STATISTICS view to display information about the current Streams pool usage percentage as follows.

SQL> desc V$STREAMS_POOL_STATISTICS;
Name Null? Type
----------------------------------------- -------- ----------------------------
TOTAL_MEMORY_ALLOCATED NUMBER
CURRENT_SIZE NUMBER
SGA_TARGET_VALUE NUMBER
SHRINK_PHASE NUMBER
ADVICE_DISABLED NUMBER
CON_ID NUMBER

SQL>


SQL> set lines 1000
SQL> select * from V$STREAMS_POOL_STATISTICS

TOTAL_MEMORY_ALLOCATED CURRENT_SIZE SGA_TARGET_VALUE SHRINK_PHASE ADVICE_DISABLED CON_ID
---------------------- ------------ ---------------- ------------ --------------- ----------
55504 268435456 4.2950E+10 0 0 0

SQL>

If you got the following error, then you should set the Streams_pool_size.

ORA-04031: unable to allocate nn bytes of shared memory

You can set streams_pool_size parameter as follows.

SQL> alter system set streams_pool_size=4G scope=spfile sid='*';

System altered.

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )