Friday, April 12, 2013

Shared Pool - Free Memory, Fragmentation ?

Facing:
    ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","SELECT MAX(XX) FROM...","SQLA","tmp")

under shared pool memory distribution of:

Top 5 Areas Name Subpool_1 Subpool_2 Subpool_3 Subpool_4 Subpool_5 Subpool_6 Subpool_7 Sum
KKSSP 617'693 563'340 590'296 590'798 577'532 643'504 577'237 4'160'404
db_block_hash_buckets 529'039 535'429 529'039 539'525 535'429 529'044 535'429 3'732'935
KGLH0 554'532 485'312 464'006 353'634 450'528 346'045 332'455 2'986'514
SQLA 1'110 588'943 565'317 185'664 574'008 155'498 272'452 2'342'994
free memory 306'562 358'029 353'146 306'659 342'194 325'386 304'535 2'296'513

(see Blog: Shared Pool - Memory Allocation - unbalanced)

an intuitively skeptical question is:
               Why do I get ORA-04031 even though there is plenty of free memory (> 10%) ?
One unsubstantial reply is mostly: memory fragmentation (or memory leak).

This Blog is trying to disperse such fashionable pretext.

A simple way to dispute it is to make a heapdump and you will see FREE LISTS starting with size=32 in Bucket 0:
   FREE LISTS:
   Bucket 0 size=32

so the minimum memory chunk size in shared pool is 32 Byte, any free memory chunk is at least 32 Byte.

But above error says:
   unable to allocate 32 bytes
evidently there is truly no more free memory.

So where is the secrecy behind the controversial information ?

To understand it, firstly we will look at what free memory is in different aspect, then expose the memory allocation of certain popular areas (subheaps), and finally debate further on this theme.

1. Free Memory


Take a DB of shared_pool_size = 1408M with two Subpools, one direct way to get free memory statistics is:
   select round(bytes/1024/1024) mb from v$sgastat where pool='shared pool' and name = 'free memory';
returns:
 298
 
or on its internal x$ table:
   select KSMDSIDX, round(ksmsslen/1024/1024) SGASTAT_MB from x$ksmss where ksmssnam ='free memory';

returns:

  KSMDSIDX   SGASTAT_MB
  ---------  ----------
  0          208
  1          42
  2          48
which lists free memory per subpool, where ksmdsidx 0 denotes the "RESERVED EXTENTS" in "sga heap" top. Anyway, the total free memory matches 208 + 42 + 48 = 298.

A heapdump shows:

 HEAP DUMP heap name="sga heap"  desc=700000000000198
    reserved granule count 13 (granule size 16777216)
  RESERVED EXTENTS
 HEAP DUMP heap name="sga heap(1,0)"  desc=700000000052a48
  FREE LISTS
   Total free space   =  9970088
  RESERVED FREE LISTS
   Total reserved free space   = 29473232
  
 HEAP DUMP heap name="sga heap(2,0)"  desc=70000000005c310
  FREE LISTS
   Total free space   = 12790056
  RESERVED FREE LISTS
   Total reserved free space   = 31900768

ksmdsidx 0 is correlated well with heapdump, but not free memory in both subpools.

  KSMDSIDX  SGASTAT_MB  HEAP_DUMP                                   Delta
  --------  ----------  ------------------------------------------  ----- 
  0         208         round(16777216*13/1024/1024)         = 208      0
  1         42          round((9970088+29473232)/1024/1024)  =  38      4
  2         48          round((12790056+31900768)/1024/1024) =  43      5
(In heapdump, summing all chunks commented with "R-free" and "free" gives the same result)

So now the question is why v$sgastat (and respective x$ksmss) reports 9 MB (4+5) free memory than heapdump ?

Let's try to dig it out.

2. Subheaps: KGLH0, KGLS, PLDIA, SQLA, SQLK, SQLP


These subheaps are chosen since they are mostly allocated in a chunk size of 4096, and all together play a prevalent role in memory consumption (On the top of Page 157 of Book: Expert Oracle Database Architecture, there is one line:
  The shared pool is characterized by lots of small (generally 4KB or less) chunks of memory).

At first select them from v$sgastat:

 select name, round(bytes/1024/1024) mb
   from v$sgastat
  where pool='shared pool'
    and name in ('KGLH0', 'KGLS', 'PLDIA', 'SQLA', 'SQLK', 'SQLP')
 order by mb desc;

 NAME   MB
 -----------
 SQLA   219
 KGLH0  125
 KGLS   43
 PLDIA  9
 SQLP   5
 SQLK   0

another query on x$ksmsp displays more details about memory usage:

 select substr(ksmchcom, 1, 5)          name
       ,round(sum(ksmchsiz)/1024/1024) mb
       ,count(ksmchsiz)                cnt
       ,round(avg(ksmchsiz))           avg
       ,min(ksmchsiz)                  min
       ,max(ksmchsiz)                  max
   from x$ksmsp v
 where substr(ksmchcom, 1, 5) in ('KGLH0', 'KGLS^', 'PLDIA', 'SQLA^', 'SQLK^', 'SQLP^')
 group by substr(ksmchcom, 1, 5)
 order by mb desc;

 NAME   MB   CNT   AVG  MIN  MAX 
 ---------------------------------
 SQLA^  220 56269 4099 4096 28664
 KGLH0  126 32261 4096 4096 4640
 KGLS^  43  11005 4096 4096 4128
 PLDIA  9   2195  4096 4096 4112
 SQLP^  5   1209  4168 4096 5888
 SQLK^  0   19    4096 4096 4096

Look again on the same heapdump, extract all lines containing one special comment, say,

KGLH0^d020e92f
  Chunk  7000000a1cd3578 sz=     4096    recreate  "KGLH0^d020e92f "  latch=0
  Chunk  7000000a1d9c4d8 sz=     4096    recreate  "KGLH0^d020e92f "  latch=0
  Chunk  7000000a1efce68 sz=     4096    freeable  "KGLH0^d020e92f "  ds=7000000a1d9c450
  Chunk  7000000a7b8f880 sz=     4096    freeable  "KGLH0^d020e92f "  ds=7000000a1d9c450
  Chunk  7000000a7f07588 sz=     4096    freeable  "KGLH0^d020e92f "  ds=7000000a1d9c450
 
(see MOS: Troubleshooting and Diagnosing ORA-4031 Error [Video] (Doc ID 396940.1) about Chunk types
Chunk types:

Normal (freeable) chunks - These chunks are allocated in such a way that the user can explicitly free the chunk once they have finished with the memory.

Free chunks - These chunks are free and available for reuse should a request come into the pool for this chunk size or smaller.

Recreatable chunks - This is a special form of "freeable" memory. These chunks are placed on an LRU list when they are unpinned. If memory is needed, we go to the LRU list and free "recreatable" memory that hasn't been used for a while.

Permanent chunks - These chunks can be allocated in different ways. Some chunks are allocated and will remain in use for the "life" of the instance. Some "permanent" chunks are allocated but can be used over and over again internally as they are available.)

and then make an addr dump of KGLH0^d020e92f:  

 Processing Oradebug command 'dump heapdump_addr 1 0X7000000A1D9C450'
 HEAP DUMP heap name="KGLH0^d020e92f"  desc=7000000a1d9c450
 EXTENT 0 addr=7000000a1efce80
   Chunk  7000000a1efce90 sz=     1384    perm      "perm           "  alo=600
   Chunk  7000000a1efd3f8 sz=      152    freeable  "kgltbtab       "
   Chunk  7000000a1efd490 sz=      696    freeable  "policy chain   "
   Chunk  7000000a1efd748 sz=      144    freeable  "context chain  "
   ...
 EXTENT 1 addr=7000000a7f075a0
   Chunk  7000000a7f075b0 sz=      376    free      "               "
   ...
 EXTENT 2 addr=7000000a7b8f898
   Chunk  7000000a7b8f8a8 sz=     1760    perm      "perm           "  alo=1760
   Chunk  7000000a7b8ff88 sz=      416    free      "               "
   ...
 EXTENT 3 addr=7000000a1cd35a8
   Chunk  7000000a1cd35b8 sz=       80    perm      "perm           "  alo=80
   Chunk  7000000a1cd3608 sz=     3296    perm      "perm           "  alo=3296
   Chunk  7000000a1cd42e8 sz=       48    free      "               "
   ...
 Total heap size    =    16200
 FREE LISTS:
  Bucket 0 size=0
   Chunk  7000000a7b8ff88 sz=      416    free      "               "
   Chunk  7000000a7f075b0 sz=      376    free      "               "
   Chunk  7000000a1cd42e8 sz=       48    free      "               "
   Chunk  7000000a1cd35d8 sz=        0    kghdsx
 Total free space   =      840
 UNPINNED RECREATABLE CHUNKS (lru first):
 PERMANENT CHUNKS:
   Chunk  7000000a1efce90 sz=     1384    perm      "perm           "  alo=600
   ...
 Permanent space    =     6520

so total used memory is 16200 (including 840 free space, and 6520 Permanent space), however the allocated space is 5*4096 = 20480, so 20480 - 16200 = 4280 over allocation.

addr dump of "KGLH0^d020e92f" shows 3 free Chunks with size 416, 376, and 48.

 FREE LISTS:
  Bucket 0 size=0
   Chunk  7000000a7b8ff88 sz=      416    free      "               "
   Chunk  7000000a7f075b0 sz=      376    free      "               "
   Chunk  7000000a1cd42e8 sz=       48    free      "               "
   Chunk  7000000a1cd35d8 sz=        0    kghdsx
 Total free space   =      840
Make a cross-check with the same heapdump (as follows), we see no free Chunks in Bucket 43 with size=376, and Bucket 48 with size=416:

HEAP DUMP heap name="sga heap(1,0)"  desc=700000000052a48
 FREE LISTS:
  Bucket 42 size=368
  Bucket 43 size=376
  Bucket 44 size=384
  Bucket 45 size=392
  Bucket 46 size=400
  Bucket 47 size=408
  Bucket 48 size=416
  Bucket 49 size=424
HEAP DUMP heap name="sga heap(2,0)"  desc=70000000005c310
 FREE LISTS:
  Bucket 42 size=368
  Bucket 43 size=376
  Bucket 44 size=384
  Bucket 45 size=392
  Bucket 46 size=400
  Bucket 47 size=408
   Chunk  7000000a3e8c3d8 sz=      408    free      "               "
  Bucket 48 size=416
  Bucket 49 size=424

 
so the FREE LISTS in addr dump of "KGLH0^d020e92f" are counted in v$sgastat, but not counted in free memory of heapdump, that probably explains why v$sgastat displays more free memory than heapdump. Since they are not listed in any FREE LISTS, they can not be allocated to any later memory demand till their bound parental chunks returned to LRU LIST (UNPINNED RECREATABLE CHUNKS). I guess that Oracle takes such an approach to reduce memory fragmentation (and coalescing) in hoping that each allocated chunk is fully used in these subheaps. In summary, free memory in v$sgastat probably displays more than allocable free memory since it includes un-allocable free memory inside the allocated chunks(chunk-intra free memory).

3. Discussion


3.1. session_cached_cursors: KGLH0 and SQLA


Each Oracle object, whatever SQL cursor(both parent and child), Table, PL/SQL, has one KGLH0 memory allocation of multiple 4K. When session_cached_cursors is set, repeated parse calls (more than 3 times) of the same SQL (including recursive SQL) or PL/SQL statement by any session connected to DB are candidate for addition to session cursor cache (Understanding Shared Pool Memory Structures ).

Cursor in this cache is "partially pinned", that means, KGLH0(heap 0) is pinned, but not SQLA (heap 6). So when session_cached_cursors is set high, there will be potentially huge KGLH0 pinned, but not SQLA.

As a certain rule of thumb, SQLA should be proportional to KGLH0, otherwise they are out of originally designed range.

In fact, the DB which throws ORA-04031 has set session_cached_cursors = 600 (Oracle Default=50).
(see Blog: Shared Pool - Memory Allocation - unbalanced) And the memory statistics shows that KGLH0 is the third top memory consumer in all subpools, and SQLA in subpool_1 is extremely problematic. With 6000 concurrently connected sessions and session_cached_cursors = 600, there will be 3'600'000 pinned KGLH0 in the extreme case (this pure math number will not appear since majority of cursors are the same stored in shared_pool).

Continuing with our experiment, a library_cache dump("level 16") confirms that there is always one KGLH0 for each cursor (parent and child), and occasionally one SQLA, so more KGLH0 subheaps than SQLA subheaps. Obviously this is already an optimization since normally SQLA requires more memory than its corresponding KGLH0.

Library_cache dump also shows certain information about memory usage, for example, a particular SQLA:

    Block:  #='0' name=KGLH0^580dee70 pins=0 Change=NONE   
      Heap=70000521f4f8300 Pointer=70000521ad6f918 Extent=70000521ad6f7f8 Flags=I/-/P/A/-/- 
      FreedLocation=0 Alloc=20.015625 Size=23.859375 LoadTime=28198292040 
    Block:  #='6' name=SQLA^580dee70 pins=0 Change=NONE   
     Heap=7000000a0117ec8 Pointer=7000000a49c0040 Extent=7000000a49bf3e8 Flags=I/-/-/A/-/E
     FreedLocation=0 Alloc=23.171875 Size=23.742188 LoadTime=0
To find out what "Alloc" and "Size" denote, make an addr heapdump for Heap=7000000a0117ec8, then you will see following lines:

  dump heapdump_addr 1 0X7000000a0117ec8
    Total heap size    =    24312
    Total free space   =      504
    Permanent space    =       80

and then try to relate them together:

   Total heap size = Size  = 23.742188*1024 = 24312
   Total free space + Permanent space = 23.742188*1024 - 23.171875*1024 = 584 = 504 + 80
  
In the dump file when memory of shard_pool being under pressure (ORA-04031), probably there are certain offending cursors with noticeable KGLH0 or SQLA memory Size, for example: KGLH0^ac7e9a16 consumes about 131'343'512 Bytes.

DataBlocks:  
  Block:  #='0' name=KGLH0^ac7e9a16 pins=0 Change=NONE   
    Heap=700014eb0a10b30 Pointer=7000150fedd92e0 Extent=7000150fedd9170 Flags=I/-/P/A/-/-/- 
    FreedLocation=0 Alloc=128010.093750 Size=128265.148438 LoadTime=16455656254 

3.2. v$sgastat(x$ksmss) vs. x$ksmsp


In v$sgastat 'shared pool', Bytes for "free memory" statistics is really allocable free memory (top RESERVED EXTENTS, and FREE LISTS, RESERVED FREE LISTS in each subpool) plus un-allocable free memory (overhead) in allocated chunks. And Bytes for each v$sgastat component is the effectively occupied memory (not including overhead). So total memory matches the configured shared_pool_size.

Whereas x$ksmsp reports allocable memory in free chuncks. If one chunk is allocated, it is not counted even it still has free memory. Additionally it reports more details, for example, KGLH0 for each cursor. It also outputs one special row for "permanent memor". But it enumerates less rows than v$sgastat.

In conclusion, this chunk-intra free memory probably irritates such a confusion:
              I get ORA-04031 even though there is plenty of free memory (> 10%).

With following query, you can get a rough comparison of both aspects. By only running the subquery_factoring_clause, you can see that above mentioned subheaps:
    KGLH0, KGLS, PLDIA, SQLA, SQLK, SQLP
are mostly allocated in 4K, and there are also more KGLH0 than SQLA due to session_cached_cursors.


with sq as 
  (select substr(ksmchcom, 1, decode((instr(ksmchcom, '^') - 1), -1, 
                           length(ksmchcom), (instr(ksmchcom, '^') - 1))) name
         ,v.*
    from sys.x_ksmsp v)
  ,ksmsp as 
  (select name                          ksmsp_name
        ,round(sum(ksmchsiz)/1024/1024) ksmsp_mb
        ,count(ksmchsiz)                cnt
        ,round(avg(ksmchsiz))           avg
        ,min(ksmchsiz)                  min
        ,max(ksmchsiz)                  max
    from sq group by name)
  ,ksmss as 
  (select ksmssnam                      ksmss_name
      ,round(sum(ksmsslen)/1024/1024)  ksmss_mb
    from sys.x_ksmss 
    where (ksmssnam, ksmdsidx) not in (('free memory', 0))
    group by ksmssnam)
select ksmss_name
      ,ksmss_mb
      ,nvl(ksmss_mb, 0) - nvl(ksmsp.ksmsp_mb, 0) delta_mb
      ,ksmsp.*
from ksmss full outer join ksmsp
  on lower(ksmss.ksmss_name) = lower(ksmsp.ksmsp_name)
where ksmss.ksmss_name in ('KKSSP', 'db_block_hash_buckets', 'KGLH0', 'SQLA', 'free memory')
order by abs(delta_mb) desc nulls last;


KSMSS_NAME             KSMSS_MB  DELTA_MB  KSMSP_NAME   KSMSP_MB  CNT    AVG    MIN   MAX
---------------------- --------- --------- ------------ --------- ------ ------ ----- -------
db_block_hash_buckets  22        22
free memory            90        15        free memory  75        3938   20034  48    2096960
SQLA                   118       -1        SQLA         119       30336  4104   4096  33960
KGLH0                  116       -1        KGLH0        117       29871  4111   4096  52560
KKSSP                  3         0         KKSSP        3         860    4244   568   12352