Oracle数据库之内存管理

kexiong

大页内存

---

备注
备注1：不能使用oracle自动内存管理；
备注2：目的是减少swap的使用；
备注3：建议SGA大于8G；
备注4：只限于linux系统；
备注5：不会使用/dev/shm空间；
备注6：需要设置内存锁；

如果配置了大页内存但没有被oracle使用
1.sga_max_size超过了大页内存；
2.没有设置内存锁；
3.没有取消自动内存管理；

查看系统大页内存的使用情况
cat /proc/meminfo | grep -i HugePages
备注1：一般大页内存的默认大小为2M；
备注2：单个内存页的大小有OS内核决定，不能修改；

设置大页内存的数量
查看sga_max_size
show parameter sga_max_size
计算大页内存的总内存数(单位全部为M)
sga_max_size(MB)/每个大页内存的大小(默认2M) +200(预留)
配置大页内存参数
vi /etc/sysctl.conf
添加或修改为如下内容：
vm.nr_hugepages = 600
使配置生效
sysctl -p
查看大页内存的使用情况
cat /proc/meminfo | grep -i HugePages
备注1：则不断的"sysctl -p"，直到达到设定值为止；或者重启系统；

设置内存锁
配置limits.conf文件
vi /etc/security/limits.conf
添加或修改为如下内容：
oracle soft memlock -1
oracle hard memlock -1
备注：-1为不限制，如果设置，则memlock的数据要大于大页内存的数量
查看修改(oracle用户)
ulimit -l

配置swap空间
清空swap空间
swapoff -a && swapon -a
修改sysctl.conf文件
vi /etc/sysctl.conf
添加或修改为如下内容：
vm.swappiness=0
使之生效
sysctl -p


lock_sga与pre_page_sga

---

备注
备注1：不能使用oracle自动内存管理；
备注2：目的是减少swap的使用；
备注3：建议SGA小于8G；
备注4：不会使用/dev/shm空间；
备注5：与大页内存二选一；
备注6：需要设置内存锁；

描述
lock_sga：把sga被锁定在物理内存中，这样可以防止sga被换出到swap中
pre_page_sga：在数据库启动的时候将整个sga读取到物理内存，而不走swap空间，从而有效的提高数据库效率；但会增加数据库的启动时间。

设置内存锁
配置limits.conf文件
vi /etc/security/limits.conf
添加或修改为如下内容：
oracle soft memlock -1
oracle hard memlock -1
备注：-1为不限制，如果设置，则memlock的数据要大于大页内存的数量
查看修改(oracle用户)
ulimit -l

修改参数
sqlplus / as sysdba
SQL>alter system set lock_sga = true scope=spfile;
SQL>alter system set pre_page_sga = true scope=spfile;
SQL>shutdown immediate;
SQL>startup open;

错误
错误1
SQL>startup;
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memory
解决方法：设置内存锁；


自动内存管理(系统才上线的数据库内存最佳配置)

---

内存设置建议
可以将服务器总物理内存的80%分配给数据库(SGA+PGA)

PGA内存管理参数
通过两个参数来实现自动PGA内存管理(v$parameter)
workarea_size_policy——默认是是AUTO，也就是自动分配内存；
pga_aggregate_target——PGA手动分配内存大小，一般设置为SGA大小的30%；

查看PGA实际大小
set pagesize 100
set linesize 100
col NAME for a22
col Used_Size for a9
select NAME, round(VALUE / 1024 / 1024 / 1024 , 2)||' GB' as Used_Size
  from v$pgastat
where name like '%PGA allocated';

SGA内存管理参数
查看SGA的实际大小
set pagesize 100
set linesize 100
col Pool_Name for a15
col Used_Size for a9
select nvl(pool, name) as Pool_Name,
       round(sum(bytes) / 1024 / 1024 / 1024 , 2) || ' GB' as Used_Size
  from v$sgastat
group by nvl(pool, name)
order by nvl(pool, name);

db_keep_cache_size与db_recycle_cache_size

1.设置keep|recycle池大小

2.使用keep|recycle池和不使用
alter table|index .... storage(buffer_pool keep|recycle|default);
备注：db_keep_cache_size——可以使表长期驻留在这个区域

3.缓存|不缓存keep池中
alter table table_name cache|nocache;

4.刷新缓存
alter system flush shared_pool

5.查看表是否已经缓存到keep池中
select table_name,CACHE,BUFFER_POOL from user_tables;

自动内存管理
设置PGA参数
alter system set workarea_size_policy=auto;
设置PGA大小
alter system set pga_aggregate_target=0;
设置SGA大小
alter system set sga_target=0;
设置共享池大小
alter system set shared_pool_size=0;
设置数据库缓冲区大小
alter system set db_cache_size=0;
设置大池大小
alter system set large_pool_size=0;
设置流池大小
alter system set streams_pool_size=0;
设置JAVA池大小
alter system set java_pool_size=0;
设置数据库最大总内存值
alter system set memory_max_target=20G scope = spfile;
设置数据库总内存参数
alter system set memory_target=20G;
备注1：memory_target=sga_target+pga_aggregate_target
备注2：memory_target<=memory_max_target

取消自动内存管理
alter system reset memory_target scope=spfile;
alter system reset memory_max_target scope=spfile;
OLTP系统
alter system set sga_target = (总物理内存* 0.8) * 0.7 scope=spfile;
alter system set sga_max_size = (总物理内存* 0.8) * 0.7 scope=spfile;
alter system set pga_aggregate_target= (总物理内存 * 0.8) * 0.3 scope=spfile;
OLAP系统
alter system set sga_target = (总物理内存* 0.8) * 0.5 scope=spfile;
alter system set sga_max_size = (总物理内存* 0.8) * 0.5 scope=spfile;
alter system pga_aggregate_target= (总物理内存 * 0.8) * 0.5 scope=spfile;


内存顾问(系统运行一段时间后的内存配置参考)

---

设置内存顾问参数
启用内存顾问需要把参statistics_level设置为ALL或者TYPICAL

PGA内存顾问
set pagesize 100
set linesize 100  
SELECT round(pga_target_for_estimate / 1024 / 1024, 0) as estimate_pga_mb, --预测的PGA内存值
       pga_target_factor as pga_rate, --若为1，则pga_target_for_estimate表示当前的PGA内存值
       estd_pga_cache_hit_percentage, --PGA的估算得到的Cache命中率的百分比
       estd_overalloc_count --若为0表示可以消除PGA的过载，也就是可以将PGA内存值设置为pga_target_for_estimate
  FROM v$pga_target_advice;

备注1：pga_target_for_estimate为预测的PGA内存值
备注2：pga_target_factor为预测PGA值与当前实际PGA值的比值，若为1，则表示pga_target_for_estimate就是当前的实际PGA值
备注3：如果estd_overalloc_count为0或者estd_pga_cache_hit_percentage为100时 ，那么就可以将pga内存值(pga_aggregate_target)设置为相应的pga_target_for_estimate值

SGA内存顾问
set pagesize 100
set linesize 100  
select sga_size as estimate_sga_mb, --预测的SGA内存值
       sga_size_factor as sga_rate, --若为1，则sga_size表示当前的SGA内存值
       estd_physical_reads --物理读的百分比
  from v$sga_target_advice;

备注1：sga_size为预测的SGA内存值
备注2：sga_size_factor 为预测SGA值与当前实际SGA值的比值，若为1，则表示sga_size就是当前的实际SGA值
备注3：当estd_physical_reads不在减少时，那么就可以将SGA内存值(sga_target)设置为相应的sga_size

总内存顾问
set pagesize 100
set linesize 100  
select memory_size as estimate_memory_mb, --预测的总内存值
       memory_size_factor, --若为1，则memory_size 表示当前的总内存值
       estd_db_time,
       estd_db_time_factor --消耗数据库时间的比例因子
  from v$memory_target_advice;

备注1：memory_size为预测的总内存值
备注2：memory_size_factor为预测总内存值与当前实际总内存值的比值，若为1，则表示memory_size就是当前的实际总内存值
备注3：当estd_db_time_factor不在减少时，那么就可以将总内存值(memory_target)设置为相应的memory_size

查看各个内存组件的大小
v$memory_dynamic_components