Master Oracle Hit Ratios and Memory Tuning with Practical SQL Queries

Library Cache Hit Ratio

Formula: Library Cache Hit Ratio = sum(pinhits) / sum(pins). Query: SELECT SUM(pinhits)/SUM(pins) FROM V$LIBRARYCACHE; A healthy ratio is typically above 98%; lower values suggest increasing the shared pool, using bind variables, or adjusting cursor_sharing.

Shared Pool Memory Usage

Formula:

SELECT (1 - ROUND(BYTES/(&TSP_IN_M*1024*1024),2)) * 100 || '%' FROM V$SGASTAT WHERE NAME='free memory' AND POOL='shared pool';

where &TSP_IN_M is the total shared pool size in MB. Aim for 75‑90% usage.

Query free memory:

SELECT * FROM V$SGASTAT WHERE NAME='free memory' AND POOL='shared pool';

DB Buffer Cache Hit Ratio

Formula: Hit ratio = 1 - (physical reads / (block gets + consistent gets)). Query:

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS, 1 - (PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "Hit Ratio" FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';

Target >90%; otherwise increase DB_CACHE_SIZE.

Alternative Oracle‑documented formula uses detailed read categories. Retrieve the needed statistics with:

SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('session logical reads','physical reads','physical reads direct','physical reads direct (lob)','db block gets','consistent gets');

Data Buffer Cache Hit Ratio

Relevant statistics queries:

SELECT value FROM v$sysstat WHERE name='physical reads';

SELECT value FROM v$sysstat WHERE name='physical reads direct';

SELECT value FROM v$sysstat WHERE name='physical reads direct (lob)';

SELECT value FROM v$sysstat WHERE name='consistent gets';

SELECT value FROM v$sysstat WHERE name='db block gets';

Compute x = physical reads direct + physical reads direct (lob) and then:

Hit Ratio = 100 - (physical reads - x) / (consistent gets + db block gets - x) * 100

. Keep the ratio above 90%.

Shared Pool Hit Ratio

Query:

SELECT SUM(pinhits-reloads)/SUM(pins)*100 "hit ratio" FROM v$librarycache;

If the ratio falls below 95%, consider using bind variables or increasing shared pool memory.

Memory Sort Ratio

Queries to count sorts:

SELECT * FROM v$sysstat WHERE NAME='sorts (memory)';

SELECT * FROM v$sysstat WHERE NAME='sorts (disk)';

Calculate the in‑memory sort percentage:

SELECT ROUND(&sort_in_memory/(&sort_in_memory+&sort_in_disk),4)*100 || '%' FROM dual;

A higher ratio is better; low values may require increasing PGA.

PGA Hit Ratio

Formula: BP * 100 / (BP + EBP) where BP = bytes processed and EBP = extra bytes read/written.

Query the raw statistic: SELECT * FROM V$PGASTAT WHERE NAME='cache hit percentage'; OEM advisory view:

SELECT ROUND(PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb, ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc, ESTD_OVERALLOC_COUNT FROM V$PGA_TARGET_ADVICE;

In the example, a PGA of at least 375 MB is recommended, and the hit ratio should stay above 50%.

Overall, monitoring these hit ratios and memory usage metrics with the provided SQL statements enables DBAs to pinpoint performance bottlenecks and apply targeted tuning actions such as adjusting memory allocations, enabling bind variables, or resizing caches.