The library cache area of the shared pool holds SQL and PL/SQL code and control structures. SQL code may be contained in the shared SQL area, which contains both the parse tree and the execution plan for a SQL statement. When more than one user needs to execute a particular SQL statement, the parsed version is executed from the shared SQL area. In addition, each user has information related to the SQL statement in the private SQL area, which is contained in the Program Global Area (PGA). The private SQL area contains information unique to each user, such as bind variables and runtime buffers and is, in turn, associated with the shared SQL area that contains the actual parsed SQL code.

When a SQL statement is first executed, it is parsed and placed in the shared SQL area. Subsequent execution of that statement by any user will be faster, since reparsing is not necessary. Oracle maintains SQL statements in the shared SQL area using an LRU algorithm. If a SQL statement is removed from the shared SQL area, it must be reparsed and reinserted into the shared SQL area the next time it is executed. When a SQL statement is executed by a user process, the following steps take place:

The private SQL area is maintained by the user process. The number of SQL statements that can be maintained in the private SQL area is limited by the INIT.ORA parameter OPEN_CURSORS, which has a default value of 50.

Note that if a user process is connected to the instance via a dedicated server process (i.e., the Multi-Threaded Server is not in use), the private SQL area is allocated in the user’s PGA, not in the SGA. However, if the process is connected via a Shared Server process, the private SQL area will be allocated in the SGA.

PL/SQL program units (procedures, functions, packages, and triggers) are handled similarly to SQL statements. A shared area of the library cache is allocated to hold the parsed unit, and a private area is allocated for values associated with the specific session executing the PL/SQL unit. SQL statements contained within PL/SQL units are handled like any other SQL statement, even though they originate in a block of PL/SQL code. That is, the parsed SQL is placed in the shared SQL area, and a private SQL area is allocated for session data associated with that statement.

Oracle maintains its metadata—data about itself—in a series of tables called the data dictionary . The data dictionary consists of a number of tables and views containing information about the structure and contents of the database. Because the information from the data dictionary is used so often by Oracle, information read from these tables and views is stored in the dictionary cache (also called the row cache ), where it can be shared with all Oracle processes. Like the database buffer cache, the dictionary cache is managed using an LRU algorithm that replaces infrequently accessed information with data that has been accessed more recently.

The SGA contains other data required for the operation and control of the Oracle instance, including information about the state of the database, the instance, locks, and individual processes. This information is shared across all processes for the instance. No user data is stored in this area.