Oracle Database Memory Management

名词	全称	管什么	怎么开
AMM	Automatic Memory Management	SGA + PGA 一起自动调	设 MEMORY_TARGET
ASMM	Automatic Shared Memory Management	只自动调 SGA 内部组件，PGA 单列	设 SGA_TARGET + PGA_AGGREGATE_TARGET
HugePages	Linux 大页内存	用 2MB 大页替代 4KB 小页，减少 TLB miss	系统级 vm.nr_hugepages

AMM 与 HugePages 不兼容。生产环境（尤其 Linux），选 ASMM + HugePages，别用 AMM。

Automatic Memory Management (AMM)

Automatic Shared Memory Management (ASMM)

-- 当前内存管理参数
SELECT name, value/1024/1024/1024 AS gb
FROM   v$parameter
WHERE  name IN ('memory_target', 'memory_max_target',
'sga_target', 'pga_aggregate_target', 'sga_max_size');

-- SGA/PGA 实际峰值（决定切完设多少，从 AWR 或 v$memory_dynamic_components 看）
SELECT component, ROUND(current_size/1024/1024/1024, 2) AS cur_gb,
       ROUND(max_size/1024/1024/1024, 2)     AS max_gb
FROM   v$memory_dynamic_components
WHERE  current_size >0;

-- SGA、PGA 按业务峰值各定一个值（例：SGA 24G、PGA 8G）
ALTER SYSTEM SET sga_max_size=24G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target=24G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=8G SCOPE=SPFILE;

-- AMM 关掉，关键一步
ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;

-- SGA 各组件自适应是否正常（ASMM 生效标志）
SELECT component, ROUND(current_size/1024/1024/1024, 2) AS gb
FROM   v$sga_dynamic_components
WHERE  current_size >0
ORDER BY gb DESC;

-- PGA 命中情况（无异常排序溢写即可）
SELECT name, value FROM v$sysstat WHERE name LIKE 'workarea executions%';

HugePages

SQL> SHOW PARAMETER use_large_pages;

值	含义
TRUE（默认）	能用大页就用，用不了静默退回小页（就是你 "配了个寂寞" 的模式）
FALSE	彻底不用大页
ONLY	必须用大页，没有就启动失败（测试利器：一验便知大页到底通没通）

Check the hugepages.

~]# sysctl -a 2>/dev/null | grep "vm.nr_hugepages"
vm.nr_hugepages = 3584
vm.nr_hugepages_mempolicy = 3584

~]# grep HugePages /proc/meminfo
AnonHugePages:         0 kB
HugePages_Total:    3584
HugePages_Free:     1799
HugePages_Rsvd:        0
HugePages_Surp:        0

Check the max locked memory.

~]# find /etc/security/limits.* -type f -exec grep memlock {} \;
#        - memlock - max locked-in-memory address space (KB)
grid   hard   memlock    10869992
grid   soft   memlock    10869992
oracle   hard   memlock    10869992
oracle   soft   memlock    10869992

~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 47225
max locked memory       (kbytes, -l) 10869992
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 2047
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

~]$ ulimit -l
10869992

Reference

・ How to Setting Oracle Huagepages Parameters