Re: v$pgastat and untunable memory

"anilech" <anilech_at_mail.ru> wrote in message news:1136901748.235255.162100_at_o13g2000cwo.googlegroups.com...
> Hello!
>
> I have
> PGA_AGGREGATE_TARGET=20G
> in my init.ora and select * from v$pgastat gives me
> aggregate PGA auto target about 2Gb during peak load hours.
> Metalink note 223730.1 says that this value gives me tunable part of
> PGA memory, so i assume that other 18Gb "are known as untunable, i.e.
> they require a size that can't be negociated
> (e.g. context information for each session, for each open/active
> cursor,
> PL/SQL or Java memory)"
>
> So my question is: how can I examine what consumes my pga memory in
> details? Context information, plsql memory, or something else?
>
> Thanks!
>

It's always worth mentioning your version, and for something like v$pgastat (which is only about 20 lines) giving the whole result rather than reading and quoting one line.

However, consider the following

     NAME                                          VALUE
    ---------------------------------------- ----------
    aggregate PGA target parameter            209715200
    aggregate PGA auto target                 175223808
    global memory bound                        10485760
    total PGA inuse                            15022080
    total PGA allocated                        18413568
    maximum PGA allocated                      19067904
    total freeable PGA memory                         0

The aggregate PGA auto target is (approximately) the memory currently available for new PGA demands, and you are likely to find that

    aggregate PGA auto target +
    total PGA allocated = (approx)

        aggregate PGA target parameter

If you then look at v$process, you find four related columns:

    PGA_USED_MEM
    PGA_ALLOC_MEM
    PGA_FREEABLE_MEM
    PGA_MAX_MEM

select sum(pga_alloc_mem), sum(pga_used_mem), sum(pga_freeable_mem) from V$process;

SUM(PGA_ALLOC_MEM) SUM(PGA_USED_MEM) SUM(PGA_FREEABLE_MEM)

------------------ ----------------- ---------------------
          18420880          15032560                     0

You can see the line up (again, approximately).

So you can always pin down big hitters to processes - thence to the sessions behind them. Sessions can take you to open cursors, open cursors can take you to work areas. This may be enough to get you started.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005