- 27 Aug, 2024
- 0 Comments
- 6 Mins Read
Shared /Large/Java/Result Pool: SGA Component of Oracle
The 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:
Data Dictionary:
- The Data Dictionary Cache stores metadata about the database schema, such as information about tables, columns, indexes, and constraints. This cache helps speed up queries and operations by providing quick access to this metadata.
- 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.
Library Cache:
- The Library Cache is a key component of the Shared Pool in Oracle databases. It plays a critical role in the efficient execution of SQL statements and PL/SQL code.
- The Library Cache stores executable forms of SQL statements, PL/SQL blocks, functions, procedures, and packages.
- Its primary function is to minimize the overhead associated with parsing, compiling, and executing SQL statements and PL/SQL code by caching their executable representations.
- Reduced Parsing Overhead: By storing parsed and compiled SQL statements, the Library Cache reduces the need for repetitive parsing and compilation, saving CPU resources.
- Faster Execution: Reusing cached execution plans and compiled code leads to faster query execution.
- Shared SQL: Enables multiple sessions to share the same SQL execution plans and compiled PL/SQL code, improving memory efficiency and consistency.
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 stores executable forms of PL/SQL programs and Java classes, collectively known as program units.The database processes program units similarly to SQL statements. For example, it allocates a shared area to hold the parsed, compiled form of a PL/SQL program. Additionally, it allocates a private area to hold session-specific values, including local, global, and package variables, and buffers for executing SQL. If multiple users run the same program, each user maintains a separate copy of their private SQL area, which holds session-specific values, while accessing a single shared SQL area.
- Individual SQL statements within a PL/SQL program unit are processed as previously described. These SQL statements use a shared area to hold their parsed representations and a private area for each session running the statement, despite originating within a PL/SQL program unit.
- The
ALTER SYSTEM FLUSH SHARED_POOLstatement clears all information in the shared pool, as does changing the global database name
Server Result Cache:
- The Server Result Cache is a feature in Oracle databases designed to improve query performance by storing the results of SQL queries in memory. This cache allows subsequent executions of the same queries to retrieve results directly from memory instead of re-executing the queries, leading to faster response times and reduced workload on the database.
- The SQL query result cache is a subset of the server result cache that stores the results of queries and query fragments. Result caching can be enabled or disabled at both the database and statement levels.
- When a query executes, the database checks if the result is already in the query result cache. If the result is not cached and caching is enabled for the query, the database runs the query, returns the result, and then caches it. If the result is found in the cache, the database retrieves it from there instead of re-executing the query.
- The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of the database objects involved in the query. As a result, the next execution of the query cannot use the cached result; the database recomputes the result and caches it for future use. This cache refresh process is transparent to the application.
Reserved Pool
- The Reserved Pool is designed to handle large memory allocations that might otherwise cause fragmentation in the Shared Pool.
By segregating these large allocations, the Reserved Pool ensures that sufficient contiguous memory is available for other operations in the Shared Pool. - 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.
Large Pool
- The Large Pool is a distinct memory area within the Oracle System Global Area (SGA) designed to support specific memory-intensive operations that might otherwise deplete the Shared Pool. Here’s an overview of its purpose, benefits, and configuration
- Memory Allocation for Specific Operations: The Large Pool is used to allocate memory for operations such as Recovery Manager (RMAN) backup and restore, shared server processes, parallel query execution, and large allocations for session memory.
- Reduce Fragmentation: By handling these memory-intensive operations separately, the Large Pool helps to reduce fragmentation in the Shared Pool, ensuring more efficient memory usage.
- Optimized Performance: By segregating large memory allocations, the Large Pool optimizes the performance of memory-intensive operations without affecting the general memory usage in the Shared Pool.
- Improved Stability: It prevents large memory operations from causing contention or memory exhaustion in the Shared Pool, leading to more stable and predictable database performance.
- Reduced Overhead: The Large Pool does not have the overhead of managing the library and data dictionary cache, making it more efficient for its designated purposes.
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.