Oracle library cache pin waits are caused by contention with the library
cache, the area used to store SQL executables for re-use. The library
cache pin Oracle metric takes place if the process wants to pin an
object in memory in the library cache for examination, ensuring no other
processes can update the object at the same time. The library cache pin
wait usually happens when you are compiling or parsing a PL/SQL object
or a view.
The libwait.sql query provides clues about whether Oracle has been waiting
for library cache activities:
. . .
where See Code depot for complete script
b.name in ('latch free','library cache load lock',
'library cache lock','library cache pin')
Output from the above script might resemble the following:
Seeing increasing numbers of waits for the above events could indicate an
undersized shared pool.
You can dig even deeper into the library cache and uncover exactly which
objects currently reside in the cache.
The libobj.sql script will show you everything you need to know on this
front, but be forewarned, as this script can return large amounts of data
in databases with large shared pools and many references to code and data
name, See Code depot for complete script