SGA and Shared Servers (MTS)
Oracle Tips by Burleson Consulting
The Shared Pool and MTS
The use of the multi-threaded server option (MTS)
in Oracle requires a sometimes-dramatic increase in the size of the
shared pool. This increase in the size of the shared pool caused by
MTS is due to the addition of the user global areas required for
sorting and message queues. If you are using MTS you should monitor
the v$sgastat values for MTS related memory areas and adjust the
shared pool memory allocations accordingly.
Note that in Oracle 8 you should make use of
the large pool feature to pull the user global areas (UGA) and
multi-threaded server queues out of the shared pool area if MTS is
being used. This prevents the fragmentation problems that have been
reported in shared pools when MTS is used without allocating the large
pool. The parallel query option (PQO) in Oracle8 also makes use of the
large pool area, depending on the number of users and degree of
parallel, the large pool may require over 200 megabytes by itself in a
Warnings about the MTS
As of 2009, I see very few Oracle shops
(well under 5%) who continue to use the MTS (unless
they have Oracle Java connections, which require MTS
Shared Servers), and it
is largely obsolete for today's 64-bit servers with
lots of inexpensive RAM resources.
Quest Software's Guy Harrison has
about using the MTS:
"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 . .
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!"
Tom Kyte notes that the MTS should not be used
without a "real reason" and he notes that shared
server connections are slower than with dedicated
database connections (Oracle's default behavior):
- "Unless you have a real reason to use MTS
- "a shared server connection is by design
"slower" than a dedicated server (more stuff
goes on, more complex) it is most likely only
getting in the way."
Large Pool Sizing
Sizing the large pool can be complex. The
large pool, if configured must be at least 600 kilobytes in size.
Usually for most MTS applications 600k is enough. However, if PQO is
also used in your Oracle8 environment then the size of the large pool
will increase dramatically. The v$sgastat dynamic performance view has
a new column in Oracle8, POOL. The POOL column in the v$sgastat view
is used to contain the pool area where that particular type of object
is being stored. By issuing a summation select against the V$SGASTAT
view a DBA can quickly determine the size of the large pool area
currently being used.
SUM(bytes) FROM V$SGASTAT WHERE pool='LARGE POOL'
GROUP BY ROLLUP(name);
The above select should be used when an
"ORA-04031:Unable to allocate 16084 bytes of shared memory ("large
pool", "unknown object", "large pool hea", "PX large pool") "
error is received during operation with a large pool configured (the
number of bytes specified may differ). When the above select is run,
the resulting summary number of bytes will indicate the current size
of the pool and show how close you are to your maximum as
specified in the initialization parameter large_pool_size. Generally
increasing the large_pool by up to 100% will eliminate the ORA-04031
Oracle8i provides for automated sizing of the
large pool. If parallel_automatic_tuning is set to TRUE or if
parallel_max_servers is set to a non-zero value then the
large_pool_size will be calculated, however, it can be over-ridden
with a manually specified entry in the initialization file.
ORA-27102: Out of Memory
error is received when you set either of
these parameters (or both) you must either manually set
large_pool_size or reduce the value for
following formula determines the set point for the large_pool_size if
it is not manually set:
* DOP Degree of Parallel calculated from
#CPU/NODE * #NODES
* I Number of threads/CPU
* PEMS Parallel execution message size set
with PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter, usually
defaults to 2k or 4k but can be larger
* USERS Number of concurrent users using
For a 2k PEMS with 4 concurrent users for a
steadily increasing value for DOP the memory size is a quadratic
function ranging from around 4 meg for 10 CPUs to 120 meg with 70
CPUs. This memory requirement is demonstrated in Figure 15.36.
Figure 8: Example Chart for 2k PEMS and 4
Concurrent Users Showing Memory Requirements as Number of CPUs
On my NT4.0 Oracle8i, 8.1.3 test system I have
2 CPUs, set at 2 threads per cpu (DOP of 4) and then 4 threads per CPU
(DOP of 8), message buffer of 4k and I performed multiple tests
increasing the parallel_max_servers initialization parameter to see
what the resulting increase in large_pool_size would be, the results
are shown in Table 17.
DOP 4 large_pool_size
DOP 8 large_pool_size
Table 17: Large Pool Size as a Function of
Parallel Max Servers and DOP
Notice that for a small number of CPUs the
large pool size increase from an increase in parallel max servers
isn't affected by changes in the number of parallel threads until the
value of threads is large in respect to the number of CPUs.
For non-PQO systems a general rule of thumb is
5K of memory for each MTS user for the large pool area.
Guideline 5: In Oracle7when using MTS increase
the shared pool size to accommodate MTS messaging and queuing as well
as UGA requirements. In Oracle8 use the Large Pool to prevent MTS from
effecting the shared pool areas. If using PQO the large pool is
required in Oracle8.
This is an excerpt from
the eBook "Oracle
DBA made Simple".
For more details on Oracle
database administration, see the "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam. Its
only $19.95 when you buy it directly from the publisher