Oracle Database Administration: The Essential Refe

Shared pool

The shared pool is an area of the SGA that, in turn, consists of three distinct areas: the library cache, the dictionary cache, and control structures. The overall size of the shared pool is controlled by the INIT.ORA parameter SHARED_POOL_SIZE.

Library cache.

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:

1. Oracle checks to see if the SQL statement ...