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:
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.
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.