MTS + AMM + BULK COLLECT = Trouble!

Most databases don't run with shared servers - A.K.A. Multi-Threaded Servers or MTS - nowadays.   While reducing the number of server processes can reduce overall memory demand and sometimes certain forms of contention, the drawbacks - particularly delays caused when all serves are busy - are generally regarded as greater than the advantages. 

MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.  When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences. 

When you allocate PL/SQL program memory with dedicated servers, the memory comes out of the PGA.  If you exceed your fair share of PGA allocation, you'll receive an ORA-4030 error:

 

BULK COLLECT for large tables is generally not a good idea because of the memory required;  it's far better to bulk fetch in increments using the LIMIT keyword, as in this snippet:

In fact, it's not necessary to explicitly code the LOOP with LIMIT from 10g onwards providing that PLSQL_OPTIMIZE_LEVEL is set to the default of 2 or higher.  With PLSQL_OPTIMIZE_LEVEL>=2, the PL/SQL compiler will transparently rewrite simple loops to use bulk collect with a LIMIT clause of 100.  For instance the statement below will have exactly the same performance profile as the statement above:

 

As counter-productive as a BULK COLLECT without LIMIT is in any circumstances, it becomes actively dangerous when using MTS and either Automatic Shared Memory Management (ASMM) or 11g Automatic Memory Management (AMM). 

When using dedicated servers, memory for PL/SQL variables is allocated from the Program Global Area (PGA).  However, with MTS the memory is allocated from the large pool.  If you try to allocate a very large collection when connected via MTS - and AMM or ASMM is enabled - then Oracle will expand the size of the large pool to make room for your collection.  

Here's what happens when we run the first BULK COLLECT example when using MTS and AMM (image courtesy of Spotlight on Oracle):

 That's right: AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request.  First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!  Sessions that may have been doing buffered IO or large sorts may experience severe degradation as in-memory sorts go to disk, and buffered IOs fail to find anything in the buffer cache.  

If you have a diagnostic pack license, you can see the same thing in OEM in the Memory advisors page:

Conclusion

  1. Don't use MTS unless you have a very good reason.  Not only does it have it's own issues, but it works poorly with Automatic memory management (AMM or ASMM).
  2. Don't use SELECT ... BULK COLLECT INTO unless the table is very small.  An explicit cursor with a LIMIT clause is preferable, and if PLSQL_OPTIMIZE_LEVEL is set to defaults, you often don't need to worry too much about bulk collect at all, since it is automatically implemented (with an array size of 100) by the PLSQL compiler
  3. When using AMM or ASMM beware of one memory region "starving" another.  The best way to avoid this is to set minimum values for specific memory areas.  For instance, you could avoid the phenomenon above if you set a value for PGA_AGGREGATE_TARGET and DB_CACHE_SIZE.  When AMM is in effect, these values will serve as minimum values which will avoid either area shrinking to absurdly small values.  We have a wizard in Spotlight on Oracle that can help you set these minimum values.