Automatic memory management analysis of Oracle 10G database

Is it difficult to accurately allocate the amount of memory required for different pools? The automatic shared memory management feature makes it possible to automatically allocate memory to where it is most needed.

Whether you're an entry-level DBA or an experienced DBA, you must have seen at least one error similar to the following:ORA-04031:unable to allocate 2216 bytes
of shared memory ("shared pool"... ...

or this error:

ORA-04031:unable to allocate XXXX bytes of shared memory
("large pool","unknown object","session heap","frame")

Or maybe this error:

ORA-04031:unable to allocate bytes of shared memory ("shared pool",
"unknown object","joxlod:init h", "JOX:ioc_allocate_pal")

The reason for the first error is obvious: there is not enough memory allocated to the shared pool to satisfy the user's request. (In some cases, the reason may not be the size of the pool itself, but the fragmentation caused by excessive analysis without using the binding variable, which is one of my favorite topics; But for now let's focus on the issues at hand.) Other errors come from the lack of space in large pools and Java pools, respectively.

You need to resolve these error conditions without making any application-related modifications. So what are the options? The problem is how to divide the available memory between all the pools required by the Oracle routine.

How do you divide the pie?

As you know, the system global Zone (SGA) of an Oracle routine contains several memory areas (including buffer caching, shared pools, Java pools, large pools, and redo log buffering). These pools occupy a fixed amount of memory in the operating system's memory space, and their size is specified by the DBA in the initialization parameter file.

These four pools (database block buffer cache, shared pool, Java pool, and large pool) occupy almost all of the space in the SGA. (compared to other regions, the redo log buffer does not occupy much space, which is irrelevant to our discussion here.) As DBAs, you must ensure that their respective memory allocations are adequate.

Suppose you decide that the values for these pools are 2GB, 1GB, 1GB, and 1GB, respectively. You will set the following initialization parameters to specify the size of the pool for the database routines.

db_cache_size = 2g
shared_pool_size = 1g
large_pool_size = 1g
java_pool_size = 1g