ORA-04031 and SGA settings

Oracle 10.2.0.2
OS RHEL 4

Recently, I got error message from Grid Control for one of our production databases.

Failed to connect to database instance: ORA-04031: unable to allocate 4120 bytes of shared memory ("large pool","unknown object","session heap","kzctxhugi1") (DBD ERROR: OCISessionBegin).

The message is pretty obvious: the large pool is not large enough for new allocations. Since it's 10g and we are using ASMM, the SGA is self-adjusted. We put 1.5G to sga_target and didn't specify a value for large_pool_size. Oracle suggests give a minimum value to large_pool_size so that Oracle won't squeeze large pool too small.

Another question I have during the research is that what's point of having both sga_target and sga_max_size.

Well, since sga_max_size is static and sga_target is dynamic, having set a larger value for sga_max_size than sga_target gives you a tuning margin for sga_target later without restarting the database. One thing we should pay attention is that on some OS, such as Windows and Linux, memory is allocated the same size as sga_max_size when the instance is started, but on some other OS, such as Sun Solaris, memory is allocated the same size as sga_target, and the other free memory (sga_max_size - sga_target) is ready for other use.