| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Urgent Query
This is from the metalink:
Hi. Thanks to GP for the information. I have previously compiled a general overview of what occurs that may provide further insight.
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.
You would generally have a higher number of pins compared to gets as a pin can be acquired multiple times with different modes, again depending on the particular operation.
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.
I think that there is a direct relationship between a "Get miss" to a "Hard parse" & a "Pin Hit" to a "soft parse".
Any feedback is welcomed
Waleed
-----Original Message-----
Sent: Tuesday, January 21, 2003 8:29 AM
To: Multiple recipients of list ORACLE-L
I am not able to understand the definition of these two cols from V$LIBRARYCACHE view:
Thank You,
Sadiq.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Sadiq Khan
INET: sadiq31279_at_rediffmail.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Jan 21 2003 - 11:39:35 CST
 |
 |