oracle workarea,关于Oracle数据库work area size的议论

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

单个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生效。

selectname,value, isdefault,description

fromv$parameter

wherenamein('workarea_size_policy','pga_aggregate_target')

ornamelike'sort%';

2、当PGA内存为手动管理时，*_area_size参数设置是有效的，sort_area_size参数详解：

sort_area_size：该参数的配置值为每个session的最大排序空间(单位：byte)。同时与该参数有关的另外一个参数是sort_area_retained_size。

select*fromv$parameterwherenamelike'%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_SIZEspecifies (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 theSORT_AREA_RETAINED_SIZEparameter. 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 bySORT_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 bySORT_AREA_SIZEfor 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*fromv$parameterwherenamelike'%pga_aggregate_target%';

但我们通过v$sesstat或者v$process，可以看到有的session或者进程使用的PGA内存量大于100M，这是为什么呢？是否是上面的说明准确呢。

selecta.sid, round(a.value/1024/1024,2)||'M'pga_area

fromv$sesstat a, v$statname b

wherea.statistic# = b.statistic#

andb.name ='session pga memory';

selectpid,

pga_used_mem /1024/1024,

pga_alloc_mem /1024/1024,

pga_freeable_mem /1024/1024,

pga_max_mem /1024/1024

fromv$process;

2)视图v$pgastat展示了数据库当前PGA总体的使用状况

select*fromv$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为PARALLEL4，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容量限制。