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.
Then Plan to Restart database for this parameter.
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 )

 

 1,203 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply