| Lesson 6 || The shared pool |
| Objective || Identify the contents of the shared pool. |
Oracle Shared Pool
What comprises the shared pool?
Shared Pool 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.