Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

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:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

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


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks