Optimization: sizing Oracle SGA and PGA with the built-in advisors

#Current settings

SHOW PARAMETER sga_target
SHOW PARAMETER sga_max_size
SHOW PARAMETER pga_aggregate_target
SHOW PARAMETER pga_aggregate_limit
SHOW PARAMETER memory_target

On Linux with large SGAs, prefer ASMM (sga_target + pga_aggregate_target) with HugePages over AMM (memory_target), because AMM can't use HugePages.

#SGA advisor

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

estd_db_time_factor < 1 for larger sizes shows the expected DB time reduction. If it flattens out, more SGA won't help.

#Buffer cache advisor

SELECT size_for_estimate AS mb, size_factor, estd_physical_read_factor, estd_physical_reads
FROM v$db_cache_advice
WHERE name = 'DEFAULT' AND advice_status = 'ON'
ORDER BY size_for_estimate;

#PGA advisor

SELECT ROUND(pga_target_for_estimate / 1048576) 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;

Aim for zero estd_overalloc_count and a high cache hit percentage. Check one-pass and multi-pass sort/hash executions:

SELECT name, value FROM v$sysstat
WHERE name LIKE 'workarea executions%';

#HugePages on Linux

• Calculate pages needed for the SGA; set vm.nr_hugepages.
• Set use_large_pages = ONLY so the instance won't start without them (avoids silent fallback).
• Disable Transparent HugePages per Oracle guidance.

SHOW PARAMETER use_large_pages

Check the alert log at startup for the large pages summary.

#Change safely

1. Use AWR over representative periods (month-end included) to read advisor trends.
2. Change by 10–20% at a time.
3. Leave headroom for the OS, other instances and PGA spikes (pga_aggregate_limit).
4. Compare AWR DB time, physical reads and top waits before/after (see the AWR reading post).

Warning

On servers with several instances, size the total of SGAs plus PGA limits below physical memory with OS headroom. Overcommitting leads to swapping, which is far worse than a smaller cache.

#Checklist

• ASMM with HugePages on Linux
• SGA, buffer cache and PGA advisors reviewed over AWR periods
• Workarea one-pass/multi-pass checked
• Changes made in small steps
• Memory headroom for OS and other instances