Gets & Pins - Library Cache Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Gets & Pins - Library Cache

  1. #1
    Join Date
    Mar 2002
    who could explain me with clear terms the difference between gets and pins stats for the library cache (v$librarycache).
    I think the documentation is not clear on this point.

  2. #2
    Join Date
    Jun 2002
    Ok so i have to start from the beginning.Some terms you must remember:

    Pins: hits in memory(thats good)

    Gets: hits from file .

    Reloads: Errors in the sql that prevent execution. Suppose and object in the SQL is modified. The shared SQL area becomes invalid and the statement must be reparsed before execution. This process is called reloading.

    Reloads->High and Invalidations
    ->Low means increase Memory

    Reloads->Low and Invalidations
    ->High means misses

    There are two types of hit ratios in Library cache. One is GETHITRATIO(parse phase) and the other is PINHITRAIO(excution phase).

    The GETHITRAIO is equal to the GETHITS/GETS. This ratio needs to remain GREATER THAN 90%.

    SQL>select gethits "Hits",gets "Misses",(gethits/gets)*100 "GETHITRATIO" from v$librarycache;

    Should be greater than 90% or otrherwise increase the size of shared pool.

    Calculating the PINHITRATIO:

    sum(pins) 'Executions',
    sum(reloads) 'Cache Misses',
    (1-(sum(reloads)/sum(pins))*100 "PINHITRATIO"
    from v$librarycache

    The PINHITRATIO should be greater than 99%.

    Data Dictionary Cache( Row cache):

    It's ratio is always low at database startup but soon increases after transactions and queries.

    select parameter,getmisses, (gets/getmisses)*100 "Hits" from v$rowcache;

    Should be >85%

    GETS: Number of requests on a object.

    GETMISSES: Number of requests resulting in cache misses.

    If there are too many getmisses, increase the SHARED_POOL_SIZE.

    You cannot succeed if you fear to face challenges.
    BE(CS) , OCP 8/8i.

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