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