Re: Freeable memory in PGA
Date: Sat, 21 Jan 2017 22:25:50 +0100
Message-ID: <CAJ2-Qb8UMbC-sj9igUD+-k23=syBavuw0n16Evp5T_GVmc33sw_at_mail.gmail.com>
Hi Stephan
got a chance to test this with heapdumps.
This is the result for one of the PQ:
PID SPID CATEGORY PNAME PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM ALLOCATED USED
---- ----- --------- ----- ------------ ------------- ---------------- ----------- ---------- ---------- 129 2348 Freeable P076 697989243 1291580278 556662784 1291580278 556662784 0 129 2348 Other P076 697989243 1291580278 556662784 1291580278 41848710 129 2348 PL/SQL P076 697989243 1291580278 556662784 1291580278 19048 224 129 2348 SQL P076 697989243 1291580278 5566627841291580278 693049736 692948248
So 1.2GB allocated memory (and not freed at OS level) and roughly 680MB used.
This is from heap dump:
type cnt Byt ---- ----- --- free 35 50088 freeable 2735 693240544 perm 11 302864 recreate 6 15264
If we breakdown:
type cnt Byt ---- ----- --- Free(heap.awk) 35 50088 kxsFrame4kPage 13 53976 kxs-heap-w 2389 692831224
Looks like most freeable is in kxs-heap-w
So I go back to my previous conclusion, I guess it is not freeing memory, it will do if there is PGA pressure I guess
Thanks
On Sun, Jan 15, 2017 at 1:25 PM, Stefan Koehler <contact_at_soocs.de> wrote:
> Hey Cheng,
> for sure there is something strange ;-)
>
> I just focus on PID 118 as an example for now. You have no break-down of
> the 1186 MB of free memory via x$ksmpgdst (gv$process_memory_detail). Have
> you cross-checked the PGA/UGA heap dump as requested? You should be able
> to find the freeable memory in the dump.
>
>
> Here is just an example from my Oracle 12.2 play-ground for demonstration
> purpose.
>
> SID SPID PID SERIAL# CATEGORY
> ALLOCATED USED MAX_ALLOCATED CON_ID
> ---------- ------------------------ ---------- ---------- ---------------
> ---------- ---------- ------------- ----------
> 215 49777 119 4 SQL
> 2000 32 790872 0
> 215 49777 119 4 PL/SQL
> 389560 383624 392824 0
> 215 49777 119 4 Freeable
> 786432 0 0
> 215 49777 119 4 Other
> 2237404 2237404 0
>
> SQL> oradebug setospid 49777
> SQL> oradebug dump heapdump 536870913
>
> shell> egrep -i "freeable|heap name=" /<path>/<trace_file_name>.trc
> HEAP DUMP heap name="pga heap" desc=0x7f510e7fe260
> Chunk 7f510e3d94a8 sz= 2520 freeable "koh-kghu call h"
> Chunk 7f510e3d9e80 sz= 8256 freeable "PLS PGA hp "
> ds=0x7f510e5233c0
> Chunk 7f510e3ddf88 sz= 8312 freeable "Alloc environm "
> ds=0x7f510e4ee9d0
> Chunk 7f510e390728 sz= 2136 freeable "PLS PGA hp "
> ds=0x7f510e5233c0
> Chunk 7f510e390f80 sz= 4184 freeable "Alloc environm "
> ds=0x7f510e4ee9d0
> Chunk 7f510e391fd8 sz= 8312 freeable "Alloc environm "
> ds=0x7f510e4ee9d0
> Chunk 7f510e394050 sz= 4184 freeable "Alloc environm "
> ds=0x7f510e4ee9d0
> Chunk 7f510e3950a8 sz= 15888 freeable "Alloc environm "
> ds=0x7f510e4ee9d0
> Chunk 7f510e35cae0 sz= 592 freeable "kopolal void "
> Chunk 7f510e35cd30 sz= 592 freeable "kopolal void "
> Chunk 7f510e35cf80 sz= 1072 freeable "kopolal void "
> Chunk 7f510e2eb080 sz= 459328 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e567270 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e5682c8 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e565180 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e5661d8 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e5624d8 sz= 11368 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e55f830 sz= 11368 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e55d740 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> Chunk 7f510e55e798 sz= 4184 freeable "diag pga "
> ds=0x7f510e7b97b0
> ...
>
> You also can drill-down further more into the sub-heaps as they are
> included in dump as well.
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Homepage: http://www.soocs.de
> Twitter: _at_OracleSK
>
> > Ls Cheng <exriscer_at_gmail.com> hat am 14. Januar 2017 um 21:31
> geschrieben:
> >
> > Hi Stefan
> >
> > I got a chance to test this.
> >
> > Following are the output for 2 PID (there are 32 processes but with
> these 2 we should have enough data). There are PID 118 and 148. As we can
> see
> > in v$process_memory_detail there is nothing strange. used mem for 118 is
> 20MB, allocated mem is 1200MB roughly and freeable 1180M, similar for PID
> > 148. But as my previous observation although the PGA is freeable at OS
> level the memory is not freed.
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jan 21 2017 - 22:25:50 CET