Shared Pool is the 2nd largest component of SGA. An important purpose of the shared pool is to cache the executable versions of SQL and PL/SQL statements. This allows multiple executions of the same SQL or PL/SQL code to be performed without the resources required for a hard parse which results in significant reductions in resources like CPU, memory, and latch usage.
The total size of the shared pool is determined by the initialization parameter SHARED_POOL_SIZE.
Below are the four major components of Shared Pool.
- Data Dictionary Cache
- Library Cache
- Result Cache(Optional) => 11g feature
- Reserved Pool
- Optional Components
1) Data Dictionary Cache or ROW 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 cache is also known as the row cache because it holds data as rows instead of buffers. e.g. DBA_ views
2) 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.
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 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.
Private SQL Area(In case Shared Server Mode): A private SQL area holds information about a parsed SQL statement and other session-specific information includes bind variable values, query execution state information, and query execution work areas for processing. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA.
Private SQL Area(In case Shared Server Mode): A private SQL area holds information about a parsed SQL statement and other session-specific information includes bind variable values, query execution state information, and query execution work areas for processing. Multiple private SQL areas in the same or different sessions can point to a single execution plan in the SGA.
For example, 100 executions of SELECT * FROM employees in one session and 10 executions of the same query in a different session can share the same plan. The private SQL areas for each execution are not shared and may contain different values and data. Each session issuing a SQL statement has a private SQL area. 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.
3) Server Result Cache:
In Oracle Database 11g, there is a brand new component of the SGA called the result cache. The result cache holds result sets and not data blocks. A client result cache differs from the server result cache. A client cache is configured at the application level and is located in client memory, not in database memory.
Server Result Cache contains below two components:
SQL Query Result Cache: You can cache the results of queries and query fragments in memory in the SQL query result cache. When the database executes the same SQL query again, it can simply retrieve the results from the result cache instead of re-executing the query, thus enhancing performance significantly.
PL/SQL Function Result Cache: PL/SQL function result caching works very similarly to the SQL query result cache. When a cached function is re-executed, the database doesn’t execute the function body, merely returning the cached result immediately instead.SQL Query Result Cache: You can cache the results of queries and query fragments in memory in the SQL query result cache. When the database executes the same SQL query again, it can simply retrieve the results from the result cache instead of re-executing the query, thus enhancing performance significantly.
Example- Without caching, 1000 calls of a function at 1 second per call would take 1000 seconds. With caching, 1000 function calls with the same inputs could take 1 second total.
lt_cache_max_size: this is the size of the result cache in bytes. The cache is allocated directly from the shared pool but is maintained separately (for example,
flushing the shared pool will not flush the result cache).
result_cache_max_result: this specifies the highest percentage of the cache that is able to be used by a single resultset (default 5%).
result_cache_remote_expiration: this specifies the number of minutes for which a resultset based on a remote object can remain valid. The default is 0 which means that resultsets dependant on remote objects will not be cached.
4) Reserved Pool:
Infrequently, Java, PL/SQL or SQL cursors may make allocations out of the shared pool that are larger than 4KB. If a large memory allocation exceeding 5K is requested , Oracle might have to age out a large no. of objects to satisfy that request and this operation could hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, an area of shared pool is set aside and only used for allocations larger than 4K .This is known as reserved pool and by default it occupies 5% of the shared pool size. Reserved pool will be used only if insufficient space is available in shared pool. The reserved pool makes allocation of large chunks more efficient and shared pool makes allocations in chunks of 1K or 4K. The database can use this memory for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool.
Infrequently, Java, PL/SQL or SQL cursors may make allocations out of the shared pool that are larger than 4KB. If a large memory allocation exceeding 5K is requested , Oracle might have to age out a large no. of objects to satisfy that request and this operation could hold the latch resource for detectable periods of time, causing minor disruption to other concurrent attempts at memory allocation. To allow these allocations to occur most efficiently, an area of shared pool is set aside and only used for allocations larger than 4K .This is known as reserved pool and by default it occupies 5% of the shared pool size. Reserved pool will be used only if insufficient space is available in shared pool. The reserved pool makes allocation of large chunks more efficient and shared pool makes allocations in chunks of 1K or 4K. The database can use this memory for operations such as PL/SQL and trigger compilation or for temporary space while loading Java objects. After the memory allocated from the reserved pool is freed, it returns to the reserved pool.
5) Optional Components:
- Large Pool:
Shared server- By default, the large pool is not configured. If you do not configure the large pool, then Oracle Database uses the shared pool for shared server user session memory. If you do configure the large pool, Oracle Database still allocates a fixed amount of memory (about 10K) for each configured session from the shared pool when a shared server architecture is used.
Parallel query- To avoid possible negative impact to performance, Oracle recommends that you do not manage SGA memory manually when parallel query is used. Instead, you should enable Automatic Memory Management or Automatic Shared Memory Management to ensure that the large pool will be used to cache parallel execution memory buffers. RMAN uses this only if the BACKUP_DISK_IO = n and BACKUP_TAPE_IO_SLAVE = TRUE parameters are set. If the Large Pool is too small, memory allocation for backup will fail and memory will be allocated from the Shared Pool. Parallel Query allows one to break a SELECT or DML statements into multiple smaller chunks and have PQ slaves execute those smaller chunks on separate CPU's in a single box.
- Java Pool:
The Java Pool is an optional memory object, but is required if the database has Oracle Java installed and in use for Oracle JVM (Java Virtual Machine). The size is set with the JAVA_POOL_SIZE parameter that defaults to 24MB.The Java Pool is used for memory allocation to parse Java commands and to store data associated with Java commands.
- Streams Pool:
If STEAMS_POOL_SIZE is not set or is zero, the size of the pool grows dynamically as required by Oracle Streams feature of Oracle Enterprise Edition. Oracle Streams enables information sharing. Using Oracle Streams, each unit of shared information is called a message, and you can share these messages in a stream. The stream can propagate information within a database or from one database to another. You specify which information is routed and the destinations to which it is routed. The result is a feature that provides greater functionality and flexibility than traditional solutions for capturing and managing messages, and sharing the messages with other databases and applications.
Thanks,
Please comment if you like this post !
Thank you for your comment !