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 Large pool sizing

Oracle Tips by Burleson

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

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 DBA can quickly determine the size of the large pool area currently being used.

See Code Depot

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.

 


This is an excerpt by Mike Ault’s book “Oracle9i Administration & Management” .  If you want more current Oracle tips by Mike Ault, check out his new book “Mike Ault’s Oracle Internals Monitoring & Tuning Scripts” or Ault’s Oracle Scripts Download.

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
 

Hit Counter