OracleFree calculatorPDF, Excel, Word exportOracle Database

Oracle Database 19c SGA, PGA and HugePages Sizing Calculator for ERP

This calculator splits server memory into SGA and PGA for an Oracle Database 19c instance on Linux running an ERP such as JD Edwards, E-Business Suite or PeopleSoft. It also shows the default PGA_AGGREGATE_LIMIT Oracle will apply and the number of 2 MB HugePages the SGA needs.

Your numbers

Server RAM

Physical memory on the database server.

OS and other software reserve

Kept for the OS, page cache, agents and backup software. Never less than 4 GB.

Share of remaining memory for this instance

100 %

Lower this if several instances share the server.

SGA share of instance memory

PROCESSES parameter

The init parameter, not the current session count.

Average PGA per process

Measure with V$PROCESS.PGA_ALLOC_MEM averages at peak; 5 MB is a starting assumption.

Instance type

Your results

SGA_TARGET (GB)

161.28

Set SGA_TARGET (and SGA_MAX_SIZE) to this with ASMM; leave MEMORY_TARGET unset.

PGA_AGGREGATE_TARGET (GB)

69.12

Reserve for OS and other software (GB)

25.6

Memory for this instance (GB)

230.4

Default PGA_AGGREGATE_LIMIT (GB)

138.24

Oracle default when MEMORY_TARGET is unset: 200% of PGA_AGGREGATE_TARGET, but at least 2 GB and at least 3 MB x PROCESSES (5 MB x PROCESSES for RAC).

Expected PGA use at PROCESSES (GB)

7.3242

HugePages needed (2 MB pages)

82,576

Minimum vm.nr_hugepages for this SGA. Add a few pages of headroom and sum across all instances on the host.

PGA warning (1 = expected PGA exceeds target)

A starting split, not a substitute for advisor data. Validate with V$SGA_TARGET_ADVICE, V$PGA_TARGET_ADVICE and AWR after a representative workload, and check the HugePages count with Oracle's hugepages_settings.sh script once the instance is running.

Save results:

Default PGA_AGGREGATE_LIMIT and expected PGA vs PROCESSES

Sweeps PROCESSES parameter from 200 to 6000, holding your other inputs fixed.

Get your memory settings reviewed

Send us your init parameters and an AWR report from peak hours and we will check the SGA, PGA and HugePages settings against your actual workload.

Optional. The tool and every export work without this. No spam.

Was this tool useful?

How to use it

Enter the physical RAM, how much to keep back for the OS and other software, and what share of the rest belongs to this instance. Pick an SGA share: 70% suits most ERP OLTP databases, while batch heavy or reporting databases with large sorts and hash joins need more PGA. Enter the PROCESSES init parameter and a realistic average PGA per process. The calculator returns SGA_TARGET, PGA_AGGREGATE_TARGET, the default PGA_AGGREGATE_LIMIT, the expected PGA at full PROCESSES and the HugePages count. If the warning output shows 1, either PROCESSES is far higher than needed or PGA_AGGREGATE_TARGET is too small for the connection count.

Reserve = max(4 GB, RAM x reserve %).
Instance memory = (RAM - reserve) x share.
SGA_TARGET = instance memory x SGA share; PGA_AGGREGATE_TARGET = the rest.
HugePages = ceil(SGA in MB / 2).

The PGA_AGGREGATE_LIMIT default rule

In 19c, when MEMORY_TARGET is not set, PGA_AGGREGATE_LIMIT defaults to 200% of PGA_AGGREGATE_TARGET, but never less than 2 GB and never less than 3 MB times PROCESSES (5 MB times PROCESSES for a RAC instance). If MEMORY_TARGET is set, the default is MEMORY_MAX_TARGET. When the limit is reached, Oracle terminates the calls or sessions using the most untunable PGA, so ERP batch jobs can fail with ORA-04036. With a high PROCESSES value the 3 MB rule can push the default limit well above what the server can supply, which the chart shows as PROCESSES grows. Set the limit explicitly if the default is unrealistic for your RAM.

HugePages and AMM on Linux

Automatic Memory Management (MEMORY_TARGET) is not compatible with Linux HugePages, so use Automatic Shared Memory Management: set SGA_TARGET and PGA_AGGREGATE_TARGET and leave MEMORY_TARGET and MEMORY_MAX_TARGET unset. Configure vm.nr_hugepages in /etc/sysctl.conf, raise the memlock limit for the oracle user in /etc/security/limits.conf, and consider USE_LARGE_PAGES=ONLY so the instance refuses to start rather than silently using small pages. Oracle also recommends disabling Transparent HugePages for database servers. Check the alert log at startup: it reports how much of the SGA was allocated in large pages.

Worked example and how to check it

A 256 GB server with a 10% reserve leaves 230.4 GB for one instance. At 70% SGA that is about 161 GB SGA_TARGET and 69 GB PGA_AGGREGATE_TARGET. With PROCESSES=1500 the default limit is max(2, 138, 4.4) = about 138 GB, which is more than the server can give, so set PGA_AGGREGATE_LIMIT explicitly, for example near 1.2 to 1.5 times the target. HugePages needed: 82,576. After a week of real load, query V$SGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE: if ESTD_DB_TIME in the SGA advice drops for a larger size, or ESTD_OVERALLOC_COUNT in the PGA advice is above zero at the current target, move memory accordingly.

Frequently Asked Questions

What is the default PGA_AGGREGATE_LIMIT in Oracle 19c?

Without MEMORY_TARGET, it is 200% of PGA_AGGREGATE_TARGET, with a floor of 2 GB and a floor of 3 MB times PROCESSES (5 MB times PROCESSES on RAC). If PGA_AGGREGATE_TARGET is explicitly 0 it is 90% of physical memory minus the SGA. With MEMORY_TARGET set it equals MEMORY_MAX_TARGET.

Should I use MEMORY_TARGET for an ERP database on Linux?

Usually not. MEMORY_TARGET (AMM) cannot use HugePages on Linux, and an ERP database with a large SGA benefits from HugePages through lower page table overhead and no SGA swapping. Use ASMM with SGA_TARGET and PGA_AGGREGATE_TARGET instead, and size from advisor views after a real workload.

How do I calculate the number of HugePages for Oracle?

Divide the total SGA of all instances on the host, in MB, by the HugePage size (normally 2 MB, check Hugepagesize in /proc/meminfo) and round up, then add a little headroom. Oracle's hugepages_settings.sh script calculates it from the running instances' shared memory segments, which is the safest final check.

How much memory should I leave for the operating system?

A common practice is around 10% of RAM with a minimum of a few GB, more if backup agents, monitoring or ERP application tiers share the server. Watch free memory and swap activity with vmstat or sar after go live; any regular swapping means the reserve or the PGA is too small.

If you want your DBAs to ask the database and AWR these questions in plain English on your own servers, we can set up a private AI layer for that.

Talk to an Expert Explore More Free Tools