另外一个疑问是关于pga_aggregate_target的
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 6G
我开启5个session同时执行
alter session set workarea_size_policy=manual;
alter session set sort_area_size=2000000000;
select * from t order by 1,2,3;
检测结果
WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED MAX_MEM_USED NUMBER_PASSES TEMPSEG_SIZE
-------------- ------------- --------------- ------------ ------------- ------------
0 1186213888 1800770560 0 1778384896
0 1800770560 1800770560 0 1483735040
0 1186213888 1800770560 0 1778384896
0 1800770560 1800770560 0 1398800384
0 1800770560 1800770560 0 1306525696
select * from v$pgastat
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
PGA memory freed back to OS 4.3649E+12 bytes
aggregate PGA auto target 402653184 bytes
aggregate PGA target parameter 6442450944 bytes
bytes processed 1.1573E+13 bytes
cache hit percentage 76.41 percent
extra bytes read/written 3.5720E+12 bytes
global memory bound 402653184 bytes
max processes count 378
maximum PGA allocated 1.3249E+10 bytes
maximum PGA used for auto workareas 1.0278E+10 bytes
maximum PGA used for manual workareas 9003852800 bytes
over allocation count 21
process count 213
recompute count (total) 6317725
total PGA allocated 1.0869E+10 bytes
total PGA inuse 1.0542E+10 bytes
total PGA used for auto workareas 380984320 bytes
total PGA used for manual workareas 9003852800 bytes
total freeable PGA memory 76808192 bytes
1.0869E+10 = 10.122G
pga_aggregate_target划定的值可以很容易被突破,那么设定它还有什么意义,譬如直接调大为10G,岂不是更省心?