Shared Pool（Conceptes）

Shared Pool

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.

The shared pool is divided into several subcomponents, the most important of which are shown in Figure 14-9.

Figure 14-9 Shared Pool

Description of "Figure 14-9 Shared Pool"

This section includes the following topics:

• Library Cache

• Data Dictionary Cache

• Server Result Cache

• Reserved Pool

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 following SQL areas:

• 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

  Each session issuing a SQL statement has a private SQL area in its PGA (see "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.

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.

Figure 14-10 shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their own PGAs. In a shared server, this copy is in the UGA, which is in the large pool or in the shared pool when no large pool exists.

Figure 14-10 Private SQL Areas and Shared SQL Area

Description of "Figure 14-10 Private SQL Areas and Shared SQL Area"

See Also:

• Oracle Database Performance Tuning Guide to learn more about managing the library cache

• Oracle Database Advanced Application Developer's Guide for more information about shared SQL

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 his or her 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.

Allocation and Reuse of Memory in the Shared Pool

The database allocates shared pool memory when a new SQL statement is parsed. The memory size depends on the complexity of the statement.

In general, an item in the shared pool stays until it is removed according to an LRU algorithm. The database allows shared pool items used by many sessions to remain in memory as long as they are useful, even if the process that created the item terminates. This mechanism minimizes the overhead and processing of SQL statements.

If space is needed for new items, then the database frees memory for infrequently used items. A shared SQL area can be removed from the shared pool even if the shared SQL area corresponds to an open cursor that has not been used for some time. If the open cursor is subsequently used to run its statement, then Oracle Database reparses the statement and allocates a new shared SQL area.

The database also removes a shared SQL area from the shared pool in the following circumstances:

• If statistics are gathered for a table, table cluster, or index, then by default the database gradually removes all shared SQL areas that contain statements referencing the analyzed object after a period of time. The next time a removed statement is run, the database parses it in a new shared SQL area to reflect the new statistics for the schema object.

• If a schema object is referenced in a SQL statement, and if this object is later modified by a DDL statement, then the database invalidates the shared SQL area. The optimizer must reparse the statement the next time it is run.

• If you change the global database name, then the database removes all information from the shared pool.

You can use the ALTER SYSTEM FLUSH SHARED_POOL statement to manually remove all information in the shared pool to assess the performance that can be expected after an instance restart.

See Also:

• Oracle Database SQL Language Reference for information about using ALTER SYSTEM FLUSH SHARED_POOL

• Oracle Database Reference for information about V$SQL and V$SQLAREA dynamic views

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.

See Also:

• Chapter 6, "Data Dictionary and Dynamic Performance Views"

• Oracle Database Performance Tuning Guide to learn how to allocate additional memory to the data dictionary cache

Server Result Cache

Unlike the buffer pools, the server result cache holds result sets and not data blocks. The server result cache contains the SQL query result cache andPL/SQL function result cache, which share the same infrastructure.

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.

See Also:

• Oracle Database Administrator's Guide for information about sizing the result cache

• Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RESULT_CACHE package

• Oracle Database Performance Tuning Guide for more information about the client result cache

SQL Query Result Cache

The database can store the results of queries and query fragments in the SQL query result cache, using the cached results for future queries and query fragments. Most applications benefit from this performance improvement.

For example, suppose an application runs the same SELECT statement repeatedly. If the results are cached, then the database returns them immediately. In this way, the database avoids the expensive operation of rereading blocks and recomputing results. The database automatically invalidates a cached result whenever a transaction modifies the data or metadata of database objects used to construct that cached result.

Users can annotate a query or query fragment with a RESULT_CACHE hint to indicate that the database should store results in the SQL query result cache. TheRESULT_CACHE_MODE initialization parameter determines whether the SQL query result cache is used for all queries (when possible) or only for annotated queries.

See Also:

• Oracle Database Reference to learn more about the RESULT_CACHE_MODE initialization parameter

• Oracle Database SQL Language Reference to learn about the RESULT_CACHE hint

PL/SQL Function Result Cache

The PL/SQL function result cache stores function result sets. 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. Good candidates for result caching are frequently invoked functions that depend on relatively static data.

PL/SQL function code can include a request to cache its results. Upon invocation of this function, the system checks the cache. If the cache contains the result from a previous function call with the same parameter values, then the system returns the cached result to the invoker and does not reexecute the function body. If the cache does not contain the result, then the system executes the function body and adds the result (for these parameter values) to the cache before returning control to the invoker.

Note:

You can specify the database objects that are used to compute a cached result so that if any of them are updated, the cached result becomes invalid and must be recomputed.

The cache can accumulate many results—one result for every unique combination of parameter values with which each result-cached function was invoked. If the database needs more memory, then it ages out one or more cached results.

See Also:

• Oracle Database Advanced Application Developer's Guide to learn more about the PL/SQL function result cache

• Oracle Database PL/SQL Language Reference to learn more about the PL/SQL function result cache

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.

Allocation of memory from the shared pool is performed 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.

See Also:

Oracle Database Performance Tuning Guide to learn how to configure the reserved pool