DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Aboout V$LIBRARYCACHE and GETS-GETHITS / PINS-PINHITS Columns

Hybrid View

  1. #1
    Join Date
    Feb 2004
    Location
    Thane, Mharashtra, India
    Posts
    4

    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?

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    Re: Aboout V$LIBRARYCACHE and GETS-GETHITS / PINS-PINHITS Columns

    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Feb 2004
    Location
    Thane, Mharashtra, India
    Posts
    4
    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.

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727

    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    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.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    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..?"
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width