René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Library Cache in Oracle

The Library Cache is a piece of memory within the SGA that Oracle uses in order to store SQL Statements. Whenever a Process issues an SQL Statement, the text of the Statement goes into the Library Cache where the statement is parsed an validated. If for example I do a insert into city (name, abbr) values ('Geneva', 'GE');. the Library Cache checks if there is a table named city having the columns name and abbr. As an additional task, the Library Cache also checks if the user's privileges are sufficient to execute the statement.
In a similar way, the Library Cache also caches PL/SQL Statements and Objects.
The Library Cache was introduced because parsing (and validating and checking privileges) is expensive in terms of CPU cycles.

Library Cache Namespaces

The Library Cache is divided into Namespaces (or Libraries). These can be found through v$librarycache
select namespace from v$librarycache
NAMESPACE
---------------
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE
JAVA SOURCE
JAVA RESOURCE
JAVA DATA