Oracle内存结构研究-PGA篇 |
|
一、概述 SGA，PGA，UGA都是Oracle治理的内存区 。 SGA(System Global Area)，即系统全局区，Oracle中最主要的内存区 。 PGA(Process Global Area)，即程序全局区，一个 历程的专用的内存区 。 UGA(User Global Area)，即消费者全局区，与特定的会话 有关联 。 专用服务器衔接模式，UGA在PGA中 调配 。 共享服务器衔接模式，UGA在SGA中的Large Pool中 调配 。 假如采纳专用服务器衔接模式，PGA中包括UGA， 其余区域用来排序，散列和位图合并 。 方便来讲，PGA=UGA+排序区+散列区+位图合并区 。 二、PGA的治理模式 。 PGA分两种治理模式： 1) 手动PGA内存治理，消费者指定排序区和散列区所 使用的内存，每个衔接 使用 雷同的内存 。 2) 自动PGA内存治理，告诉Oracle 可以 使用的PGA的总量，由Oraclce依据系统负载决定具体 调配 。 9iR1时默许为手动PGA内存治理，9iR2以后默许为自动PGA内存治理 。 PGA内存 可以动态 扩充和回收 。 PGA内存治理模式由WORKAREA_SIZE_POLICY操纵 。 1) 设为MANUAL，启用手动内存治理 。 2) 设为AUTO，而且PGA_AGGREGATE_TARGET不为0时，启用自动内存治理 。 三、手动PGA内存治理 有三个参数对PGA影响最大 。 SORT_AREA_SIZE：对信息排序所用的内存总量 SORT_AREA_RETAINED_SIZE：排序后在内存中 保留排序信息的内存总量 。 HASH_AREA_SIZE：存储散列列表所用的内存量 。 下面对这三个参数进行 注明： 1) SORT_AREA_SIZE： 假如SORT_AREA_SIZE设为512KB，SORT_AREA_RETAINED_SIZE也为512KB，则Oracle 使用512KB的内存进行排序，排序后所有数据都留在内存中 。 2) SORT_AREA_RETAINED_SIZE： 假如SORT_AREA_SIZE设为512KB，SORT_AREA_RETAINED_SIZE设为384KB，则Oracle 使用512KB的内存进行排序， 而后保留384KB的已排序数据，另外512KB-384KB=128KB的已排序数据会写到暂时表空间中 。 假如SORT_AREA_RETAINED_SIZE没有设置，则它的值为0，然而实际保留的排序数据和SORT_AREA_SIZE 雷同 。 3) HASH_AREA_SIZE： 一个大 集中和另个 集中进行衔接时，会用到HASH_AREA_SIZE参数 。较小的 表会放到这 部分内存中作为驱动表， 而后大表进行探究(PROBE)操作进行衔接 。假如HASH_AREA_SIZE过小会影响两个 集中(表)衔接时的性能 。 留神点： 1) 假如需求排序的数据量大于SORT_AREA_SIZE，Oracle会分批进行排序 。把目前已排序的数据 保留到暂时表空间中， 而后对 残余的数据进行排序 。最终，还会对这些 保留在暂时表空间中的已排序数据再进行排序，由于每次 保留到暂时表空间中的已排序数据只不过 部分数据的排序，对整体需排序的数据来说只不过 部分 部分有序 。 2) *_AREA_SIZE只不过对某个操作的 制约，一个 查问可能有多个操作，每个操作都有自己的内存区 。假如SORT_AREA_SIZE设为5MB，一个 查问可能会有10个排序操作，这样一个 查问会占用50MB的排序内存 。 3) 3，*_AREA_SIZE内存的 调配是按需 调配 。假如一个 查问需求5MB内存进行排序，就算 调配1G的SORT_AREA_SIZE也不会所有 使用，只会 使用需求的5MB的内存量 。 四、自动PGA内存治理 要启用自动PGA内存治理，设置下列参数： 1，WORKAREA_SIZE_POLICY=AUTO 2，PGA_AGGREGATE_TARGET=非零 有关PGA_AGGREGATE_TARGET： 1) PGA_AGGREGATE_TARGET是一个 指标值 。衔接数少的时候实际 调配PGA内存会比它要小 。衔接数多的时候实际 调配的PGA内存会比它要大，然而Oracle会 奋力 维持总PGA 维持在PGA_AGGREGATE_TARGET值内 。 例如，PGA_AGGREGATE_TARGET 设为300MB 。5个消费者衔接时，每个消费者可能 调配10MB的PGA内存，共 调配50MB的PGA内存 。300个消费者衔接时每个消费者可能 调配1.3MB的PGA内存，共 调配390MB的PGA内存 。当消费者衔接多时，Oracle会减低每个消费者的PGA内存 使用量 。 2) 一个串行 查问(非并行 查问)可能包括多个排序/散列操作，每个排序/散列操作最多 使用5%的PGA内存 。 3) 一个并行 查问最多可用到30%的PGA内存，无论有多少并行 历程 。 五、手动PGA内存治理与自动PGA内存治理 自动PGA内存治理 有关于手动PGA内存治理有众多 长处 1， 当消费者衔接少时 a) 手动PGA内存治理 无论有多少可用内存都依照预设值进行 调配 。 比方目前空暇内存为300MB，衔接需求10MB的内存进行排序，而我们设定的排序区大小为5MB，招致 固然有足够的空暇内存却 无奈 调配给目前衔接，造成执行效率低下 。 b) 自动PGA内存治 理睬依据目前空暇内存来进行 调配 。当空暇内存为300MB，目前消费者需求10MB内存进行排序，Oracle就会 调配10MB内存给目前消费者 。 2， 当消费者衔接多时 a) 手动PGA内存治 理睬 彻底依照预设值 调配内存 。假如物理内存总量为1G，排序区设为5MB，当有300个消费者衔接时，Oracle会 调配1.5G的内存，这已经超过了我们的实际物理内存！ b) 自动PGA内存治 理睬依据目前衔接状况进行 调配 。假如物理内存总量为1G，PGA_AGGREGATE_TARGET为300MB，当消费者数从10升到300时，每个消费者衔接的内存会从满足需求的10MB 渐渐削减到1.3MB， 固然最终总量也会超过PGA_AGGREGATE_TARGET，但比起手动PGA内存治理要好众多了 。 什么时候 使用自动PGA内存治理？什么时候 使用手动PGA内存治理？ 白天系统 畸形运行时 合适 使用自动PGA内存治理，让Oracle依据目前负载自动治理、 调配PGA内存 。 夜里消费者数少、进行 保护的时候 可以设定目前会话 使用手动PGA内存治理，让目前的 保护操作 获得尽可能多的内存，加速执行速度 。 如：服务器平时运行在自动PGA内存治理模式下，夜里有个 使命要大表进行排序衔接后更新，就 可以在该操作session中暂时更改为手动PGA内存治理， 而后 调配大的SORT_AREA_SIZE和HASH_AREA_SIZE(50%甚至80%内存，要确保无 其余消费者 使用)，这样能大大加速系统运行速度，又不影响白天巅峰期对系统造成的影响 。 六、操作命令 系统级更改： ALTER SYSTEM SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}； ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100000000; ALTER SYSTEM SET SORT_AREA_SIZE = 65536 SCOPE = SPFILE； ALTER SYSTEM SET HASH_AREA_SIZE = 65536 SCOPE = SPFILE； 会话级更改 ALTER SESSION SET WORKAREA_SIZE_POLICY = {AUTO | MANAUL}； ALTER SESSION SET SORT_AREA_SIZE = 65536； ALTER SESSION SET HASH_AREA_SIZE = 65536； 七、学 以致用 1，排序区： pga_aggregate_target为100MB，单个 查问能用到5%也便是5MB时排序所需 工夫 SQL> create table sorttable as select * from all_objects; 表已 缔造 。 SQL> insert into sorttable (select * from sorttable); 已 缔造49735行 。 SQL> insert into sorttable (select * from sorttable); 已 缔造99470行 。 SQL> set timing on; SQL> set autotrace traceonly; SQL> select * from sorttable order by object_id; 已 取舍198940行 。 已用 工夫: 00: 00: 50.49 Session级 批改排序区为30mb所需 工夫 SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL; 会话已更改 。 已用 工夫: 00: 00: 00.02 SQL> ALTER SESSION SET SORT_AREA_SIZE = 30000000; 会话已更改 。 已用 工夫: 00: 00: 00.01 SQL> select * from sorttable order by object_id; 已 取舍198940行 。 已用 工夫: 00: 00: 10.76 可以看到所需 工夫从50.49秒削减到10.31秒，速度 晋升很显而易见 。 2，散列区： pga_aggregate_target为100MB，单个 查问能用到5%也便是5MB时表衔接所需 工夫 SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id; 已 取舍49735行 。 已用 工夫: 00: 00: 40.50 Session级 批改散列区为30mb所需 工夫 SQL> ALTER SESSION SET WORKAREA_SIZE_POLICY = MANUAL; 会话已更改 。 已用 工夫: 00: 00: 00.01 SQL> ALTER SESSION SET HASH_AREA_SIZE = 30000000; 会话已更改 。 已用 工夫: 00: 00: 00.01 SQL> select /*+ use_hash(tb1 tb2)*/ * from sorttable tb1,sorttable tb2 where tb1.object_id=tb2.object_id; 已 取舍49735行 。 已用 工夫: 00: 00: 04.47 所需 工夫由40.50秒 晋升到4.47秒， 动机同样很显而易见 。 备注：以上 试验皆执行全表扫描 保障 有关表读入缓冲区中，幸免因数据没读入缓存造成误差 。 论断：在9iR2版以后，PGA不再像以往那样困扰DBA了，Oracle会帮我们做好PGA的 调配 。但这并不 象征着DBA不需求 深刻了解PGA了， 主宰PGA并依据适当 利用会让工作 锦上添花 。 |
