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@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9879835/viewspace-1050619/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9879835/viewspace-1050619/