Confessions of an Oracle Database Junkie - Arup Nanda
The opinions expressed here are mine and mine alone. They may not necessarily reflect that of my employers and customers - both past or present. The comments left by the reviewers are theirs alone and may not reflect my opinion whether implied or not. None of the advice is warranted to be free of errors and ommision. Please use at your own risk and after thorough testing in your environment.

Pages

Friday, April 19, 2013

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 justfor 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:

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:

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:

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.

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.