BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

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

PARALLEL_MAX_SERVERS

DOP 4 LARGE_POOL_SIZE

DOP 8 LARGE_POOL_SIZE

  4

   685,024 bytes

   685,024 bytes

  8

   857,056 bytes

   857,056 bytes

 16

 1,151,968 bytes

 1,545,184 bytes

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.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter