The location of a private SQL area depends on the type of connection established for a session. If a session is connected via a dedicated server, private SQL areas are located in the user's PGA. However, if a session is connected via the multithreaded server, the persistent areas and, for SELECT statements, the runtime areas, are kept in the SGA (x$ksmms) table provide the runtime information regarding SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance。
E.g.
/* Formatted on 2011/6/21 10:18:48 (QP5 v5.163.1008.3004) */
SELECT *
FROM X$KSMSS
WHERE KSMSSNAM = 'sql_area' AND KSMSSLEN <> 0;
2.2 PL/SQL Procedure Part
Oracle processes PL/SQL program units (procedures, functions, packages, anonymous blocks, and database triggers) much the same way it processes individual SQL statements.
Oracle allocates a shared area to hold the parsed, compiled form of a program unit. Oracle allocates a private area to hold values specific to the session that executes the program unit, including local, global, and package variables (also known as package instantiation) and buffers for executing SQL. If more than one user executes the same program unit, then a single, shared area is used by all users, while each user maintains a separate copy of his or her private SQL area, holding values specific to his or her session.
Individual SQL statements contained within a PL/SQL program unit are processed as described in the previous sections. Despite their origins within a PL/SQL program unit, these SQL statements use a shared area to hold their parsed representations and a private area for each session that executes the statement.
(x$ksmms) table provide the runtime information regarding PL/SQL area in Library Cache which is suppose to be allocated to a particular Oracle Instance 。
E.g.
/* Formatted on 2011/6/21 10:39:11 (QP5 v5.163.1008.3004) */
SELECT *
FROM X$KSMSS
WHERE KSMSSNAM LIKE 'PL/SQL%' AND KSMSSLEN <> 0;
PL/SQL MPCODE stands for machine dependent pseudocode.
PL/SQL DIANA stands for the PL/SQL code size in the shared pool at runtime.
2.3 Library Cache Manager
The main purpose of the library cache is to provide a mechanism to locate and store any library cache object quickly. A hashing mechanism is used to locate a handle, which contains the identity (name) of the object. The library cache handle then points us to one or more the library cache objects and their contents.
The library cache caches different types of library objects (e.g. packages, procedures, functions, shared cursors, anonymous PL/SQL blocks, table definitions, view definitions, form definitions).
Library cache memory is allocated out of the top most heap or the generic SGA heap. When the library cache, KGL, needs more memory, it will call the heap manager (KGH) to allocate it. The library cache consists of a hash table, which consists of an array of hash buckets. Each hash bucket is a doubly linked list of library cache object handles. Each library cache object handle points to a library cache object and has a reference list. The library cache object is further broken down into other components such as a dependency table, a child table, and an authorisation table (to name a few).
KGH Heap