| |
 |
|
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 Oracle 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.
This is an excerpt by Mike Ault’s book “Oracle
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. |
