Re: Freeable memory in PGA

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        556662784

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-l