内存

 1、ASMM和AMM

由于AMM不支持HugePage，而ASMM支持HugePage，所以，在生产库上强烈推荐使用ASMM.
在Oracle 10g中，必须将STATISTICS_LEVEL参数设置为TYPICAL（默认值）或者ALL才能启用ASMM功能，如果将其值设置为BASIC，那么会禁用很多新特性，比如像AWR、ASMM等。
当LOCK_SGA初始化参数的值设置为TRUE时，不能启用AMM，该参数的值默认为FALSE。

使用AMM经常出现的一个错误是“ORA-00845: MEMORY_TARGET not supported on this system”。
这个错误原因是操作系统不支持MEMORY_TARGET参数或/dev/shm的大小设置不正确。解决方法就是将/dev/shm的值增大，至少需要大于数据库参数MEMORY_MAX_TARGET的值。修改步骤如下：
[root@LHRDB ~]# cat /etc/fstab | grep tmpfs    #查看/dev/shm大小
tmpfs                   /dev/shm                tmpfs   defaults,size=1G 0 0
[root@LHRDB ~]# mount -o remount,size=4G /dev/shm     #临时修改/dev/shm大小
[root@LHRDB ~]# vi /etc/fstab  #永久修改/dev/shm大小， tmpfs /dev/shm tmpfs defaults,size=4G 0 0

---每个动态组件分配的内存大小

SET LINESIZE 1000 PAGESIZE 1000
COLUMN component FORMAT A30
SELECT inst_id,
component,
        ROUND(current_size/1024/1024) AS current_size_mb,
        ROUND(min_size/1024/1024) AS min_size_mb,
        ROUND(max_size/1024/1024) AS max_size_mb
FROM gv$memory_dynamic_components
WHERE current_size != 0
ORDER BY component;

---记录内存变更记录

SET LINESIZE 1000 PAGESIZE 1000
COLUMN parameter FORMAT A25
SELECT start_time,
end_time,
component,
oper_type,
oper_mode,
parameter,
ROUND(initial_size/1024/1024) AS initial_size_mb,
ROUND(target_size/1024/1024) AS target_size_mb,
ROUND(final_size/1024/1024) AS final_size_mb,
status
FROM v$memory_resize_ops
ORDER BY start_time;

大页内存

在没有使用大页的系统上，经常可能会发现几十上百G的页表，严重情况下，系统CPU的sys部分的消耗非常大，这些都是没使用大页的情况下的一些症状。

---大页的缺点
要预先分配
不够灵活，甚至需要重启主机
如果分配过多，会造成浪费，不能被其他程序使用。

HugePages on Oracle Linux 64-bit (文档 ID 361468.1),AIX页表共享，一般不用设置大页。

如果数据库使用MANUAL方式管理SGA，需要改为AUTO方式，即将SGA_TARGET_SIZE设置为大于0的值。对于11g，由于HugePage只能用于共享内存，不能用于PGA，所以不能使用AMM，
即不能设置MEMORY_TARGET为大于0，只能分别设置SGA和PGA，SGA同样只能是AUTO方式管理。

2、PGA

程序全局区(PGA)是一个专用非共享内存区，其中包含服务器进程的数据及控制信息。每个服务器进程都有独立的 PGA。PGA 只能由相应的服务器进程访问，并且只有代表该服务器进程的 Oracle 代码可以读取它。
开发人员的代码不能访问 PGA。每个 PGA 都包含堆栈空间。在专用服务器环境中，连接到数据库实例的每个用户都有单独的服务器进程。对于这种类型的连接，PGA 包含一个名为用户全局区(UGA)的内存细分部分。
UGA 包括以下部分：
• 游标区，用于存储游标的运行时信息
• 用户会话数据存储区，用于存储有关会话的控制信息
• SQL 工作区，用于处理 SQL 语句，其中包括：
- 排序区，用于对数据排序的函数，如 ORDER BY 和 GROUP BY
- 散列区，用于执行表的散列联接
-位图创建区，用于创建数据仓库常用的位图索引
- 位图合并区，用于解析位图索引计划的执行
在共享服务器环境中，多个客户机用户共享服务器进程。在这种模式下，UGA 将移入 SGA（共享池，如果经过配置也可是大型池），PGA 仅包含堆栈空间。

一般情况下对于PGA的大量使用有如下几种操作：

hash 对于hash join操作，hash桶所占用的内存就在进程的私有PGA内存中，而不是在共享内存SGA中，如果使用PGA手工管理的话，可以通过HASH_AREA_SIZE参数来动态调整会话进行hash操作能够使用的内存量。

sort 对于排序操作，例如查询语句里的order by、创建索引的排序操作等占用的内存也在PGA中，如果使用PGA手工管理，可以通过SORT_AREA_SIZE参数动态调整会话排序操作可以使用的内存量。

parallel 并行操作简直可以说是PGA内存的杀手，每一个并行进程都能使用到最多2G的PGA内存，当然Oracle会确保所有的并行slave使用的PGA内存不能超过PGA_AGGREGATE_TARGET的一半。

Oracle每个进程的PGA占用不会太大，按照10M计算是个合理安全的值。AIX下可以大一些，按照每个15M-20M计算。

PGA（Program Global Area，程序全局区）是单个 Oracle 进程使用的内存区域，为每个连接到Oracle 数据库的用户进程保留的内存，不属于实例的内存结构。它含有单个进程工作时需要的数据和控制信息。
PGA 是非共享的，只有服务进程本身才能够访问它自己的 PGA 区。

PGA 有如下几个组件：
1 Private SQL Area（私有 SQL 区）：参考 Shared Pool 部分的介绍。
2 Cursor and SQL Areas（游标和 SQL 区）：Oracle Pro*C 程序（Pro*C 是 Oracle 提供的应用程序专用开发工具，它以 C 语言为宿主语言，能在 C 程序中嵌入 SQL 语句，进行数据库操作。）
的应用程序开发人员或 Oracle 调用接口（Oracle Call Interface，OCI）程序可以显式打开游标或处理私有 SQL区。
3 Session Memory（会话内存）：保存会话的变量（例如，登录信息）及其他与会话相关的信息。在共享服务器模式下，Session Memory 是共享的。
4 Work Area（工作区）：PGA 的一大部分被分配给 Work Area，用来执行如下操作：
 基于排序的操作，GROUP BY、ORDER BY、ROLLUP 和窗口函数。由于排序需要内存空间，Oracle 利用该内存排序数据，这部分空间称为排序区。排序区存在于请求排序的用户进程的内存中，
该空间的大小为适应排序数据量的大小，可增长，但受参数 SORT_AREA_SIZE 所限制。
 HASH 连接，大小受参数 HASH_AREA_SIZE 所限制
 位图合并，大小受参数 BITMAP_MERGE_AREA_SIZE 所限制
 位图创建，大小受参数 CREATE_BITMAP_AREA_SIZE 所限制
 批量装载操作使用的写缓存

PGA 和 SGA 最明显的差别在于，PGA 不是共享内存，是私有不共享的。用户对数据库发起的无论查询还是更新的任何操作，都是在 PGA 先预处理，然后接下来才进入实例区域，
由 SGA 和系列后台进程共同完成用户发起的请求。PGA起到的具体作用主要有三点：第一，保存用户的连接信息，如会话属性、绑定变量等；第二，保存用户权限等重要信息，当用户进程与数据库建立会话时，
系统会将这个用户的相关权限查询出来，然后保存在这个会话区内；第三，当发起的指令需要排序的时候，PGA 正是这个排序区，如果在内存中可以放下排序的尺寸，就在内存 PGA 区内完成，如果放不下，
超出的部分就在临时表空间中完成排序，也就是在磁盘中完成排序。

（1）WORKA REA _SIZE_POLICY 参数用于开关 PGA 内存自动管理功能。
当 W ORKA REA _SIZE_POLICY 设置为 manual时，PGA 的内存分配还是使用 sort_area_size等参数来分配，设置为 auto 时，sort_area_size 等参数将被忽略。

能进行完全内存操作的workarea大小叫做 optimal（cache）size与之对应的还有onepass size和multipass size。
optimal：所有操作都在内存中进行。
onepass：使用最小写磁盘操作,大部分在内存中进行。
multipass：当 w orkarea 太小的话将会发生大量磁盘操作，性能急剧下降。

SELECT NAME,
VALUE,
100 *
(VALUE / DECODE((SELECT SUM(VALUE)
FROM v$sysstat
WHERE NAME LIKE'workarea executions%'),
0,
NULL,
(SELECT SUM(VALUE)
FROM v$sysstat
WHERE NAME LIKE 'workarea executions%'))) pct
FROM v$sysstat
WHERE NAME LIKE 'workarea executions%';

这里sort和hash join 还是有点区别，当进行sort操作的话，分配的内存介于optimal与onepass之间，语句的响应时间是不会随着内存的增加而减少的。但是如果是 hash join，那么一旦增加内
存，它的响应时间将会随之减少，这与 sort、hash join 的内部执行方式有关

---expect size 不能超过 5% pga_aggregate_target大小或 100M B，并行操作下不能超过 30% pga_aggregate_target的大小
为什么不能用到 5% pga_aggregate_target=0.05*10GB=500M B 的内存呢?这是由于有隐藏参数_pga_m ax_size 来控制，每个 session 只能用到一半_pga_m ax_size 值大小的内存，
pga_m ax_size 默认的大小为 200MB

set linesize 120
col name for a30
col value for a20
col describ for a60
set pages 3000
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%_pga_max_size%';

一个process能够分配的最大内存(串行操作)的规则:
10gR1之前，对于串行操作(非并行)一个process能够分配的最大的内存为min(5%pga_aggregate_target,100m)
10gR2之后，对于串行操作(非并行)一个process能够分配的最大内存有如下规则:
如果pga_aggregate_target<=500m,那么最大的内存为20%*pga_aggregate_target.
如果500m<pga_aggregate_target<=1000m,那么最大内存为100m.
如果1000m<pga_aggregate_target<=2.5G,那么最大内存为10%*pga_aggregate_target.
如果pga_aggregate_target>2.5G,那么最大内存为2.5G.

set linesize 120
col name for a30
col value for a20
col describ for a60
set pages 3000
SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM SYS.x$ksppi x, SYS.x$ksppcv y WHERE x.indx = y.indx AND x.ksppinm LIKE '%_smm_max_size%';

alter system set workarea_size_policy=auto;
alter system set pga_aggregate_target=10M;
select distinct * from a where rownum<500000;       ---执行一个排序
SELECT sql_text,operation_type,POLICY,last_memory_used/1024/1024,last_execution,last_tempseg_size FROM v$sql l, v$sql_workarea a 
WHERE l.hash_value = a.hash_value AND sql_text = 'select distinct * from a where rownum<500000';             ---查询语句占用内存空间

show parameter area_size
show parameter workarea_size_polic;
select description,dest from x$messages where description like 'SQL Memory%';

SELECT PGA_TARGET_FOR_ESTIMATE/1024/1024 PGAMB,PGA_TARGET_FACTOR P_TR_FCT,ESTD_PGA_CACHE_HIT_PERCENTAGE E_P_C_HIT_PRCT,ESTD_OVERALLOC_COUNT E_OR_CNT FROM V$PGA_TARGET_ADVICE;
该视图的E_OR_CNT列表示需要额外分配的PGA内存,如果此数值不是0,就表示PGA_AGGREGATE_TARGE设置得太小,需要调整

 

SELECT pga_target_factor factor,
low_optimal_size / 1024 low,
ROUND(high_optimal_size / 1024) high,
estd_optimal_executions estd_opt,
estd_onepass_executions estd_op,
estd_multipasses_executions estd_mp,
estd_total_executions estd_exec
FROM v$pga_target_advice_histogram WHERE pga_target_factor = 0.25 AND estd_total_executions > 0;

 ---当前一个process消耗最大的内存

 select max(pga_used_mem)/1024/1024M from v$process;

 ---当前一个process消耗最小的内存

select min(pga_used_mem)/1024/1024M from v$process where pga_used_mem>0;

---当前process一共消耗的PGA
select sum(pga_used_mem)/1024/1024 from v$process;
select sum(value)/1024/1024 Mb from  v$sesstat s, v$statname n where n.STATISTIC# = s.STATISTIC# and name = 'session pga memory';

---查看某个session 的pga内存使用
select p.username,p.spid,p.program,pm.category,pm.used,pm.allocated,pm.max_allocated from v$process p ,v$process_memory pm where p.pid=pm.pid and p.pid = &pid;

---查询该会话所占用的内存分布情况：
select spid,USERNAME,PGA_USED_MEM ,PGA_ALLOC_MEM,PGA_FREEABLE_MEM, PGA_MAX_MEM from v$process;

 select * from v$process_memory where pid = 120;

 

alter system set sga_max_size=24G scope=spfile;
alter system set sga_target=24G scope=spfile;

alter system set WORKAREA_SIZE_POLICY = AUTO
alter system set pga_aggregate_limit=10G scope=both;
alter system set PGA_AGGREGATE_TARGET =8G

ORA-4030报错发生的原因是Oracle以及操作系统对PL/SQL程序中使用的内存有上限（在缺省配制下为4GB）

解决方案建议:
根据前文的说明，对这一问题的根本解决办法是对PL/SQL代码进行优化。在剩余物理内存充足的情况下，也可以通过下面的配置，以突破缺省的4GB的限制：修改操作系统参数vm.max_map_count，由缺省的65536增大为比如20000。

$ more /proc/sys/vm/max_map_count
$ sysctl -w vm.max_map_count=200000 (for example)

修改数据库参数：
_use_realfree_heap=TRUE
_realfree_heap_pagesize_hint = 262144

缺省的realfree allocator pagesize是64KB，所以缺省最大的4GB。修改为256KB（262144）后，最大限制为16GB。

 

2、若 SGA 分配得很大，但没有使用 VLM ，几乎很靠近 3GB 的时候，大约只留下 20M B 左右。这样当一个进程进行 hash join，由 于 pga_aggregate_target 设 置 为 1GB ， Oracle 默 认 单 个 进 程 使 用 PGA 可 以 达 到pga_aggregate_target * 5% = 50M B，则使得在进行 hash join 的时候出错：ORA-04030: out of process memory when trying to allocate 254476 bytes (hash-joinsubh,kllcqas:kllsltba)

64位操作系统不存在此问题

 3、UGA

UGA（User Global Area）保存了会话信息，会话总能访问这部分内存。UGA 的位置取决于会话连接到Oracle 的方式。如果是专用服务器连接，那么 UGA 在 PGA 中创建；如果是共享服务器连接，
那么 UGA 在SGA 的 Large Pool 中创建，若 Large Pool 没有分配则在 Shared Pool 中分配。
PGA 和 UGA 两者间的区别跟一个进程和一个会话之间的区别是类似的。尽管说进程和会话之间一般都是一对一的关系，但实际上比这个更复杂。一个很明显的情况是 MTS 配置，会话往往会比进程多得多。
在这种配置下，每一个进程会有一个 PGA，每一个会话会有一个 UGA。PGA 所包含的信息跟会话是无任何关联的，而 UGA包含的信息是以特定的会话为基础的。