First page Back Continue Last page Graphics
Library Cache
Part of the Shared Pool
Library cache contains parsed SQL statements
- SQL stored in a memory area called a cursor
Parsed statements can be reused!
- Increase overall system performance!
- Statements must match exactly
- Use stored procedures to increase reuse
Notes:
The library cache is one of the major structures in the Shared Pool. Its main function is to store parsed SQL statements (and PL/SQL blocks).
Before SQL can be executed, it needs to be translated by the user’s server process. This translation is known as parsing. (Note that a large part of the parse process is optimization - Oracle generates all available access paths for a statement, then chooses the path with the lowest cost. Refer to SkillBuilders’ SQL Tuning For Developers and DBAs course for more information.) The results of the parse are stored in a memory area called a cursor. These cursors live in the library cache of the shared pool. If an SQL statement is run a second time by the same or another server process it will not need re-parsing - as long as the parsed version of the SQL statement is still in the library cache.
If the SQL statement is not found in the library cache, it will require parsing (or re-parsing). This process has several downsides:
it uses valuable CPU time
it requires latches (memory locks) on the library cache. If many server processes are parsing, contention for library cache latches can occur
the library cache can become fragmented, increasing the time it takes to load large packages into the pool