LLI Wiki — My Digital Knowledge Base

Oracle on Linux: huge pages, max_map_count and memory settings (ORA-04030)

Linux and Oracle settings (huge pages, vm.max_map_count, _realfree_heap_pagesize, PGA) and apply procedure for a memory-hungry Oracle server.

On an Oracle server running on Linux with very high memory use (ORA-04030, out of process memory type errors), three levers are worth checking: huge pages, vm.max_map_count and swappiness. This memo collects the Linux and Oracle settings used and the procedure to apply them.

References: Configuring Huge Pages for Oracle on Linux (oracle-base), Change default hugepage size (golinuxcloud), Bulk collect and memory limits (performatune), SUSE KB 000016692.

Check the current state

# user limits (current and hard)
ulimit -a
ulimit -Ha

# huge pages
grep Huge /proc/meminfo

# max_map_count
more /proc/sys/vm/max_map_count

vm.max_map_count

The kernel parameter vm.max_map_count limits the number of memory areas (maps) a process can own. It is set in /etc/sysctl.conf:

# persistent value: add the line to /etc/sysctl.conf
vm.max_map_count=200000

# apply the file
sysctl -p

# apply immediately without a reboot
sysctl -w vm.max_map_count=200000

Sizing rule from the note

The note assumes 1 map = 64 bytes, so:

RAM (GB) max_map_count
4 65536
8 131072
16 262144
20 327680
24 393216
32 524288
40 655360
48 786432

Hidden Oracle parameters

_realfree_heap_pagesize_hint, _realfree_heap_pagesize and _use_realfree_heap are undocumented parameters: change them only on Oracle Support's advice. Syntax examples:

ALTER SYSTEM SET "_realfree_heap_pagesize_hint"=524288 SCOPE=SPFILE;
ALTER SYSTEM SET "_realfree_heap_pagesize_hint"=262144 SCOPE=SPFILE SID='*';
ALTER SYSTEM SET "_realfree_heap_pagesize_hint"=262144 SCOPE=SPFILE SID='ORCL';

Read the value of a hidden parameter (connected as SYSDBA):

SELECT par.ksppinm name, val.ksppstvl value, par.ksppity type, val.ksppstdf is_default,
       DECODE(BITAND(par.ksppiflg/256,1), 1, 'True', 'False') is_session_modifiable,
       DECODE(BITAND(par.ksppiflg/65536,3), 1, 'Immediate', 2, 'Deferred', 3, 'Immediate', 'False') is_system_modifiable,
       par.ksppdesc description
FROM x$ksppi par, x$ksppsv val
WHERE par.indx = val.indx
  AND par.ksppinm = '_realfree_heap_pagesize';
-- same with '_use_realfree_heap'
Level Parameter Value
Linux vm.nr_hugepages 16388
Linux vm.max_map_count 524288
Oracle _realfree_heap_pagesize 524288
Oracle _use_realfree_heap TRUE
Oracle pga_aggregate_target 6G
Oracle pga_aggregate_limit 14G

These values depend on the server memory: recompute them for each machine.

Procedure

  1. Shut down the database and the server, take a VM snapshot, boot again.

  2. Check max_map_count, then add to /etc/sysctl.conf (with sudo):

    vm.max_map_count=524288
    vm.nr_hugepages=16388
    

    No-reboot option: sysctl -w vm.max_map_count=524288 and sysctl -w vm.nr_hugepages=16388. Huge pages are more reliably reserved at boot, so reboot afterwards.

  3. After the reboot, check and record /proc/sys/vm/max_map_count and /proc/sys/vm/nr_hugepages.

  4. Back up the pfile and spfile (. oraenv, then cd $ORACLE_HOME/dbs and copy the files with a date suffix).

  5. Change the memory settings:

    alter system set pga_aggregate_limit=14G scope=spfile;
    alter system set pga_aggregate_target=6G scope=spfile;
    
  6. Record the current values of _realfree_heap_pagesize and _use_realfree_heap with the query above.

  7. Create a working pfile, shut the instance down, edit it:

    create pfile='$ORACLE_HOME/dbs/work_pfile.ora' from spfile;
    shutdown immediate
    

    Add the line _realfree_heap_pagesize=524288 and, if _use_realfree_heap is FALSE, the line _use_realfree_heap=TRUE.

  8. Test startup with this pfile and check both parameters again:

    startup pfile='$ORACLE_HOME/dbs/work_pfile.ora';
    
  9. If everything is fine, rebuild the spfile from the pfile, restart normally and check again:

    create spfile from pfile='$ORACLE_HOME/dbs/work_pfile.ora';
    shutdown immediate
    startup
    

Notes

Mastodon LLIMastodon SocialFosstodon