Oracle memory use and allocation

Buffer Hit Ratio

The buffer hit ratio represents how often requested data blocks were retrieved from memory instead of disk. Hit ratio’s are not always an indicator of good performance, but they can be an indicator of bad performance. Use the following query to determine the buffer cache hit ratio.

SQL> SELECT (1 - (SUM(DECODE(name, 'physical reads', value, 0)) /
                 (SUM(DECODE(name, 'db block gets', value, 0)) +
                  SUM(DECODE(name, 'consistent gets', value, 0)))))
            * 100 "Hit Ratio"
     FROM v$sysstat;

The dynamic performance view v$db_cache_advice can be used to estimate the number of physical reads in relation to the cache size by running the following query.

SQL> SELECT size_for_estimate    AS "Cache Size in MB",
       buffers_for_estimate      AS "Buffers",
       estd_physical_read_factor AS "Estd Phys Read Fctr",
       estd_physical_reads       AS "Estd Phys Reads"
     FROM v$db_cache_advice
     WHERE name     = 'DEFAULT'
AND block_size = (SELECT value FROM v$parameter WHERE name = 'db_block_size')
AND advice_status = 'ON';

Dictionary Cache Hit Ratio

The dictionary cache hit ratio displays the percentage of memory reads for the data dictionary and other objects.

SQL> SELECT (1 - (SUM(getmisses) / SUM(gets))) * 100 "Hit Ratio"
     FROM v$rowcache;

Library Cache Hit Ratio

The library cache hit ratio shows the percentage of memory reads for SQL and PL/SQL.

SQL> SELECT SUM(pins) / (SUM(pins) + SUM(reloads)) * 100 "Hit Ratio"
     FROM v$librarycache;

Anything below 100% means statements had to be parsed or reparsed.

PGA Memory Sort Ratio

Ideally, sort operations in the PGA should be performed in memory and not on disk. The next query shows the number of disk- and memory sorts and the percentage of memory sorts.

SQL> SELECT a.value "Disk Sorts",
            b.value "Memory Sorts",
            ROUND((100 * b.value) /
            DECODE((a.value + b.value), 0, 1,
                   (a.value + b.value)), 2) "% Memory Sorts"
     FROM v$sysstat a,
          v$sysstat b
     WHERE a.name = 'sorts (disk)'
     AND b.name   = 'sorts (memory)';