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: @OracleSK
Ls Cheng <exriscer@xxxxxxxxx> 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.
