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?

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.

Dear ThomasPS,

You have said in your reply that
>> "First Oracle will calculate the hash value for the SQL submitted >> and look for the same value in the buffer. "
When Oracle "LOOKS FOR THE SAME VALUE IN THE BUFFER", doesn't a GET-Call happens? And if "Looking-Up" is a GET-Call, then every PIN-Call has to be preceded by one GET-Call and by that logic, the no. of PIN-Calls in an Instance wide statistics has to be same as GET-Calls.

What could be reason that the PINS are always Greater than the GETS?
Is it such that, each GET-Call may have multiple PIN-Calls (for different SQL-statements coming under the same Cursor)?

Thanking you for your prompt reply,

Regards,
Mohan Wagh.

Quote:

---

http://www.dba-oracle.com/art_tuning4.htm

The parse phase—When the time comes to parse a SQL statement, Oracle first checks to see if the parsed representation of the statement already exists in the library cache. If not, Oracle will allocate a shared SQL area within the library cache and then parse the SQL statement.

The execution phase—At execution time, Oracle checks to see if a parsed representation of the SQL statement already exists in the library cache. If not, Oracle will reparse and execute the statement. During the execution phase, the query plan is run and the data is retrieved from Oracle

---

As I said before, Oracle will do a LOOK UP in the library cache using the HASH Value, and I don't think it is a parse call.

So it is clear that parse call will not be generated if the execution plan is in the library cache hence the exisitng plan will reuse. That shows why the PIN is more than GET.

Quote:

---

Originally posted by Thomasps
As I said before, Oracle will do a LOOK UP in the library cache using the HASH Value, and I don't think it is a parse call.

So it is clear that parse call will not be generated if the execution plan is in the library cache hence the exisitng plan will reuse. That shows why the PIN is more than GET.

---

I dont think u really got his Point..

His point is, isnt that Oracle has to do a GET ( for look up ) before it can say that SQL is in Shared Pool ( PIN )..if so why is Oracle showing that inflated value for PINS..

Quote:

---

Originally posted by abhaysk
I dont think u really got his Point..

His point is, isnt that Oracle has to do a GET ( for look up ) before it can say that SQL is in Shared Pool ( PIN )..if so why is Oracle showing that inflated value for PINS..

---

But Aby, I don't think LOOK UP is a GET. I think, if LOOKUP (for the hash value) fails then Oracle calls for a GET. Hence the infaltion in PINS.

Steve Adams website (perhaps the best place to research this sort of thing) says the following...

'A get is an attempt to locate an object in the library cache. If it is not found, it is loaded. Database object definitions are loaded from the data dictionary via the dictionary cache. Cursors are loaded by parsing the statement text'

'A pin is an attempt to use a previously located object in the library cache. If it has been aged out, then it must be reloaded'

Based on the above if the cache is working as intended (i.e. as a cache) then it seems sensible that pins could (should?) be greater than gets.

Quote:

---

Originally posted by Thomasps
But Aby, I don't think LOOK UP is a GET. I think, if LOOKUP (for the hash value) fails then Oracle calls for a GET. Hence the infaltion in PINS.

---

Quote:

---

Steve Adams website (perhaps the best place to research this sort of thing) says the following...

'A get is an attempt to locate an object in the library cache. If it is not found, it is loaded. Database object definitions are loaded from the data dictionary via the dictionary cache. Cursors are loaded by parsing the statement text'

'A pin is an attempt to use a previously located object in the library cache. If it has been aged out, then it must be reloaded'

Based on the above if the cache is working as intended (i.e. as a cache) then it seems sensible that pins could (should?) be greater than gets.

---

Thomas :

This is what he was looking for..( according to my uderstanding )

Abhay.

Quote:

---

Steve Adams website (perhaps the best place to research this sort of thing) says the following...

'A get is an attempt to locate an object in the library cache.

---

Ie GET and LOOKUP is same. If so, for each SQL execution ( even if, in the case of a cache hit) the GET should increment along with PIN, but never happens. GET will increment only when parsing. So can we say "A get is an attempt to locate an object in the library cache" ? If so, what is the justification for the original question "Why the value of PIN inflated..?"

Well Well..We gotta put an end to the story here..

Gets/Pins in V$LibraryCache goes some thing like this..

* GET : Its increamented only when SQL is parsed, ofcourse Get Call is made to find the SQL in Library Cache if its present & it obiviously reads the Hash Value in the cache for which it will have to access the Oracle BLOCK/s..but if the SQL is found GET is not incremenmted..So we have term GET which is misleading ( Please note its not the read from data block but an incremental value & incremented if and only if SQL is Parsed )...GET in context to Library Cache can be termed as PARSE LOCK.

GETHIT is opposite of this & its number of times the SQL found in the Library Cache.

* PIN : While GET is at parse stage, PIN is at Execution Stage..PIN is always assciated with a GET or GETHIT...And this value will be incremented by 1 every time the SQL is executed..

So it can be viewed as GET + GETHIT which implies that PIN > GET ( always ) & also PIN > GETHIT always ...


Hope this may help.

Rgds
Abhay.