Aboout V$LIBRARYCACHE and GETS-GETHITS / PINS-PINHITS Columns
Question about GETS-GETHITS and PINS-PINHITS columns of V$LIBRARYCACHE, where
Oracle stores the Instance-wide LibraryCache Statistics.
I have gone through all your follow-up answers regarding the questions on
V$LIBRARYCACHE and Gets/Pins. After going through all answers, I have few
questions of my own.
(Actually all the confusion is only cause, every time i have observed that PINS
are always Greater than GETS, and the difference is quite big.)
1> Does 'Every' PIN (Execution Call and Latching for execution) has to be
preceded by a GET (Parse Call & Latching for lookup)?
If the answer is "NO", Then --->
Earlier in one follow-up answer you have a explaination for this "NO" --
if a Query is fired by the user and a similar/exact matching cursor is
still 'Open', then the GET-Call is not required and Oracle directly goes
for a PIN-Call.
My Question is = Without GET-Call, how does Oracle determines whether the
Query fired by the user is Exact/Similar to the already Open Cursor? And
if Oracle has to determine if the SQL statement is present in cache or
not, then isn't a GET-Call is necessary?
If the answer is "YES", Then --->
There is a difference in GETS and GETHITS value, I can understand the
difference as GET always doesnot succeed in locating the desired SQL
statement and the reasons could be 'brand-new statement/ purged from cache
My Question is = Why there is a difference between PINS and PINHITS ?
Since a PIN-Call must be preceded by a GET-Call, doesn't it imply that the
SQL statement(parsed) is already available in the cache? If it is already
in the cache, then how come a PIN-Miss can occur? Or does the purging of
the desired SQL statement happens between and within the time Oracle takes
to release GET-Latch and acquire a PIN-Latch?
Click Here to Expand Forum to Full Width