Memory Architecture   «Prev  Next»

Lesson 6 The shared pool
Objective Identify the contents of the shared pool.

Oracle Shared Pool

What comprises the shared pool?

The shared pool is an area of shared memory that contains information related to the execution of SQL statements and PL/SQL code. This show will tell you a little about the three major structures that make up the shared pool:

1) The library cache contains the shared SQL area, PL/SQL code, and some control structures.
1) The library cache contains the shared SQL area, PL/SQL code, and some control structures.

2) The shared SQL area contains parse trees, and execution plans for SQL statements.
2) The shared SQL area contains parse trees, and execution plans for SQL statements.

3) The library cache holds PL/SQL packages, procedures, and functions. Multiple users of a PL/SQL program unit all share a single copy of the code.
3) The library cache holds PL/SQL packages, procedures, and functions. Multiple users of a PL/SQL program unit all share a single copy of the code

4) Control structures in the library cache include locks and library cache handles
4) Control structures in the library cache include locks and library cache handles

5) To improve performance, the data dictionary cache holds the most recently accessed data dictionary records.
5) To improve performance, the data dictionary cache holds the most recently accessed data dictionary records

6) Control structures in the main area of the shared pool include character set conversion memory and network security attributes
6) Control structures in the main area of the shared pool include character set conversion memory and network security attributes

Shared Pool

The shared pool contains two major subcaches: the 1) library cache and 2) data dictionary cache. The shared pool is sized by the SHARED_POOL_SIZE initialization parameter. This is another dynamic parameter that can be resized as long as the total SGA size is less than SGA_MAX_SIZE or SGA_TARGET.

Library Cache
The library cache holds information about SQL and PL/SQL statements that are run against the database. In the library cache, because it is shared by all users, many different database users can potentially share the same SQL statement. Along with the SQL statement itself, the execution plan and parse tree of the SQL statement are stored in the library cache. The second time an identical SQL statement is run, by the same user or a different user, the execution plan and parse tree are already computed, improving the execution time of the query or DML statement. If the library cache is sized too small, the execution plans and parse trees are flushed out of the cache, requiring frequent reloads of SQL statements into the library cache. See Chapter 8 for ways to monitor the efficiency of the library cache.

Data Dictionary Cache

The data dictionary is a collection of database tables, owned by the SYS and SYSTEM schemas, that contain the metadata about the database, its structures, and the privileges and roles of database users. The data dictionary cache holds a subset of the columns from data dictionary tables after first being read into the buffer cache. Data blocks from tables in the data dictionary are used continually to assist in processing user queries and other DML commands. If the data dictionary cache is too small, requests for information from the data dictionary will cause extra I/O to occur; these I/O-bound data dictionary requests are called recursive calls and should be avoided by sizing the data dictionary cache correctly.

Parsing and execution plans

The shared SQL area is a particularly important part of the shared pool. When you send a SQL statement to the database, Oracle must determine how to execute that statement. Oracle needs to determine what tables are involved, which indexes to use, if any, what the join order should be, and so forth. This process is called parsing, and typically involves a number of recursive queries to the data dictionary.

SEMrush Software