Re: v$pgastat and untunable memory

"anilech" <anilech_at_mail.ru> wrote in message news:1136976014.414525.231190_at_g43g2000cwa.googlegroups.com...
> Hello!
>
> Thanks for your reply, Jonathan.
>
> We are running 9.2.0.6 64bit on Aix 5.3.
>
> The whole v$pgastat view looks like this:
> aggregate PGA target parameter 21474836480
> aggregate PGA auto target 3616468992
> global memory bound 104857600
> total PGA inuse 17545417728
> total PGA allocated 20252230656
> maximum PGA allocated 22105217024
> total freeable PGA memory 2009137152
> PGA memory freed back to OS 712509751296
> total PGA used for auto workareas 88340480
> maximum PGA used for auto workareas 637140992
> total PGA used for manual workareas 0
> maximum PGA used for manual workareas 1073152
> over allocation count 0
> bytes processed 14204348607488
> extra bytes read/written 850115845120
> cache hit percentage 94.35
>
> I know about that columns in v$process, but how can I tell, for
> example, how much space from that process private memory has been used
> for holding local contexts?
>
> Bye.
>

Until 10.2 and the appearance of the view v$process_memory, you can't really get that detail (and the view doesn't really give you much more in 10.2)

For the occasional investigation, you can dump the associated heap:

    oradebug setorapid {process id}
    oradebug dump heapdump 1

This will give you a (possibly large) trace file starting with lines like:

HEAP DUMP heap name="pga heap" desc=00230380 extent sz=0x213c alt=84 het=32767 rec=0 flg=2 opc=2 parent=00000000 owner=00000000 nex=00000000 xsz=0x213c EXTENT 0 addr=04A0ABD8
Chunk 4a0abe0 sz= 276 free " "
Chunk 4a0acf4 sz= 8224 freeable "PLS non-lib hp " ds=049FD63C

-- 
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 10th Jan 2006