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
statement' etc.
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?
Re: Aboout V$LIBRARYCACHE and GETS-GETHITS / PINS-PINHITS Columns
Quote:
Originally posted by MOHAN WAGH
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??
My answer is 'NO'. I don't think oracle is comapring the execution plan or something else. First Oracle will calculate the hash value for the SQL submitted and look for the same value in the buffer. If not exists in the buffer call for GET and if there exists direct PIN. Hash value is calculated using an algorithm based on the 'TEXT' of the command supplied.