 |
|
Oracle parallel_automatic_tuning
|
Oracle8i
allows the large pool to be sized automatically. 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 an entry, manually specified, in the initialization
file. Indeed, if an ORA-27102: Out of Memory error is received when
either of these parameters (or both) is set, the large_pool_size
must be manually set or the value for parallel_max_servers must be
reduced. The following formula determines the set point for the
large_pool_size, if it is not set manually:
(DOP^2*(4I-1)+2*DOP*3+4*DOP(I-1))*PEMS*USERS
Where
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
parallel query
A 2k PEMS with 4 concurrent users results in a
steadily increasing value for DOP. The memory size is a quadratic
function ranging from around 4 MB for 10 CPUs, to 120 MB with 70
CPUs. This memory requirement is demonstrated in Figure 2.4.
On a NT4.0 Oracle8i, 8.1.3 test system there
are 2 CPUs, set first at 2 threads per CPU (DOP of 4), and then at 4
threads per CPU (DOP of 8), with a message buffer of 4k. Multiple
tests were performed, increasing the parallel_max_servers initialization
parameter to see what the resulting increase in large_pool_size would
be. The results were as follows:
PARALLEL_MAX_SERVERS DOP 4 LARGE_POOL_SIZE DOP
8 LARGE_POOL_SIZE
4 685,024
bytes 685,024 bytes
857,056
bytes 857,056 bytes
16 1,151,968
bytes 1,545,184 bytes
Notice that for a small number of CPUs, the
increase in the large pool size resulting from an increase in
parallel max servers isn't affected by changes in the number of
parallel threads, until the value of the threads is large with
respect to the number of CPUs.
For non-PQO systems, a general rule of thumb is
to allocate 5K of memory in the large pool area for each MTS user.
The Oracle script collection contains more than 600 working Oracle scripts. You can download them immediately at this link:
|
