Oracle Internals: An Introduction

Library Cache Locks and Pins

The library cache is not one cache, but many. It contains the pseudo code for PL/SQL program units. It contains parse trees and execution plans for shareable SQL statements. It also contains abstract representations in a form called DIANA of the database objects referenced by the SQL statements. The information is needed in this form for PL/SQL program unit compilation and SQL statement parsing and execution, despite the fact that the dictionary cache contains the same information in a different form. The library cache also contains control structures such as synonym translations, dependency tracking information, and library cache locks and pins.

Library cache locks are referred to as breakable parse locks in the Oracle documentation. They are applied to the library cache objects for SQL statements and PL/SQL program units, and recursively to the library cache objects for the database objects on which they depend. Library cache locks are held in shared mode during parse operations and are converted to null mode thereafter. If a DDL statement later modifies the definition of a database object, then the library cache information for that database object and all dependent library cache objects is invalidated by breaking the library cache locks.

Library cache locks can only be broken, however, when the library cache object is not also pinned. A pin is applied to the library cache object for a PL/SQL program unit or SQL statement while it is being compiled, parsed, or executed. Pins are normally held in shared mode, but are also held in exclusive mode while the library cache information for the object is being changed. The library cache objects for pipes and sequences are most subject to change. When a library cache object is pinned, pins are applied to all referenced objects in turn. When a pin is applied to the library cache object for a database object, then a corresponding row cache enqueue lock is acquired on the underlying data dictionary row, thereby preventing conflicting DDL.

Every object in the library cache has a handle that acts as the resource structure for library cache locks and pins. The handle, lock, and pin structures are all dynamically allocated within the shared pool. The handle implements two-way linked lists of locks held, locks waited for, pins held, and pins waited for. Sessions waiting for a lock or pin report a library cache lock or library cache pin wait respectively. The parameters for these waits are shown in Table 4.5.

Table 4-5. Wait Parameters (library cache lock and library cache pin waits)

Parameter	Description
p1	The address in memory of the library cache handle.
p2	The memory address of the lock or pin structure.
p3	The mode of lock or pin required, and the namespace of the object, encoded as 10 * mode + namespace. In this case, the modes are: 2 shared 3 exclusive The namespaces are: 0 cursor 1 table, procedure, and others 2 package body 3 trigger 4 index 5 cluster 6 object 7 pipe

If there are multiple readers of a single pipe, then library cache pin waits on the library cache object for that pipe will be routine, but brief. Other than that, library cache waits are relatively rare, although much more likely to be prolonged. These waits time out after three seconds and, if they do time out, deadlock detection is performed. If a deadlock is found, the lock or pin request is aborted and an ORA-4020 error is returned. This error is normally caused by ad hoc DDL. It should not be necessary to code your applications to handle this error.