--实验步骤【经常看才知道 这些数据的含义】
--运行下面的程序很多次
select * from tbak where rownum <&dd order by 1 desc;
select * from tbak where rownum <&dd order by 1 ;
select * from tbak where rownum <&dd order by 1 desc;
....
select * from tbak where rownum <&dd order by 1 desc;
select * from tbak where rownum <&dd order by 1 ;
select * from tbak where rownum <&dd order by 1 desc;
--回话关键信息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'
--这个数据根本不能控制进程使用的内存