(oralce)pga_aggregate_target与workarea_size_policy相互关系验证

pga_aggregate_target与workarea_size_policy相互关系验证

 

先放上结论：

1. 当pga_aggregate_target设置为非0， 手工设置（workarea _size_policy设置为manual），这时使用的 manual管理模式，而不是auto管理模式

2. 当内存为自动管理时（2个条件同时满足，pga_aggregate_target设置为非0，workarea _size_policy设置为auto），sort_area_size不生效；当内存为手动管理时（1个条件，workarea _size_policy设置为manual，不管pga_aggregate_target是什么值），sort_area_size生效。

 

问题：

 

今日，从网上看到一篇总结pga方面参数配置的文章，其中提到<2>点，workarea _size_policy设置为manual，pga_aggregate_target设置为非0，会使用自动内存管理。

我对于其中的第二点持怀疑态度，遂做一个测试。

 

单个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，因此，使用自动管理。

-- 经过自己的测试，这个说法是不正确的，当pga_aggregate_target设置为非0， 手工设置（workarea _size_policy设置为manual），这时使用的 manual管理模式，而不是auto管理模式

（3）如果不设置pga_aggregate_targe，那么系统缺省也是使用自动管理，并且设置该参数的值为sga的20%和10m中较大的值。

总之：当内存为自动管理时（2个条件同时满足，pga_aggregate_target设置为非0，workarea _size_policy设置为auto），sort_area_size不生效；当内存为手动管理时（1个条件，workarea _size_policy设置为manual，不管pga_aggregate_target是什么值），sort_area_size生效。

 

测试：

 

主要通过看v$pgastat 的 如下4个指标来观察到底用了什么方式的pga管理，到底是 manual还是 auto

total pga used for auto workareas          
maximum pga used for auto workareas         
total pga used for manual workareas         
maximum pga used for manual workareas       

 

 

1.首先禁用amm

memory_max_target                    big integer 0
memory_target                        big integer 0

 

2. 系统目前状态， pga_aggregate_target=224m , workarea_size_policy= auto

3. 手动设置 workarea_size_policy= manual

注释： 在设置为manual之后，测试了一下重新将 pga_aggregate_target设置为另外一个值，这时并不会将workarea_size_policy自动设置为auto，看来官网上说的是从0设置为其他值时，才会将policy自动改为auto

4. 重启实例，可以看到这4个相关指标都是0

5.创建测试表

sql> create table x as select * from dba_objects;

table created.

sql> insert into x select * from x;

86672 rows created.

sql> insert into x select * from x;

173344 rows created.

sql> commit;

commit complete.

 

6. 执行一个较大的排序查询，select * from x order by object_id,last_ddl_time;

7.再次观察 v$pgastat , 

可以看出  manual的2个相关指标有变化了，而 auto都是0，这里说明了 真实采用的是 manual方式。

8.改回 auto再试试

9.执行sql前

total pga used for auto workareas                                         0 bytes
maximum pga used for auto workareas                                       0 bytes
total pga used for manual workareas                                   67584 bytes
maximum pga used for manual workareas                                274432 bytes

 

 

10.执行sql  select * from x order by object_id,last_ddl_time;

11. 再次观察，发现auto指标变化了， 而 manual毫无变化

total pga used for auto workareas                                         0 bytes
maximum pga used for auto workareas                                       0 bytes
total pga used for manual workareas                                   67584 bytes
maximum pga used for manual workareas                                274432 bytes

 

12， 经过几次反复的验证，证明了 pga是采用auto模式还是manual模式，是由参数  workarea_size_policy控制的，而跟 pga_aggrate_target没关系。

 

结论：

 

（2）workarea _size_policy设置为manual，pga_aggregate_target设置为非0，会使用自动内存管理。
    因为，如果设置pga_aggregate_targe为非0，workarea _size_policy自动设置为auto，因此，使用自动管理。

-- 经过自己的测试，这个说法是不正确的，当pga_aggregate_target设置为非0， 手工设置（workarea _size_policy设置为manual），这时使用的 manual管理模式，而不是auto管理模式

 

 

总之：当内存为自动管理时（2个条件同时满足，pga_aggregate_target设置为非0，workarea _size_policy设置为auto），sort_area_size不生效；

         当内存为手动管理时（1个条件，workarea _size_policy设置为manual，不管pga_aggregate_target是什么值），sort_area_size生效。