Question:
I am trying to expand by shared_pool
size and I get the ORA-04033 error:
SQL> alter system set shared_pool_size=10g
ORA-02097: parameter cannot be modified because specified value
is invalid
ORA-04033: Insufficient memory to grow pool
How do I fix the ORA-04033 error?
Answer:
As the ORA-04033 message suggests, you do not have enough RAM
allocated to the SGA to allow you to create a 10 gig shared pool.
The
oerr utility shows this for the ORA-04033 error, which is not
very helpful:
ORA-04033: Insufficient memory to grow pool
Cause: The system had insufficient memory to grow the pool to
the specified size.
Action: Specify a smaller value to grow
the pool.
The ORA-04030 fix depends on your release of Oracle and you use of
the
sga_max_size parameter and whether you have deployed
Automatic Shared Memory Management (ASMM), a mechanism to
automatically morph the pool sizes depending on system load.
Automatic memory management is enabled by using the
memory_target and memory_max_target initialization
parameters.
Starting with Oracle9i, Oracle started
using
granules to
include a reserved region of RAM memory for SGA growth, and this is
the ORA-04033 limit when
sga_max_size is deployed.
Depending on your release and settings for
parameters, the remedy to fix the ORA-04033 might include:
- Reduce the size of another pool or
choose a smaller value for your pool parameter. This SGA memory
shift will avoid the ORA-04033 error when there is enough spare
RAM.
- If you also see the ORA-04031:
unable to allocate xxx bytes of shared memory, you need
to Increase the RAM on the server (if not using sga_max_xxx
parms)
- Increase
sga_max_size
and bounce the database (or use
scope=memory)
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
