PGA not getting released even when nothing is running

From: Ind-dba <oraclearora_at_googlemail.com>
Date: Fri, 4 Sep 2009 11:12:53 -0700 (PDT)
Message-ID: <f69b52f0-45b3-4965-8cea-190c3133dd11_at_l35g2000pra.googlegroups.com>

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 Received on Fri Sep 04 2009 - 13:12:53 CDT