Logo Background

SQL Cache And Library Cache Hit Ratio

  • By on January 26, 2010 | No Comments

    If you are having performance issue in Oracle Database especially on caching part, it is always recommended to review the cache hit ratio and library cache hit ration from time to time.

    The cache hit ratio should be more than 90% as the rule of thumb setting and you can increase the shared pool memory to increase the cache hit ratio.

    --Cache Hit Ratio
    SELECT ROUND(((1-(SUM(DECODE(name,
    'physical reads', VALUE,0))/
    (SUM(DECODE(name, 'db block gets', VALUE,0))+
    (SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
    || '%' "Buffer Cache Hit Ratio"
    FROM v$sysstat;

    Library cache hit ratio has another different story. Whenever procedure or package is executed, it will loads into the shared pool memory and eventually will pin the procedure and package into memory and reuse it whenever there’s subsequent call for the function.

    The result of the query LIBCACHE should be near 0, if the ratio is larger than 1% then you have to increase the SHARED_POOL_SIZE.

    --Library Cache Hit Ratio
    SELECT SUM(pins) "Total Pins", SUM(reloads) "Total Reloads",
    SUM(reloads)/SUM(pins) *100 libcache
    FROM v$librarycache

    For RAC environment, you can use the SQL as below to check on the cache hit ratio and library cache hit ratio.

    --RAC Cache Hit Ratio
    SELECT ROUND(((1-(SUM(DECODE(name,
    'physical reads', VALUE,0))/
    (SUM(DECODE(name, 'db block gets', VALUE,0))+
    (SUM(DECODE(name, 'consistent gets', VALUE, 0))))))*100),2)
    || '%' "Buffer Cache Hit Ratio"
    FROM gv$sysstat;
    --RAC Library Cache Hit Ratio
    SELECT SUM(pins) "Total Pins", SUM(reloads) "Total Reloads",
    SUM(reloads)/SUM(pins) *100 libcache
    FROM gv$librarycache
    Previous
    Next
    » Oracle SQL Drop Stored Procedure
Leave a Comment