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