Re: GETS, PINS and RELOADS in v$librarycache.

from http://www.ixora.com.au/q+a/0011/08200945.htm
>>>question : Could anybody please explain about what is the
difference between gets and pins and correspondingly between gethits and pinhits in the v$librarycache view?

>>>answer:=20

A get is an attempt to locate an object in the library cache. If it is not found, it is loaded. Database object definitions are loaded from the data dictionary via the dictionary cache. Cursors are loaded by parsing the statement text.

A pin is an attempt to use a previously located object in the library cache. If it has been aged out, then it must be reloaded.

When an object has been located in (or loaded into) the library cache, the session "remembers" its location by maintaining a null mode lock on the object. When a session needs to use an object, if it already (or still) has the library cache lock, then it can proceed with the pin request. If it does not yet have a lock then a get operation is needed to get a lock before the pin can be requested.

This probably makes little sense unless you understand the distinction between the handle of a library cache object and its heaps. Locks protect the handle. Pins protect the heaps. The heaps are the "body" parts of the object that get loaded as required, except for heap 0 which contains the identifying information about the object. When we speak of an object being aged out and then reloaded, it is actually the heaps other than heap 0 of which we are speaking. Heap 0 is never aged out while any session is holding a lock on its handle.

The fundamental reason for the distinction between locks and pins is that it in theory allows an object's identity to be checked using a shared lock on the handle while another session has an exclusive pin on one or more of its heaps. The distinction is also important for maintaining cache coherence.

>>>>about reloads/invalidations.

In V$LIBRARYCAHE for one of my databases, though the miss percentage is very low, reloads are 1329 with invalidations 1116. What are these invalidations? Do I still need to increase the shared pool to reduce the reloads near zero? Could you please throw some light on it?

when we need the object again, then a reloads happen!.

--
http://www.freelists.org/webpage/oracle-l