The Shared Pool and MTS
Oracle Tips by Burleson Consulting
The use of the multithreaded 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 (UGAs) 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 and greater, if MTS is
being used, you should make use of the large pool feature to pull the
user global areas and MTS queues out of the shared-pool area. This
prevents the fragmentation problems that have been reported in shared
pools when MTS is used without allocating the large pool.
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, 600 is enough. However, if PQO
(parallel query option) is also used in your Oracle8,Oracle8i, or
Oracle9i environment, then the size of the large pool will increase
dramatically. The V$SGASTAT dynamic performance view has a new column
in Oracle8 called POOL. It 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 Remote 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 percent will eliminate the ORA-04031
In an undocumented (as of this writing) bug in
220.127.116.11 if you have large complex SQL statements, such as a 15 table
join, and nothing to force large pool usage you can get excessive CPU
times, ORA-04031 and ORA_01037 errors. If you get ORA-04031, or
ORA-01037 errors and aren't using the large pool try turning on
parallel query by setting the parallel query processes to minimum
values to force use of the large pool.
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 nonzero value, then the
LARGE_POOL_SIZE will be calculated; however, it can be overridden with
a manually specified entry in the initialization file. Indeed, if an
“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 PARALLEL_MAX_SERVERS. The
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 the PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter;
this usually defaults to 2K or 4K but can be larger.
USERS. Number of concurrent users using
For a 2D PEMS with four concurrent users, for
a steadily increasing value for DOP, the memory size is a quadratic
function ranging from around 4 meg with 10 CPUs to 120 meg with 70
CPUs. This memory requirement is demonstrated in Figure 13.3.
Figure 13.3 Charting 2K PEMS and four
concurrent users, showing memory requirements as number of CPUs
On my NT4.0 Oracle8i, 8.1.3 test system, I
have two CPUs, set at two threads per CPU (DOP of 4) and four 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. Those
results are shown in Table 13.1
Table 13.1 Parallel Max Servers versus Large
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 Oracle7,when 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
affecting the shared-pool areas. If using PQO, the large pool is
required in Oracle8.
Code Depot for Full Scripts
||This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.