Re: v$librarycache -- sorry for crossposting

The library cache is organized as a set of "objects", organized into a hash table. The hash table permits rapid lookup of an object to see if it already exists. If it does not exist, your session creates one. If it does exist, your session may be able to use it as it exists. The objects can be anything, but most commonly are cursors (sql statements and anonymous pl/sql blocks).

While looking up an object in the library cache, in order to keep pointers from changing under your session's feet, Oracle uses latches to protect the hash table's linked list pointers -- the hash chain, as it is called.

The idea is to keep the pointers from changing while you are scanning the linked list, looking for your object. Keeps you from pointing to the wrong value and perhaps crashing your session. A latch is a very low-level locking mechanism.

The "gets" you ask about are "latch gets", or times a session (or all sessions, in an instance-wide view) obtained a latch to lookup something in the hash table. This lookup process is also known as a "soft parse".

None of this has anything to do with physical IO in any way. "Gets" in this sense are library cache gets, while the IO variety are related to buffer gets, in the buffer cache hash table. Another beast altogether.

"Pins" are low level locking mechanisms as well, but more coarse-grained then latches and suitable for protecting chunks of memory that belong to a library cache object from changing while you are executing that object. It can also give you exclusive access to a memory chunk to permit you to change it, as in the case of parsing or reloading. Pins have queueing properties, just like the other, more familiar Oracle locks -- those more properly known as enqueues, such as TM, TX, ST, etc.

Again, library cache pins are similar to, but not identical to buffer cache pins. Don't confuse the two. Buffer cache pin waits are measured as "buffer busy waits".

In addition to Gets and Pins, statistics related to library cache activity also include Misses -- times we failed to get a latch because someone else had it in use. In such cases, we "spin", or loop continuously trying to get the latch. After a predetermined number of failures, we give up for a bit and "sleep". Sleeps are relatively long in time, starting with 10 milliseconds doubling up to maybe 2 seconds. What is important here is that Misses are insignificant for performance consideration, but Sleeps are terribly important.

Hope this clarifies things a bit.

• ricky

aNotherDBA wrote:
>
> Does anyone have a better explanation of the GETS and PINS columns in this
> view, besides:
>
> GETS: Handle requests for objects of this namespace
> PINS: PIN requests for objects of this namespace
>
> Are the GETS necessarily physical gets??? Can they be physical GETS (from
> the disk)? I'm struggling with this concept somewhat.
Received on Tue Mar 04 2003 - 14:46:55 CST