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.

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 percent will eliminate the ORA-04031 errors.

In an undocumented (as of this writing) bug in 8.1.7.1 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^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 the PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter; this usually defaults to 2K or 4K but can be larger.

USERS. Number of concurrent users using parallel query.

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 increases

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 Pool Size

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.

 
See 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.