Java Pool

This is an excerpt from the bestselling book Oracle Grid & Real Application Clusters.  To get immediate access to the code depot of working RAC scripts, buy it directly from the publisher and save more than 30%.

The JAVA Pool holds the JAVA execution code in a similar manner to the PL/SQL cache in the shared pool. The JAVA pool is used by many internal routines, such as import and export, and should be sized at approximately 60 megabytes if no other JAVA will be utilized in the user applications.

Shared Pool

The shared pool holds the library cache, dictionary cache, message queues, latch and lock areas, buffers for parallel execution messages, and control structures. The shared pool also contains the RAC lock areas known as the Global Resource Directory. The total size of the shared pool is determined by the initialization parameter shared_pool_size.

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles. Shared SQL areas are accessible to all users so the library cache is contained in the shared pool within the SGA.

The data dictionary is accessed very often by various Oracle processes. Dictionary Cache is the memory areas designated to hold dictionary data. It is also known as the row cache because it holds data as rows instead of buffers which hold entire blocks of data.

Large Pool

This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. The large pool holds the user global areas when MTS is used and holds the parallel query execution message queues for parallel query.

In general, large pool provides memory allocations for:

* Session memory for the shared server and the Oracle XA interface which is used where transactions interact with more than one database.

* I/O server processes.

* Oracle backup and restore operations.

Streams Pool

This is a new area in Oracle Database 10g that is used to provide buffer areas for the streams components of Oracle.  To configure the Streams pool explicitly, specify the size of the pool in bytes using the streams_pool_size initialization parameter. If the size of the Streams pool is greater than zero, then any SGA memory used by Streams is allocated from the Streams pool. If the size of the Streams Pool is zero or not specified, then the memory used by Streams is allocated from the shared pool and may use up to 10% of the shared pool.

Fixed SGA

A portion of the SGA contains general information about the state of the database and the instance, which the background processes need to access. This is called the Fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

Automatic Shared Memory Management

Oracle 10g, with its emphasis on self-management and self-tuning, introduces many features that operate automatically, as well as in an advisory role. Since the values for the SGA components are fixed at instance start time, they must be used as they are during the instance runtime, with some exceptions.

Often it happens that a certain component?s memory pool is never used and it is not available for another component, which is in need of extra memory. Under-sizing can lead to poor performance and out-of memory errors (ORA-4031), while over-sizing can waste memory.

With the Database 10g, the Automatic Shared Memory Management feature can be employed. This feature enables the Oracle database to automatically determine the size of each of these memory components within the limits of the total SGA size. This solves the allocation issues that are normally faced in a manual method.