PGA not getting released even when nothing is running

[Database Administrator]

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

[Database Administrator]

On Fri, 4 Sep 2009 11:12:53 -0700 (PDT), Ind-dba
wrote:

>Any idea when Oracle shifts used space to freeable space? or when it
>actually releases.

Why should it release? To acquire the memory again after release? That
doesn't look very sensible to me.
Look: overallocating the server RAM and making sure it pages
constantly is your choice, not Oracle's!

--
--
Sybrand Bakker
Senior Oracle DBA

[Database Administrator]

On Sep 4, 11:30*pm, sybra...@hccnet.nl wrote:
> On Fri, 4 Sep 2009 11:12:53 -0700 (PDT), Ind-dba
>
> wrote:
> >Any idea when Oracle shifts used space to freeable space? or when it
> >actually releases.
>
> Why should it release? To acquire the memory again after release? That
> doesn't look very sensible to me.
> Look: overallocating the server RAM and making sure it pages
> constantly is your choice, not Oracle's!
>
> --
> --
> Sybrand Bakker
> Senior Oracle DBA

I understand your point. Let me ask the same question in a different
way.
Why the memory is not marked "freeable" when the session is sitting
idle? In such case there is no real "release" and "acquire" happening.


To your point "Look: overallocating the server RAM and making sure it
pages constantly is your choice, not Oracle's!"
1. How do you know we have overallocated. Does any datapoint suggests
that ?
2. How do you know that we are paging constantly? Any data points?

[Database Administrator]

On Sep 4, 2:12*pm, Ind-dba wrote:

snip

> 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

Try purchasing and reading Tom Kyte's book "Expert Oracle Database
Architecture" or pick it up from a library.

The first four chapters including chapter 4 Memory Structures should
give you the background you need.

Are you limiting the amount of PGA space and SGA space on your server
to reasonable limits? ( That's kind of what Mr. Bakker was hinting
that you probably were not doing ).

It is always helpful when you post a question here to give some
technical background like what version of oracle, what operating
system, what patchset level, how much memory available on the server,
etc.

[Database Administrator]

> The first four chapters including chapter 4 Memory Structures should
> give you the background you need.
>

Thanks John for your reply!

>>Are you limiting the amount of PGA space and SGA space on your server
to reasonable limits? ( That's kind of what Mr. Bakker was hinting
that you probably were not doing ).

The scope of this question is for PGA and we are using dedicated
server (not shared server mode). Yes we are limiting the PGA to 6G
using pga_aggregate_target (SGA size 32G Total server memory 64G).

My question is when does Oracle mark a piece of memory "freeable" in
PGA?
As I understand, when the execution of the sql is over, oracle should
mark the runtime area of PGA memory it used for that operation as
"freaable", which in this case I don't see.



>> It is always helpful when you post a question here to give some
technical background like what version of oracle, what operating
system, what patchset level, how much memory available on the server,
etc.

This is 10.2.0.4 database on Linux.

[Database Administrator]

On Fri, 04 Sep 2009 11:12:53 -0700, Ind-dba 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

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

[Database Administrator]

On Sep 4, 11:12*am, Ind-dba 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/2.../07/pga-leaks/

jg
--
@home.com is bogus.
http://arstechnica.com/open-source/n...n-possible.ars

[Database Administrator]

On Fri, 04 Sep 2009 20:30:06 +0200, sybrandb wrote:

> Why should it release?

Actually, it releases memory when the cursors that caused the allocation
are closed. MMON controls the memory consumption and is supposed to tell
processes to de-allocate some memory. Memory is always de-allocated when
the cursor that caused its allocation is closed.



--
http://mgogala.freehostia.com