Re: PGA not getting released even when nothing is running

> DBRO_USER: j2pm41p3> select * from
> 2 (
> 3 select p.inst_id, s.sid, s.sql_hash_value sql_hash,
> s.username, round(p.pga_used_mem/1048576) pga_used_mb, round
> (PGA_FREEABLE_MEM/1048576) PGA_FREEABLE_MEM
> 4 from gv$process p, gv$session s 5 where
> 6 p.inst_id=s.inst_id and 7 p.addr=s.paddr and
> 8 p.inst_id=&v_inst_id and 9 s.sql_hash_value
> not in (&v_sql_filter)
> 10 order by pga_used_mb desc
> 11 )
> 12 where rownum <=&v_top_n
> 13 /
> old 8: p.inst_id=&v_inst_id and new 8:
> p.inst_id=3 and
> old 9: s.sql_hash_value not in (&v_sql_filter) new 9:
> s.sql_hash_value not in (-3) old 12: where rownum <=&v_top_n
> new 12: where rownum <=20
>
> INST_ID SID SQL_HASH USERNAME
> PGA_USED_MB PGA_FREEABLE_MEM
> ---------- ---------- ---------- ------------------------------
> ----------- ----------------
> 3 201 0 MFG_USER
> 257 27
> 3 198 0 MFG_USER
> 229 1
> 3 991 0 MFG_USER
> 211 1
> 3 423 0 MFG_USER
> 201 1
> 3 611 0 MFG_USER
> 180 0
> 3 246 0 MFG_USER
> 173 1
> 3 840 0 MFG_USER
> 173 1
> 3 675 0 MFG_USER
> 168 1
> 3 215 5468642 MFG_USER
> 165 0
> 3 1043 2003966279 MFG_USER
> 157 0
> 3 929 0 MFG_USER
> 156 1
> 3 121 0 MFG_USER
> 155 1
> 3 509 0 MFG_USER
> 149 1
> 3 228 0 MFG_USER
> 141 0
> 3 120 0 MFG_USER
> 141 1
> 3 737 0 MFG_USER
> 140 0
> 3 849 0 MFG_USER
> 139 1
> 3 310 0 MFG_USER
> 132 1
> 3 71 0 MFG_USER
> 129 0
> 3 353 0 MFG_USER
> 128 1
>
> 20 rows selected.
>
>
> There are so many sessions that are running nothing but yet consuming,
> so much of PGA.
>
> Any idea when Oracle shifts used space to freeable space? or when it
> actually releases.
>
> Regards,
> Sachin

What is the platform and version? There are some bugs about freeing the memory. Are you closing your cursors or keeping them open for posterity?

-- 
http://mgogala.freehostia.com