Memory Advisors and Parse Health Checks

Oracle DBA Certification 13 min min read Updated: Nov 16, 2026 Advanced
Memory Advisors and Parse Health Checks
Advanced Topic 7 of 7

Do Not Chase the Hit Ratio

Old books say 'keep the buffer cache hit ratio above 95%'. That number is misleading: a badly written query that reads the same blocks millions of times makes the ratio look wonderful. Use advisors and wait events instead.

SGA Advisor

sql SELECT sga_size, sga_size_factor, estd_db_time, estd_physical_reads FROM v$sga_target_advice ORDER BY sga_size;

estd_db_time is the estimated total database time at each SGA size. Look for the size where the curve flattens. A bigger SGA beyond that point gives no real benefit.

Buffer Cache Advisor

sql SELECT size_for_estimate AS mb, size_factor, estd_physical_read_factor, estd_physical_reads FROM v$db_cache_advice WHERE name = 'DEFAULT' AND block_size = 8192 ORDER BY size_for_estimate;
How to read it: estd_physical_read_factor = 1.0 is your current size. A value of 0.5 at a bigger size means disk reads would be cut in half.

PGA Advisor

sql SELECT ROUND(pga_target_for_estimate/1024/1024) AS target_mb, pga_target_factor, estd_pga_cache_hit_percentage, estd_overalloc_count FROM v$pga_target_advice ORDER BY pga_target_for_estimate;
Goal: Pick the smallest PGA target where estd_overalloc_count is 0 and the cache hit percentage is close to 100%.

Parse Health

sql SELECT name, value FROM v$sysstat WHERE name IN ('parse count (total)','parse count (hard)','execute count');

A healthy OLTP system has few hard parses compared to total parses. Many hard parses mean applications are not using bind variables.

sql -- SQL statements that differ only by literals SELECT force_matching_signature, COUNT(*) AS versions, MIN(sql_text) AS example FROM v$sql WHERE force_matching_signature <> 0 GROUP BY force_matching_signature HAVING COUNT(*) > 50 ORDER BY versions DESC;

Open Cursors

sql SELECT s.sid, s.username, COUNT(*) AS open_cursors FROM v$open_cursor c JOIN v$session s ON s.saddr = c.saddr GROUP BY s.sid, s.username ORDER BY open_cursors DESC FETCH FIRST 10 ROWS ONLY; SHOW PARAMETER open_cursors

If sessions get close to open_cursors, you will see ORA-01000. Often the application leaks cursors and the real fix is in the code, but you can raise the limit as a short-term step.

Quick Health Table

CheckHealthy sign
Hard parsesSmall share of total parses
log file sync waitsShort average, a few ms
PGA advisorestd_overalloc_count = 0
Swapping at OS levelNone. Swapping ruins Oracle performance.

Conclusion

Use the SGA, buffer cache and PGA advisors to size memory, and check hard parses and open cursors for application problems. Ignore the old hit-ratio rules.

Quick Summary: Advisors beat hit ratios. Many hard parses = missing bind variables.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators