Oracle内存结构管理全解析

### Oracle内存结构管理全解析 #### 1. PGA内存管理分析 在数据库运行中，用户数量与PGA（Program Global Area）内存使用密切相关。以下是不同用户数量下的PGA使用情况示例： | USERS | MY_PGA | TOTAL_PGA | | ---- | ---- | ---- | | 1 | 7 | 7 | | 26 | 7 | 182 | | 51 | 7 | 357 | | 76 | 7 | 532 | | 101 | 7 | 707 | | 126 | 7 | 882 | | 151 | 7 | 1057 | | 176 | 7 | 1232 | | 201 | 7 | 1407 | | 226 | 7 | 1582 | | 251 | 7 | 1757 | 需要注意的是，此查询使用了递归子查询分解技术，仅适用于Oracle 11g Release 2及更高版本。若服务器有2GB物理内存，SGA为256MB，当用户数达到250时，机器可能会开始进行页面交换，难以继续运行；500用户时，可能会分配约3514MB的RAM。所以，DBA通常不会将SORT_AREA_SIZE设置为5MB，而是约0.5MB，以控制高峰时的PGA使用量。 手动内存管理在工作量随时间增减时，对内存的使用可预测但并非最优。自动PGA内存管理则允许少量用户在内存可用时尽可能多地使用，随着负载增加减少分配，负载减少时增加单个操作的内存分配。 ##### 1.1 使用PGA_AGGREGATE_TARGET控制内存分配 理论上，可使用PGA_AGGREGATE_TARGET控制实例使用的PGA内存总量，但这并非严格限制。实例会尽量保持在该目标范围内，若无法做到也不会停止处理，而是会超出阈值。此外，工作区虽对PGA内存贡献较大，但并非唯一因素，许多因素都会影响PGA内存分配，且只有工作区受数据库实例控制。 以下是一个示例，展示了如何创建一个可在服务器中保存持久数据的包： ```sql EODA@ORA12CR1> create or replace package demo_pkg 2 as 3 type array is table of char(2000) index by binary_integer; 4 g_data array; 5 end; 6 / ``` 接着测量当前会话在PGA/UGA中使用的内存： ```sql EODA@ORA12CR1> select a.name, to_char(b.value, '999,999,999') bytes, 2 to_char(round(b.value/1024/1024,1), '99,999.9' ) mbytes 3 from v$statname a, v$mystat b 4 where a.statistic# = b.statistic# 5 and a.name like '%ga memory%'; ``` 初始时会话使用约2.1MB的PGA内存。之后使用相同的256MB PGA_AGGREGATE_TARGET再次对表T进行查询： ```sql EODA@ORA12CR1> set autotrace traceonly statistics; EODA@ORA12CR1> select * from t order by 1,2,3,4; ``` 查询结果显示排序完全在内存中完成，再次查看会话的PGA/UGA使用情况，发现使用了11.5MB的RAM。 然后填充包中的CHAR数组： ```sql EODA@ORA12CR1> begin 2 for i in 1 .. 200000 3 loop 4 demo_pkg.g_data(i) := 'x'; 5 end loop; 6 end; 7 / ``` 再次测量会话的PGA利用率，发现已超出整个实例设置的PGA_AGGREGATE_TARGET，数据库对此无能为力。若再次运行相同的排序查询，会发现排序到了磁盘，因为实例已无法提供所需的12MB内存。 #### 2. 手动与自动内存管理的选择 在手动和自动内存管理方法中，默认建议使用自动PGA内存管理。但在对生产系统进行任何更改之前，务必先测试是否有副作用，因为查询计划可能改变，性能也可能受影响，可能出现以下三种情况： - 运行情况与之前完全相同。 - 运行情况比之前更好。 - 运行情况比之前更差。 DBA在设置单个参数（如SORT|HASH_AREA_SIZE等）时常常感到困惑，默认值通常较小，可能严重影响系统性能，且合适的值会随时间和用户数量变化。而设置WORKAREA_SIZE_POLICY = AUTO和相应的PGA_AGGREGATE_TARGET则更方便，DBA只需指定一个值，Oracle会根据情况在活动会话中分配内存。 此外，从Oracle 9i Release 2及更高版本开始，有PGA建议功能，可帮助确定最优的PGA_AGGREGATE_TARGET，以减少临时表空间的物理I/O。 不过，在某些情况下可能不适合使用自动内存管理。自动PGA内存管理旨在实现多用户公平，但当需要独占所有可用内存时，可使用ALTER SESSION命令在会话中禁用自动内存管理，并手动设置SORT|HASH_AREA_SIZE。例如，凌晨2点的大型批处理作业，可使用所有可用资源。 ####