关于Oracle数据库work area size & pga_aggregate_target参数的讨论

关于Oracle数据库work area size & pga_aggregate_target参数的讨论

单个session的PGA内存使用量限制有比较多的概念，现在统一梳理一下：

 
1、关于PGA内存是自动管理还是手工管理，以及*_area_size（例如 sort_area_size）参数是否生效
workarea_size_policy:该参数定义数据库PGA内存是否自动管理，同时涉及到SQL运行中各种内存参数设置是否生效；当PGA内存为自动内存管理（workarea_size_policy =auto），有关SQL运行的各种内存参数设置（例如sort_area_size 排序内存大小）均不生效，但至于SQL运行的各种内存参数设置生否真的生效，还与pga_aggregate_target值有关，具体如下：
（1）workarea _size_policy设置为auto，pga_aggregate_target设置为0 ，不会使用自动内存管理，因为当pga_aggregate_target设置为0时，workarea _size_policy自动设置为manual。
（2）workarea _size_policy设置为manual，pga_aggregate_target设置为非0，会使用自动内存管理。因为，如果设置pga_aggregate_targe为非0，workarea _size_policy自动设置为auto，因此，使用自动管理。
（3）如果不设置pga_aggregate_targe，那么系统缺省也是使用自动管理，并且设置该参数的值为SGA的20%和10M中较大的值。
 
总之：当内存为自动管理时，sort_area_size不生效；当内存为手动管理时，sort_area_size生效。
 
select name, value, isdefault, description
  from v$parameter
 where name in ('workarea_size_policy', 'pga_aggregate_target')
 or  name like 'sort%';
 
2、当PGA内存为手动管理时，*_area_size参数设置是有效的，sort_area_size参数详解：
 
sort_area_size：该参数的配置值为每个session的最大排序空间(单位：byte）。同时与该参数有关的另外一个参数是sort_area_retained_size。
 
select * from v$parameter where name like '%area_size%';
 
sort_area_retained_size：该参数是控制一个session在排序结束后，该session用的内存排序区（最大为sort_area_size）降低到的空间值。例如：sort_area_size=100k, sort_area_retained_size=20k,当排序已经完成后，在记录被取出返回的过程中，oracle将逐步释放排序中使用的内存空间（例如使用了最大100k），直至释放到剩余20k即sort_area_retained_size空间大小时，该session的排序内存空间将不再继续释放，sort_area_retained_size的内存空间将在排序记录的最后一条记录取走返回后再全部释放。
 
sort_area_retained_size通常设置的值与sort_area_size同样大，默认设置sort_area_retained_size=0，即与sort_area_size同样大。
10G中文档解释:SORT_AREA_SIZE specifies (in bytes) the maximum amount of memory Oracle will use for a sort. After the sort is complete, but before the rows are returned, Oracle releases all of the memory allocated for the sort, except the amount specified by the SORT_AREA_RETAINED_SIZE parameter. After the last row is returned, Oracle releases the remainder of the memory.
Each sort in a query can consume memory up to the amount specified by SORT_AREA_SIZE, and there can be multiple sorts in a query. Also, if a query is executed in parallel, each PQ slave can consume memory up to the amount specified by SORT_AREA_SIZE for each sort it does.
 
3、当PGA内存为自动管理时，允许的最大排序内存空间是多少？
 
1）PGA_AGGREGATE_TARGET：该参数参数同时限制全局PGA分配和私有工作区内存分配，对于单个session可调整的内存部分（即work areas）的限制，具体如下：
a)对于串行操作，单个SQL操作能够使用的PGA内存work areas按照以下原则分配：
MIN（5% * PGA_AGGREGATE_TARGET，100MB）
b)对于并行操作，单个SQL操作能够使用的PGA内存work areas按照以下原则分配：
30%* PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）
 
select * from v$parameter where name like '%pga_aggregate_target%';
 
但我们通过v$sesstat或者v$process，可以看到有的session或者进程使用的PGA内存量大于100M，这是为什么呢？是否是上面的说明准确呢。
 
select a.sid, round(a.value/1024/1024,2)||'M' pga_area
  from v$sesstat a, v$statname b
 where a.statistic# = b.statistic#
   and b.name = 'session pga memory';
 
select pid,
       pga_used_mem / 1024 / 1024,
       pga_alloc_mem / 1024 / 1024,
       pga_freeable_mem / 1024 / 1024,
       pga_max_mem / 1024 / 1024
  from v$process;
 
2）视图v$pgastat展示了数据库当前PGA总体的使用状况
 
select * from v$pgastat；
total PGA allocated：该参数显示的值是目前数据库正在使用中的PGA内存量，这个值是有可能大于PGA_AGGREGATE_TARGET配置值的。
over allocation count：该参数记录的是从数据库启动以来，PGA使用总量大于PGA_AGGREGATE_TARGET配置值的累计次数。
 
从上面的两个参数来看，在全局的范围内，PGA分配给各个session的总PGA使用量的汇总在某些情况下，是允许超过PGA_AGGREGATE_TARGET配置值的。
 
total PGA allocated - Current amount of PGA memory allocated by the instance. Oracle attempts to keep this number below the value of the PGA_AGGREGATE_TARGET initialization parameter. However, it is possible for the PGA allocated to exceed that value by a small percentage and for a short period of time when the work area workload is increasing very rapidly or when PGA_AGGREGATE_TARGET is set to a small value. 


over allocation count - This statistic is cumulative since instance startup. Over allocating PGA memory can happen if the value of PGA_AGGREGATE_TARGET is too small. When this happens, Oracle cannot honor the value of PGA_AGGREGATE_TARGET and extra PGA memory needs to be allocated.
 
3）详细分析PGA的组成
首先需要说明，PGA分成两部分：
  Untunable Memory Size + Tunable Memory Size <= PGA_AGGREGATE_TARGET


这两部分各自是些什么成份﹕
a)不可调整部分(Untunable Memory Size)﹕PL/SQL arrays, stack space, session variables等等。如果设置的PGA（也就是PGA_AGGREGATE_TARGET的设置)小于该部分﹐那一定会超过PGA_AGGREGATE_TARGET的值﹐并且该部分是不可以shrink的，即data areas。
 
b)可调整部分(Tunable Memory Size)﹕sort area,hash area,bitmap create area, bitmap merge area 这部分也就oracle concept内所说的work areas。
 
单个session在排序或者hash 时，最大允许的work areaa 是隐含参数_pga_max_size配置值的一半（隐含参数_pga_max_size的默认值为200M） 和pga_aggregate_target参数配置值的5% 中的最小值。
 
对于并行操作，单个session操作能够使用的PGA内存work areas是pga_aggregate_target参数配置值的30%除以并发度。例如：如果SQL的hints为 PARALLEL 4，pga_aggregate_target为200M，那么单个session的最大PGA分配空间为15M （200M*30% / 4 = 15M ）。
 
The limits of sorting and hashing：
There are important limitations of pga_aggregate_target:   The total work area cannot exceed 200 megabytes of RAM because of the default setting for _pga_max_size.  No RAM sort may use more than 5% of pga_aggegate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins. The algorithm further reduces this to (200/2) for sorts so the actual limit for pure sorts will be 100 megabytes.
 
pga_aggegate_target *5%=_smm_max_size
pga_aggegate_target *30%=_smm_px_max_size


_smm_px_max_size – This parameter is used for Oracle parallel query, and defaults to 30% of the pga_aggregate_target setting, divided by degree of parallelism (as set by a PARALLEL hint, "alter table xxx parallel" command, or the parallel_automatic_tuning initialization parameter). For example, by default a DEGREE=4 parallel query would have a maximum sort area value of 15 megabytes per session with a 200 megabyte pga_aggregate_target setting. Remember, parallel full-table scans bypass the data buffers and store the incoming data rows in the PGA region and not inside the data buffers (as defined by the db_cache_size parameter).
 
综上所述：PGA内存为自动管理时，pga_aggregate_target参数配置值为整个数据库的PGA内存量，单个session分配的PGA容量是有限制的，包含两部分data areas和work areas，具体为：串行操作，单个session操作能够使用的PGA内存work areas为 MIN（5% * PGA_AGGREGATE_TARGET，100MB）；并行操作为30%*PGA_AGGREGATE_TARGET /DOP （DOP=Degree Of Parallelism 并行度）；如果单个session在PGA中data areas载入了大量的业务数据（全表扫描，并发查询），即data areas可以超过PGA容量限制。