| |
 |
|
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 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...
See code depot
This is an excerpt by
Mike Ault’s e-book:
Tuning
Third-party Vendor Oracle Systems
Tuning when you can't touch the code
ISBN:
0-9740716-3-3
http://www.rampant-books.com/ebook_vendor_tune.htm
|
