【YashanDB知识库】swap空间使用超大报错

drop table tmp1;
create table tmp1(c1 int,c2 double,c3 varchar(500),c4 int);
 
begin
for i in 1 .. 4994196 loop
insert into tmp1 select random()*100000000,random()*5000000,'hello world test xxxxxiio','' from dual;
end loop;
commit;
end;
/
 
select c1,c2,LISTAGG(c3) value_list from tmp1 where c4 is null group by c1,c2 having to_char(LISTAGG(c3)) = '15395169080,15395169080';
-- 能出结果,(swap空间590M)
 
源表数据2百多M，这个group_concat sql使用1百多G swap空间还不够
select c1,c2,group_concat(c3) value_list from tmp1 where c4 is null group by c1,c2 having to_char(group_concat(c3)) = '15395169080,15395169080';
-- 出不来结果，swap空间越来越大，(直到128.063G，报错退出)
YAS-02025 no free space in virtual memory pool
 
select c1,c2,group_concat(c3) value_list from tmp1 where c4 is null group by c1,c2 ;
-- 能出结果,swap空间一直增大(直到33.188G，报错退出)
YAS-00103 no free block in application pool
 
select c1,c2,group_concat(c3) value_list from tmp1 group by c1,c2
-- 能出结果,swap空间一直增大(直到33.188G，报错退出)
YAS-00103 no free block in application pool
 
select c1,c2 from tmp1 group by c1,c2 ;
-- 能出结果，swap空间到一定大小停下(128M)4994196行
 
select group_concat(c3) value_list from tmp1 ;
-- 能出结果，swap空间一定大小停下(252M)
 
select * from (
select c1,c2,group_concat(c3) value_list from tmp1 where C4 is null group by c1,c2
) where to_char(value_list) = '15395169080,15395169080';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.

vmAllocAndOpen
doVmAlloc
vmAllocSwap
vmAllocExtend
spcAllocExtent
doAllocExt
spcAllocDataFileExt

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

spcAllocExtent
doExtendExt
spcExtendDatafile
dfExtend
dbExtendDevice
dbBuildDeviceBlocks
writeDevice
writeFile
write

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.