Gets and pins in oracle
Posted on May 8, 2008
Within the shared pool, there are 2 types of data structures used for concurrency control:
locks (gets) and pins. A lock has a higher level than a pin and must be acquired before
attempting to pin an object. Locks can be thought of as parse locks while pins can be
thought of as short-term locks to read or change the contents of an object. We have
broken these out into seperate mechanisms in order to provide as much access to the
object as possible. After locking a library cache object, a process must then pin the
object before accessing it. It can be pinned in shared or exclusive mode depending on
whether the particular operation is read-only or not.
When there is a large number of gets and pins (over 1000) and the GetHitRatio and
PinHitRatio are low (less than 85%), the shared pool size needs to be increased. Also, it
is likely that the application is using unsharable SQL or infrequently referencing objects.
Reloads indicate that library objects have to be reinitialized and reloaded with data
because they have been aged out or invalidated. A high number of reloads can also
signal that the shared pool size needs to be increased.
The information in v$librarycache is primarily used to give an idea of total misses and
access attempts in the library cache. The sum(pins) indicates the number of times that
SQL statements, PL/SQL blocks and object definitions were accessed for execution.
The sum(reloads) indicates the number of times those executions resulted in library
cache misses causing Oracle to implicitly reparse a statement or reload an object
definition because it has been aged out or invalidated.
Popularity: 1% [?]
Related Posts:
» Filed Under Other
Comments
Leave a Reply