Re: PGA not getting released even when nothing is running
From: joel garry <joel-garry_at_home.com>
Date: Fri, 4 Sep 2009 16:01:51 -0700 (PDT)
Message-ID: <bb679f2b-9022-4abf-8e5b-b4c82062ab2f_at_h40g2000prf.googlegroups.com>
On Sep 4, 11:12 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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
Date: Fri, 4 Sep 2009 16:01:51 -0700 (PDT)
Message-ID: <bb679f2b-9022-4abf-8e5b-b4c82062ab2f_at_h40g2000prf.googlegroups.com>
On Sep 4, 11:12 am, Ind-dba <oraclear..._at_googlemail.com> wrote:
> 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
To really answer your question, you're going to have to go heapdump spelunking. Start here, then followup at Tanel Poder's site: http://jonathanlewis.wordpress.com/2009/06/07/pga-leaks/
jg
-- _at_home.com is bogus. http://arstechnica.com/open-source/news/2009/09/ec-fears-oracle-will-kill-mysql-but-is-it-even-possible.arsReceived on Fri Sep 04 2009 - 18:01:51 CDT