dbms_session.free_unused_user_memory; 可以釋放 部分的PGA記憶體

--實驗步驟【經常看才知道 這些資料的含義】
--執行下面的程式很多次
select * from tbak where rownum select * from tbak where rownum select * from tbak where rownum ....
select * from tbak where rownum select * from tbak where rownum select * from tbak where rownum --回話關鍵資訊PGA
select *
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.sid = 136
order by 3 desc;
120122964
120122964
119562592
119497128
--pga相關資訊
select * from v$pgastat
160690176
209377280
209377280
--process
select sum(pga_used_mem)/1024/1024,sum(pga_alloc_mem)/1024/1024 from v$process
153 199
--原來的回話執行
begin
dbms_session.free_unused_user_memory;
end;
--=========================================================================
--回話關鍵資訊PGA
select *
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.sid = 136
order by 3 desc;
120122964
119562592
43839060
43493424
--pga相關資訊
select * from v$pgastat
85087232
133814272
209377280
--process
select sum(pga_used_mem)/1024/1024,sum(pga_alloc_mem)/1024/1024 from v$process
80 127
--===========dbms_session.free_unused_user_memory; 證明是可以釋放 部分的PGA記憶體

======================

select sum(work_area_size)/1024/1024 from v$sql_workarea_active

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV('Instance')
AND y.inst_id = USERENV('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_smm_max_size' --v$sql_workarea_active workarea_size –增大後也不一定是

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
FROM SYS.x$ksppi x, SYS.x$ksppcv y
WHERE x.inst_id = USERENV('Instance')
AND y.inst_id = USERENV('Instance')
AND x.indx = y.indx
AND x.ksppinm = '_pga_max_size'
--這個資料根本不能控制程式使用的記憶體

[@more@]