Oracle认证：Oracle内存结构研究-PGA篇

会计考友

一、概述
' g5 S4 ^0 I% ]8 c) e2 n0 R　　SGA，PGA，UGA都是Oracle管理的内存区。
　　SGA（System Global Area），即系统全局区，Oracle中最重要的内存区。
　　PGA（Process Global Area），即程序全局区，一个进程的专用的内存区。
/ @* F& X' K% c* M1 S$ u　　UGA（User Global Area），即用户全局区，与特定的会话相关联。
" r: ?2 N) x  P$ M/ E　　专用服务器连接模式，UGA在PGA中分配。
　　共享服务器连接模式，UGA在SGA中的Large Pool中分配。
　　如果采用专用服务器连接模式，PGA中包含UGA，其他区域用来排序，散列和位图合并。
4 E) [. n6 g; D4 M- d$ j　　简单来讲，PGA=UGA+排序区+散列区+位图合并区。
　　二、PGA的管理模式。
　　PGA分两种管理模式：
4 L# }* B4 h. n1 m" u0 t2 C: v　　1) 手动PGA内存管理，用户指定排序区和散列区所使用的内存，每个连接使用相同的内存。
　　2) 自动PGA内存管理，告诉Oracle可以使用的PGA的总量，由Oraclce根据系统负载决定具体分配。
　　9iR1时默认为手动PGA内存管理，9iR2以后默认为自动PGA内存管理。
　　PGA内存可以动态扩大和回收。
　　PGA内存管理模式由WORKAREA_SIZE_POLICY控制。
+ s# y. Y7 p  y$ Y　　1) 设为MANUAL，启用手动内存管理。
　　2) 设为AUTO，并且PGA_AGGREGATE_TARGET不为0时，启用自动内存管理。
　　三、手动PGA内存管理
, T7 n; K, ^0 V. w3 x0 B, ~9 @　　有三个参数对PGA影响最大。
% |+ S( o2 \3 m0 G2 l  g+ }　　SORT_AREA_SIZE：对信息排序所用的内存总量
7 P% s: i' g1 c, a　　SORT_AREA_RETAINED_SIZE：排序后在内存中保存排序信息的内存总量。
　　HASH_AREA_SIZE：存储散列列表所用的内存量。
　　下面对这三个参数进行说明：
　　1) SORT_AREA_SIZE：
4 l/ t/ U( j& E2 |) R$ Y! |　　如果SORT_AREA_SIZE设为512KB，SORT_AREA_RETAINED_SIZE也为512KB，则Oracle使用512KB的内存进行排序，排序后所有数据都留在内存中。
, Z7 {- ]& r" G% z$ \1 D　　2) SORT_AREA_RETAINED_SIZE：
! f6 Z. `. u6 C　　如果SORT_AREA_SIZE设为512KB，SORT_AREA_RETAINED_SIZE设为384KB，则Oracle使用512KB的内存进行排序，然后保留384KB的已排序数据，另外512KB-384KB=128KB的已排序数据会写到临时表空间中。
/ {4 u" i, A2 M; D( q　　如果SORT_AREA_RETAINED_SIZE没有设置，则它的值为0，但是实际保留的排序数据和SORT_AREA_SIZE相同。
　　3) HASH_AREA_SIZE：
　　一个大集合和另个集合进行连接时，会用到HASH_AREA_SIZE参数。较小的 表会放到这部分内存中作为驱动表，然后大表进行探索（PROBE）操作进行连接。如果HASH_AREA_SIZE过小会影响两个集合（表）连接时的性能。
　　注意点：
0 Q7 N7 }) ~1 C5 z1 N! a　　1) 如果需要排序的数据量大于SORT_AREA_SIZE，Oracle会分批进行排序。把当前已排序的数据保存到临时表空间中，然后对剩余的数据进行排序。最后，还会对这些保存在临时表空间中的已排序数据再进行排序，因为每次保存到临时表空间中的已排序数据只是部分数据的排序，对整体需排序的数据来说只是部分局部有序。
　　2) *_AREA_SIZE只是对某个操作的限制，一个查询可能有多个操作，每个操作都有自己的内存区。如果SORT_AREA_SIZE设为5MB，一个查询可能会有10个排序操作，这样一个查询会占用50MB的排序内存。
　　3) 3，*_AREA_SIZE内存的分配是按需分配。如果一个查询需要5MB内存进行排序，就算分配1G的SORT_AREA_SIZE也不会全部使用，只会使用需要的5MB的内存量。
5 H' [$ ~4 M/ T/ r9 Z  r* ]1 U　　四、自动PGA内存管理
　　要启用自动PGA内存管理，设置下列参数：
　　1，WORKAREA_SIZE_POLICY=AUTO
　　2，PGA_AGGREGATE_TARGET=非零
　　有关PGA_AGGREGATE_TARGET：
" {9 o0 ~, E, u/ P　　1) PGA_AGGREGATE_TARGET是一个目标值。连接数少的时候实际分配PGA内存会比它要小。连接数多的时候实际分配的PGA内存会比它要大，但是Oracle会努力保持总PGA保持在PGA_AGGREGATE_TARGET值内。
# b) `- {( |% E! q6 N　　例如，PGA_AGGREGATE_TARGET 设为300MB。5个用户连接时，每个用户可能分配10MB的PGA内存，共分配50MB的PGA内存。300个用户连接时每个用户可能分配1.3MB的 PGA内存，共分配390MB的PGA内存。当用户连接多时，Oracle会降低每个用户的PGA内存使用量。
& L$ y3 O" F/ |　　2) 一个串行查询（非并行查询）可能包括多个排序/散列操作，每个排序/散列操作最多使用5%的PGA内存。
/ G/ q2 o# ~" f4 T9 i, Y　　3) 一个并行查询最多可用到30%的PGA内存，无论有多少并行进程。
6 v* z# m4 \% _/ ~1 d) p5 f　　五、手动PGA内存管理与自动PGA内存管理
　　自动PGA内存管理相对于手动PGA内存管理有很多优点
　　1， 当用户连接少时
　　a) 手动PGA内存管理不管有多少可用内存都按照预设值进行分配。比如当前空闲内存为300MB，连接需要10MB的内存进行排序，而我们设定的排序区大小为5MB，导致虽然有足够的空闲内存却无法分配给当前连接，造成执行效率低下。
& n1 i- N* v  ~  f* s  Z　　b) 自动PGA内存管理会根据当前空闲内存来进行分配。当空闲内存为300MB，当前用户需要10MB内存进行排序，Oracle就会分配10MB内存给当前用户。
2 S' w% x. x+ E3 U: v. s　　2， 当用户连接多时
　　a) 手动PGA内存管理会完全按照预设值分配内存。如果物理内存总量为1G，排序区设为5MB，当有300个用户连接时，Oracle会分配1.5G的内存，这已经超过了我们的实际物理内存！
4 w5 {$ v$ P' v" |* y+ X! j　　b) 自动PGA内存管理会根据当前连接情况进行分配。如果物理内存总量为1G，PGA_AGGREGATE_TARGET为300MB，当用户数从10升到 300时，每个用户连接的内存会从满足需要的10MB慢慢减少到1.3MB，虽然最后总量也会超过PGA_AGGREGATE_TARGET，但比起手动 PGA内存管理要好很多了。
　　什么时候使用自动PGA内存管理？什么时候使用手动PGA内存管理？
/ W! ]% x; r) [　　白天系统正常运行时适合使用自动PGA内存管理，让Oracle根据当前负载自动管理、分配PGA内存。
　　夜里用户数少、进行维护的时候可以设定当前会话使用手动PGA内存管理，让当前的维护操作获得尽可能多的内存，加快执行速度。
  ?' B, o5 o, T3 A　　如：服务器平时运行在自动PGA内存管理模式下，夜里有个任务要大表进行排序连接后更新，就可以在该操作session中临时更改为手动PGA内存管理，然后分配大的SORT_AREA_SIZE和HASH_AREA_SIZE（50%甚至80%内存，要确保无其他用户使用），这样能大大加快系统运行速度，又不影响白天高峰期对系统造成的影响。

会计考友

　　六、操作命令
　　系统级更改：
　　ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
0 S6 m# |- w+ w( I# i　　ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100000000;
4 r: R: n7 r1 R( A　　ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE；
: q- P; D5 ?0 s. a. S* A, F# u　　ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE；
　　会话级更改
9 j/ }( ?. ]/ v. W$ w& ^　　ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}；
　　ALTER SESSION SET SORT_AREA_SIZE = 65536；
　　ALTER SESSION SET HASH_AREA_SIZE = 65536；
; H. G9 X2 G9 E/ b( Z/ a; G　　七、学以致用
　　1，排序区：
* ], p. z+ |- _) Q4 ^- r/ C- R9 c　　pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时排序所需时间
　　SQL> create table sorttable as select * from all_objects;
- [( x* W, u& U# M$ k5 P　　表已创建。
) l; c7 w/ L) E, |8 R+ H　　SQL> insert into sorttable (select * from sorttable);
/ Y1 P3 B8 ^/ p) t$ x2 w% x/ I& ~2 C　　已创建49735行。
9 D# s2 i, A0 g+ ~6 x- J# V　　SQL> insert into sorttable (select * from sorttable);
* K3 |$ ]. r7 ?: R　　已创建99470行。
* k% `. P. K& f: v; b　　SQL> set timing on;
' @( W6 o9 c* L* c　　SQL> set autotrace traceonly;
　　SQL> select * from sorttable order by object_id;
, `- Y$ D% ?0 t, ?4 r　　已选择198940行。
　　已用时间: 00: 00: 50.49
　　Session级修改排序区为30mb所需时间
% Y; c" p7 J( V0 F3 M) {! ]5 i/ n: L　　SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
, j5 b, B: u' B# g' ~　　会话已更改。
　　已用时间: 00: 00: 00.02
　　SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000;
4 e1 ]- I' w+ r, [3 P6 X　　会话已更改。
, C3 s8 }* F4 r. B% f4 c: k, z( ]& U　　已用时间: 00: 00: 00.01
8 s* t: M0 m6 p1 ]  A　　SQL> select * from sorttable order by object_id;
: h0 m7 B0 i: v3 S　　已选择198940行。
　　已用时间: 00: 00: 10.76
　　可以看到所需时间从50.49秒减少到10.31秒，速度提升很明显。
　　2，散列区：
9 r* `' r/ H/ ?  T1 U　　pga_aggregate_target为100MB，单个查询能用到5%也就是5MB时表连接所需时间
　　SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
$ _/ g4 {0 G! ?9 f; T5 @! [　　已选择49735行。
　　已用时间: 00: 00: 40.50
  r7 _+ \9 \& s9 M　　Session级修改散列区为30mb所需时间
　　SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL;
; w. [- ]7 q; E) N9 r　　会话已更改。
　　已用时间: 00: 00: 00.01
; I1 j% F6 A$ j: I/ M　　SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000;
+ B/ h* ^$ U  |) W* Q" J* [+ c$ Z　　会话已更改。
　　已用时间: 00: 00: 00.01
+ n' J, W1 |( q! ]0 O　　SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id;
7 ~9 Q' x7 c% a% y% e) v　　已选择49735行。
　　已用时间: 00: 00: 04.47
　　所需时间由40.50秒提升到4.47秒，效果同样很明显。
/ k" N3 L- K+ K; s, |; p+ C　　备注：以上实验皆执行全表扫描保证相关表读入缓冲区中，避免因数据没读入缓存造成误差。
　　结论：在9iR2版以后，PGA不再像以前那样困扰DBA了，Oracle会帮我们做好PGA的分配。但这并不意味着DBA不需要深入了解PGA了，掌握PGA并根据适当应用会让工作如虎添翼。