Streams Pool is only for Streams? Think Again!

If you don’t use the automatic SGA (i.e. set the sga_target=0) - something I frequently do - and don’t use Streams, you probably have set the parameter streams_pool_size to 0 or not set it at all, since you reckon that the pool is used for Streams alone and therefore would be irrelevant in your environment wasting memory.

But did you know that the Streams Pool is not just for Streams and it is used for other tools some of which are frequently used in almost any database environment? Take for instance, Data Pump. It uses Streams Pool, contrary to conventional wisdom. If Streams Pool is not defined, it is dynamically allocated by stealing that much memory from the buffer cache. And the size is not reset back to zero after the demand for the pool is over. You should be aware of this lesser known fact as it reduces the buffer cache you had allocated to the instance earlier.

Demonstration

Let’s examine this with an example. First, let’s check the various pools defined in the database instance right now:

SQL> show parameter sga_target

NAME TYPE VALUE
------------------------------------ ----------- -----
sga_target big integer 0

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 300M

SQL> show parameter streams_pool_size

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

Note carefully the values of the following parameters:

• sga_target = 0 --> this means the SGA is not auto tuned.
• db_cache_size = 300M --> this is the buffer cache
• streams_pool_size = 0 --> this is the stream pool, set to 0 as expected

Now kick off a Data Pump Export (expdp) job:

$ expdp directory=DATA_FILE_DIR tables=arup.t1

... output truncated ...

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:49:16

After the Data Pump job is complete, check the size of the buffer cache again:

SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- -----
db_cache_size big integer 280M

The buffer cache got compressed from 300 MB earlier to 280 MB. But you didn’t do that; Oracle did it.

Well, where did the 20 MB of missing memory go? Now, check the size of the Streams Pool:

SQL> show parameter streams_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
streams_pool_size big integer 20M

The Streams Pool was 0 earlier, as you intended it to be; but Oracle allocated 20 MB to it by stealing that much memory from the buffer cache. The reason: the Streams Pool was used for the Data Pump Export job, even though it does not sound intuitive. If you check the alert log, you will see the activity recorded there:

$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Thu Apr 18 14:44:46 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/opt/oracle"
adrci> set homepath diag/rdbms/d112d2/D112D2
adrci> show alert -tail –f

Here are the excerpts from the alert log:

2013-04-18 14:48:45.581000 -04:00
streams_pool_size defaulting to 20971520. Trying to get it from Buffer Cache for process 27378.

The next question you may be wondering about is – why did Oracle decide to give only 20 MB to the Streams Pool? Why not 100 MB, or 10 MB? Is it dependent on the size of the table being exported? The answer is no.

Oracle by default gives 10% of the size of the shared pool to the Streams Pool. Let me find out the size of the shared pool:

SQL> show parameter shared_pool_size

NAME TYPE VALUE
------------------------------------ ----------- -----
shared_pool_size big integer 200M

The shared pool is 200 MB. 10% of that is 20 MB, which is how much was assigned to the Streams Pool. That size is not dependent on the size of the exported data; but the size of the shared pool.

It's important to understand that the shared pool is used to compute the default size of the streams pool; the actual memory is carved out of buffer cache; not the shared pool.

If you check the database’s operations, you will be able to confirm Oracle’s adjustment of the pools:

SQL> select component, oper_type, parameter, initial_size, target_size, final_size
2 from v$sga_resize_ops
3 order by start_time;

COMPONENT OPER_TYPE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE
--------------- ------------- -------------------- ------------ ----------- ----------
DEFAULT buffer STATIC db_cache_size 0 314572800 314572800
cache
DEFAULT buffer SHRINK db_cache_size 314572800 293601280 293601280
cache
streams pool GROW streams_pool_size 0 20971520 20971520

The output has been truncated to show only the relevant records. From the output you can see clearly that the buffer cache was defined statically as 314572800, or 300 MB initially. Later the buffer cache shrank from 314572800 to 293601280 (about 280 MB). The amount of shrinkage was 314572800 - 293601280 = 20971520 (or, 20 MB), the exact amount the streams_pool_size was allocated.

Why this is a problem? Well, the biggest problem is that the buffer cache size is now reduced without your knowledge. The buffer cache lost 10% of the shared pool. But systems with large shared pool, it could be substantial. Worse, the amount allocated to Streams Pool remains there; it is not returned to the buffer cache as you might expect. You have to manually give it back:

SQL> alter system set streams_pool_size = 0;

In case of a RAC database, it’s possible that only one instance sees this change in Streams Pool size; the other instances will be unaffected.

It would be prudent to note here that this surprise occurs when you do not use automatic SGA settings. When auto SGA is used, i.e. sga_target is set to a non-zero value, you give up complete control to Oracle to manipulate the memory structures. In that case Oracle juggles the memory between various pools – including Streams Pool - without your control anyway.

While it is not very well known, this behavior is not undocumented. It’s mentioned in the Utilities Guide at http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_perf.htm#SUTIL973.

Conclusion

Just because you haven’t defined the streams_pool_size parameter as you don’t use Streams doesn't mean that Oracle will not assign some memory to Streams Pool. Data Pump, which is frequently used in many databases, uses the Streams Pool and Oracle will assign it as 10% of the size of the shared pool and reduce the buffer cache by that amount to fund the memory for the Streams Pool. So you should configure the Streams Pool, even if you don’t use Streams, so that Data Pump can use a precisely allocated pool it rather than stealing it from the Buffer Cache. If you don’t do that now, or don’t intend to do it, then regularly check the streams_pool_size value and set it to zero if it is not so.