In Oracle Database 19c, the Large Pool is a memory structure within the System Global Area (SGA) that is used to manage memory allocations for specific operations that require large chunks of memory. It is an optional memory component, but it plays a crucial role in optimizing performance for certain database operations.
Purpose of the Large Pool: The Large Pool is designed to handle memory allocations for operations that require large, contiguous chunks of memory. By isolating these allocations from the Shared Pool, the Large Pool helps reduce fragmentation and contention in the Shared Pool, improving overall database performance.
Key Uses of the Large Pool
The Large Pool is primarily used for the following operations:
Backup and Restore Operations (RMAN)
When using Oracle Recovery Manager (RMAN) for backup and restore operations, the Large Pool is used to allocate memory for I/O buffers. This helps improve the performance of these operations.
Parallel Query Execution
For parallel query operations, the Large Pool is used to allocate memory for message buffers that facilitate communication between parallel query processes.
Shared Server (formerly MTS - Multi-Threaded Server)
In a Shared Server configuration, the Large Pool is used to store session memory (UGA - User Global Area) for shared server processes, reducing the load on the Shared Pool.
I/O Server Processes
The Large Pool is used for memory allocations related to I/O server processes, such as those used in asynchronous I/O operations.
Advanced Queuing (AQ)
Memory for Advanced Queuing message buffers can be allocated from the Large Pool.
Advantages of Using the Large Pool
Reduces Shared Pool Contention: By offloading memory allocations for large operations to the Large Pool, the Shared Pool is less likely to become fragmented or overloaded.
Improves Performance: Operations like RMAN backups and parallel queries can run more efficiently when memory is allocated from the Large Pool.
Simplifies Memory Management: Separating memory allocations for specific operations makes it easier to manage and tune memory usage in the database.
Configuring the Large Pool The Large Pool is configured using the `LARGE_POOL_SIZE` initialization parameter. You can set this parameter in the database parameter file (`spfile` or `pfile`). For example:
ALTER SYSTEM SET LARGE_POOL_SIZE = 256M;
The size of the Large Pool depends on the workload and the specific operations being performed. For example:
If you use RMAN for backups, allocate enough memory to accommodate the I/O buffers.
If you use Shared Server or parallel queries, allocate memory based on the number of sessions and the complexity of the queries.
Monitoring the Large Pool You can monitor the usage of the Large Pool using the following views:
V$SGASTAT: Provides detailed information about memory allocations in the SGA, including the Large Pool.
V$SGA: Displays summary information about the SGA components, including the Large Pool.
V$SGAINFO: Shows information about the sizes of SGA components.
Example query to check Large Pool usage:
SELECT * FROM V$SGASTAT WHERE POOL = 'large pool';
When to Use the Large Pool You should consider configuring and using the Large Pool if:
You use RMAN for backups and restores.
You run parallel queries frequently.
You use Shared Server configurations.
You experience Shared Pool contention or fragmentation.
If the Large Pool is not configured, Oracle will allocate memory for these operations from the Shared Pool, which may lead to performance issues. In summary, the Large Pool in Oracle 19c is an optional but highly beneficial memory area that helps optimize performance for specific operations by reducing contention and fragmentation in the Shared Pool. Proper configuration and monitoring of the Large Pool can significantly improve the efficiency of your database.
Large Memory Allocations
The large pool is an optional memory area. It provides an area of memory from which large allocations can be made. Oracle's backup and restore utilities typically allocate buffers that are hundreds of kilobytes in size. These will be allocated in the large pool if one is present. The multi-threaded server will also take advantage of the large pool, allocating session memory there instead of in the shared pool, thus leaving more of the shared pool open for SQL statements and execution plans.
Configuring Large Pool:
A large pool will only be present if the DBA has used the LARGE_POOL_SIZE initialization parameter to configure one. For example, to allocate a large pool of 10 megabytes, you would add the following line to your database's initialization file:
The large pool is an optional memory area intended for memory allocations that are larger than is appropriate for the shared pool. The large pool can provide large memory allocations for the following:
UGA for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)
Message buffers used in the parallel execution of statements
Buffers for Recovery Manager (RMAN) I/O slaves
By allocating session memory from the large pool for shared SQL, the database avoids performance overhead caused by shrinking the shared SQL cache. By allocating memory in large buffers for RMAN operations, I/O server processes, and parallel buffers, the large pool can satisfy large memory requests better than the shared pool.
Figure 5-8 is a graphical depiction of the large pool.
Figure 5-8 Large Pool
The large pool is different from reserved space in the shared pool, which uses the same LRU list as other memory allocated from the shared pool. The large pool does not have an LRU list. Pieces of memory are allocated and cannot be freed until they are done being used. As soon as a chunk of memory is freed, other processes can use it.
Java Pool:
The Java pool is an area of memory that stores all session-specific Java code and data within the Java Virtual Machine (JVM). This memory includes Java objects that are migrated to the Java session space at end-of-call. For dedicated server connections, the Java pool includes the shared part of each Java class, including methods and read-only memory such as code vectors, but not the per-session Java state of each session. For shared server, the pool includes the shared part of each class and some UGA used for the state of each session. Each UGA grows and shrinks as necessary, but the total UGA size must fit in the Java pool space. The Java Pool Advisor statistics provide information about library cache memory used for Java and predict how changes in the size of the Java pool can affect the parse rate. The Java Pool Advisor is internally turned on when statistics_level is set to TYPICAL or higher. These statistics reset when the advisor is turned off.
Shared large Pools - Quiz
Click the Quiz link below to test your knowledge of the shared pool and the large pool. Shared Large Pools - Quiz
