Shared /Large/Java/Result Pool: SGA Component of Oracle
6 min readThe shared pool caches various types of program data.For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.
This section includes the following topics: Library Cache The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. Data Dictionary Cache The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Server Result Cache The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks. Reserved Pool The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.
Library Cache The library cache is a shared pool memory structure that stores executable SQL and PL/SQL code. This cache contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. In a shared server architecture, the library cache also contains private SQL areas. When a SQL statement is executed, the database attempts to reuse previously executed code. If a parsed representation of a SQL statement exists in the library cache and can be shared, then the database reuses the code, known as a soft parse or a library cache hit. Otherwise, the database must build a new executable version of the application code, known as a hard parse or a library cache miss. Shared SQL Areas The database represents each SQL statement that it runs in the shared SQL area and private SQL area. Program Units and the Library Cache The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to as program units. Allocation and Reuse of Memory in the Shared Pool The database allocates shared pool memory when a new SQL statement is parsed, unless the statement is DDL, which is not considered sharable. The size of memory allocated depends on the complexity of the statement.
Shared SQL Areas
The database represents each SQL statement that it runs in the shared SQL area and private SQL area.
The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement. Each session issuing a SQL statement has a private SQL area in its PGA. Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.
The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements.
The database performs the following steps:
1. Checks the shared pool to see if a shared SQL area exists for a syntactically and semantically identical statement:
* If an identical statement exists, then the database uses the shared SQL area for the execution of the subsequent new instances of the statement, thereby reducing memory consumption.
* If an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool. A statement with the same syntax but different semantics uses a child cursor.
In either case, the private SQL area for the user points to the shared SQL area that contains the statement and execution plan.
2. Allocates a private SQL area on behalf of the session
The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.
Program Units and the Library Cache
The library cache holds executable forms of PL/SQL programs and Java classes. These items are collectively referred to as program units.
The database processes program units similarly to SQL statements. For example, the database allocates a shared area to hold the parsed, compiled form of a PL/SQL program. The database allocates a private area to hold values specific to the session that runs the program, including local, global, and package variables, and buffers for executing SQL. If multiple users run the same program, then each user maintains a separate copy of their private SQL area, which holds session-specific values, and accesses a single shared SQL area.
The database processes individual SQL statements within a PL/SQL program unit as previously described. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that runs the statement.
The ALTER SYSTEM FLUSH SHARED_POOL statement removes all information in the shared pool, as does changing the global database name.
Data Dictionary Cache The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing. The data dictionary is accessed so often by Oracle Database that the following special memory locations are designated to hold dictionary data: Data dictionary cache This cache holds information about database objects. The cache is also known as the row cache because it holds data as rows instead of buffers. Library cache All server processes share these caches for access to data dictionary information.
Server Result Cache The server result cache is a memory pool within the shared pool. Unlike the buffer pools, the server result cache holds result sets and not data blocks. SQL Query Result Cache The SQL query result cache is a subset of the server result cache that stores the results of queries and query fragments. You can enable or disable result caching at the database or statement level. When a query executes, the database determines whether the result exists in the query result cache. If the result is not cached, and if caching is enabled for the query, then the database runs the query, returns the result, and then caches it. If the result exists, however, then the database retrieves it from the cache instead of executing the query. The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of database objects used to construct the result. The next query cannot use the cached result, so the database automatically computes a new result, and then caches it for use by subsequent queries. The cache refresh process is transparent to the application. In effect, the query result cache functions as a "just-in-time" materialized view that the database creates and maintains as needed. The cache enables the database to avoid the expensive operation of rereading data blocks and recomputing results. Most applications benefit from this performance improvement.
Reserved Pool
The reserved pool is a memory area in the shared pool that Oracle Database can use to allocate large contiguous chunks of memory.
The database allocates memory from the shared pool in chunks. Chunking allows large objects (over 5 KB) to be loaded into the cache without requiring a single contiguous area. In this way, the database reduces the possibility of running out of contiguous memory because of fragmentation.
Infrequently, Java, PL/SQL, or SQL cursors may make allocations out of the shared pool that are larger than 5 KB. To allow these allocations to occur most efficiently, the database segregates a small amount of the shared pool for the reserved pool.
Large Pool
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:
1. UGA for the shared server and the Oracle XA interface (used where transactions interact with multiple databases)
2. Message buffers used in parallel execution
3. Buffers for Recovery Manager (RMAN) I/O child processes
4. Buffers for deferred inserts (inserts with the MEMOPTIMIZE_WRITE hint)
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.
