Christian Bilien’s Oracle performance and tuning blog

Where has all my memory gone ?

Advertisements

A while ago, I came across an interesting case of memory starvation on a Oracle DB server running Solaris 8 that was for once not directly related to the SGA or the PGA. The problem showed up from a user perspective as temporary “hangs” that only seemed to happen at a specific time of the day. This server is dedicated to a single 10gR2 Oracle instance. Looking at the OS figures, the first things that I saw were some vmstat signs of memory pressure:

A high number of page reclaims, 200 to 500 Mb of free memory left out of 16GB and 2000 to 3000 pages/s scanned by the page scanner. Look at how memory is allocated using prtmem:

Total memory:           15614 Megabytes
Kernel Memory:           1534 Megabytes
Application:            12888 Megabytes
Executable & libs:        110 Megabytes
File Cache:               410 Megabytes
Free, file cache:         250 Megabytes
Free, free:               430 Megabytes

But look at the Oracle SGA and PGA:

SGA:

Total System Global Area 6442450944 bytes
Fixed Size                  2038520 bytes
Variable Size            3489662216 bytes
Database Buffers         2936012800 bytes
Redo Buffers               14737408 bytes

PGA:

  select * from v$pgastat
 NAME                                                                  VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter                                   1048576000 bytes
aggregate PGA auto target                                          65536000 bytes
global memory bound                                                 2258944 bytes
total PGA inuse                                                  1181100032 bytes
total PGA allocated                                              2555433984 bytes
maximum PGA allocated                                            2838683648 bytes
total freeable PGA memory                                         755367936 bytes
process count                                                          1943
max processes count                                                    2273
PGA memory freed back to OS                                      2.5918E+11 bytes
total PGA used for auto workareas                                   9071616 bytes

Well, that’s at this point 7.5GB (again out of 16GB) for the PGA currently in use + the SGA allocation. I assumed here that because of the memory pressure, the unused part of the PGA was already paged out. Prtmem showed an “application” memory size of 12.9GB.

Where are the 5.4 GB gone ?

I looked at the structure of one of the processes using pmap –x:

pmap -x 14816
         Address   Kbytes Resident Shared Private Permissions       Mapped File
0000000100000000  100536   44600   44600       - read/exec         oracle
000000010632C000     816     560     368     192 read/write/exec   oracle
00000001063F8000     912     904       -     904 read/write/exec     [ heap ]
0000000380000000   16384   16384   16384       - read/write/exec/shared  [ ism shmid=0x5004 ]
00000003C0000000 3145728 3145728 3145728       - read/write/exec/shared  [ ism shmid=0x2005 ]
0000000480000000 3129360 3129360 3129360       - read/write/exec/shared  [ ism shmid=0x11007 ]
FFFFFFFF7B270000     128      48       -      48 read/write          [ anon ]
FFFFFFFF7B300000      64      64       -      64 read/write          [ anon ]
FFFFFFFF7B310000     448     328       -     328 read/write          [ anon ]
FFFFFFFF7B400000       8       8       8       - read/write/exec/shared   [ anon ]
FFFFFFFF7B500000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7B600000      16      16      16       - read/exec         libmp.so.2
FFFFFFFF7B704000       8       8       -       8 read/write/exec   libmp.so.2
FFFFFFFF7B800000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7B900000     216     216     216       - read/exec         libm.so.1
FFFFFFFF7BA34000      16      16       -      16 read/write/exec   libm.so.1
FFFFFFFF7BB00000      24      24      24       - read/exec         librt.so.1
FFFFFFFF7BC06000       8       8       -       8 read/write/exec   librt.so.1
FFFFFFFF7BD00000      32      32      32       - read/exec         libaio.so.1
FFFFFFFF7BE08000       8       8       -       8 read/write/exec   libaio.so.1
FFFFFFFF7BF00000     728     728     728       - read/exec         libc.so.1
FFFFFFFF7C0B6000      56      56       -      56 read/write/exec   libc.so.1
FFFFFFFF7C0C4000       8       8       -       8 read/write/exec   libc.so.1
FFFFFFFF7C100000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7C200000       8       8       8       - read/exec         libsched.so.1
FFFFFFFF7C302000       8       8       -       8 read/write/exec   libsched.so.1
FFFFFFFF7C400000       8       8       -       8 read/write/exec   libdl.so.1
FFFFFFFF7C500000      32      24      24       - read/exec         libgen.so.1
FFFFFFFF7C608000       8       8       -       8 read/write/exec   libgen.so.1
FFFFFFFF7C700000      56      56      56       - read/exec         libsocket.so.1
FFFFFFFF7C80E000      16      16       -      16 read/write/exec   libsocket.so.1
FFFFFFFF7C900000     672     672     672       - read/exec         libnsl.so.1
FFFFFFFF7CAA8000      64      64       -      64 read/write/exec   libnsl.so.1
FFFFFFFF7CAB8000      32      32       -      32 read/write/exec   libnsl.so.1
FFFFFFFF7CB00000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7CC00000       8       8       8       - read/exec         libkstat.so.1
FFFFFFFF7CD02000       8       8       -       8 read/write/exec   libkstat.so.1
FFFFFFFF7CE00000    2176     376     376       - read/exec         libnnz10.so
FFFFFFFF7D11E000     240     232      16     216 read/write/exec   libnnz10.so
FFFFFFFF7D15A000       8       -       -       - read/write/exec   libnnz10.so
FFFFFFFF7D200000      72      72      72       - read/exec         libdbcfg10.so
FFFFFFFF7D310000       8       8       -       8 read/write/exec   libdbcfg10.so
FFFFFFFF7D400000    1056     112     112       - read/exec         libclsra10.so
FFFFFFFF7D606000      48      32       -      32 read/write/exec   libclsra10.so
FFFFFFFF7D612000       8       -       -       - read/write/exec   libclsra10.so
FFFFFFFF7D700000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7D800000    9256    3272    3272       - read/exec         libjox10.so
FFFFFFFF7E208000     560     472       8     464 read/write/exec   libjox10.so
FFFFFFFF7E300000    1056     208     208       - read/exec         libocrutl10.so
FFFFFFFF7E506000      56      48      16      32 read/write/exec   libocrutl10.so
FFFFFFFF7E514000       8       -       -       - read/write/exec   libocrutl10.so
FFFFFFFF7E600000    1256     136     136       - read/exec         libocrb10.so
FFFFFFFF7E838000      64      56       -      56 read/write/exec   libocrb10.so
FFFFFFFF7E848000       8       -       -       - read/write/exec   libocrb10.so
FFFFFFFF7E900000    1368     536     536       - read/exec         libocr10.so
FFFFFFFF7EB54000      72      56       -      56 read/write/exec   libocr10.so
FFFFFFFF7EC00000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7ED00000       8       8       8       - read/exec         libskgxn2.so
FFFFFFFF7EE00000       8       8       -       8 read/write/exec   libskgxn2.so
FFFFFFFF7EF00000    1736    1088    1088       - read/exec         libhasgen10.so
FFFFFFFF7F1B0000      72      64       -      64 read/write/exec   libhasgen10.so
FFFFFFFF7F1C2000       8       8       -       8 read/write/exec   libhasgen10.so
FFFFFFFF7F200000     128     128     128       - read/exec         libskgxp10.so
FFFFFFFF7F31E000      16      16       -      16 read/write/exec   libskgxp10.so
FFFFFFFF7F400000       8       8       8       - read/exec         libc_psr.so.1
FFFFFFFF7F500000       8       8       -       8 read/write/exec     [ anon ]
FFFFFFFF7F600000     176     176     176       - read/exec         ld.so.1
FFFFFFFF7F72C000      16      16       -      16 read/write/exec   ld.so.1
FFFFFFFF7FFE0000     128     128       -     128 read/write          [ stack ]
----------------  ------  ------  ------  ------
        total Kb 6416104 6347336 6344392    2944

Look at the resident size of the private section of the segments: the total private size is about 2.9MB. The largest private chunk is the heap, but it is only 1/3rd of the total private space. The remaining part of the private area resident in physical memory is made of anon segments and of private data sections.

_use_real_free_heap=true (the default in Oracle 10), meaning different heaps are used for the process portion of the PGA plus the CGA (call global area) and the UGA and possibly of other components. _use_ism_for_pga is also set to its default value (false), meaning the PGA is indeed part of the heap, not allocated from an ISM segment.

This is where it gets interesting: the number of oracle user processes at a given point at this time of the day is around 2000. I plotted for 100 randomly selected oracle processes the private size occupied by each of the processes: they all had a private memory of 3MB +/- 10%, and it was unlikely that any of the processes would significantly allocate more memory than the others. 3MB of private memory/process x 2000= 6GB: that’s about 4.9GB of “non PGA” private space (PGA in use=1.1GB).This example highlights the fact that UGA, CGAs and other portions of private memory, although seldom accounted for sizing the memory are not always negligible when the data base hosts many connexions.

Advertisements

Advertisements