Re: PGA not getting released even when nothing is running

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. Received on Fri Sep 04 2009 - 13:47:03 CDT