Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

streams_pool_size defaulting to 179516211 in oracle alert log

Posted by Hendry chinnapparaj on June 16, 2011

streams_pool_size defaulting to 179516211 in oracle alert log

 

Problem

———-

The streams_pool_size dynamically changed from 0 to 179516211

and the following were recorded in the oracle alert log indicating issues related to data pump.

 

streams_pool_size defaulting to 179516211

kupprdp: master process DM00 started with pid=61, OS id=39190678

to execute – SYS.KUPM$MCP.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’, ‘KUPC$C_1_20110516040004’, ‘KUPC$S_1_20110516040004’, 0);

kupprdp: worker process DW01 started with worker id=1, pid=115, OS id=14024736

to execute – SYS.KUPW$WORKER.MAIN(‘SYS_EXPORT_FULL_01’, ‘OPS$ORACLE’);

Mon May 16 04:29:12 2011

streams_pool_size defaulting to 179516211

streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  streams_pool_size        = 0

alert_zoeprd.log.110521:streams_pool_size defaulting to 179516211

alert_zoeprd.log.110521:  streams_pool_size        = 184549376

 

alert_zoeprd.log.110514:  db_cache_size            = 1291845632

alert_zoeprd.log.110521:  db_cache_size            = 1107296256

 

 

Cause

———

Oracle11g  doc has the following explanation

Setting the Size Of the Buffer Cache In a Streams Environment

Oracle Data Pump uses Streams functionality to communicate between processes. If the SGA_TARGET initialization parameter is set, then the STREAMS_POOL_SIZE initialization parameter is automatically set to a reasonable value.

If the SGA_TARGET initialization parameter is not set and the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of the size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly. A minimum size of 10M is recommended for STREAMS_POOL_SIZE in order to ensure successful Data Pump operations.

Oracle10g has the details

Oracle’s Automatic Shared Memory Management feature manages the size of the Streams pool when the SGA_TARGET initialization parameter is set to a nonzero value. 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.

 

Setting the Size Of the Buffer Cache In a Streams Environment

As of Oracle Database 10g release 10.2, if Streams functionality is used, but the STREAMS_POOL_SIZE initialization parameter is not defined, then the size of the streams pool automatically defaults to 10% of size of the shared pool.

When the streams pool is created, the required SGA memory is taken from memory allocated to the buffer cache, reducing the size of the cache to less than what was specified by the DB_CACHE_SIZE initialization parameter. This means that if the buffer cache was configured with only the minimal required SGA, then Data Pump operations may not work properly.

Here it doesn’t mention the minimum requirement of 10m.

Fix

—-

If you are using data pump, then increase the size of streams_pool_size to atleast 10m as recommended by oracle and increase the buffer_cache_size to it’s original size

Alter system set streams_pool_size=10m scope=spfile sid=’*’;

Alter system set db_cache_size=1291845632 scope=spfile sid=’*’;

And bounce the database

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: