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 PQO environment.

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 this warning 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!"

Oracle's 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 -- don't."
  
• "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.

SELECT name, 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 errors.

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.

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^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

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 Increases

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.

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.  It?s only $19.95 when you buy it directly from the publisher here.