Oracle memory troubleshooting, Part 3: Automatic top subheap dumping with heapdump

Tanel Poder

2009-06-24

If you haven’t read them – here are the previous articles in Oracle memory troubleshooting series: Part 1, Part 2

In Oracle, the HEAPDUMP dump event in Oracle allows you to dump various heap contents to tracefile. With adding the “level” parameter to this dump event, you can specify which heaps to dump.

Julian Dyke has documented most of the levels here.

There are two little known, but useful level bits for heapdumps – bit 0x10000000 and 0x20000000. These allow Oracle to dump top-5 biggest subheaps in a heap recursively.

When bit 0x10000000 is enabled then Oracle dumps the top-5 subheaps inside any heap its instructed to dump.

When bit 0x20000000 is enabled then Oracle dumps the top-5 subheaps as mentioned above, but in addition Oracle recursively dumps top-5 subheaps inside any subheaps automatically dumped above. So instead of dumping 1 level of subheaps, Oracle recursively dumps 2 levels if such sub-sub-heaps exist.

This reduces the amount of manual work – as Oracle can drill down towards the root cause automatically and dump the relevant information.

Here’s a little test case:

I set the serverout buffer unlimited (Oracle 10.2+) so that Oracle would buffer unlimited amount of dbms_output data in UGA (this is also a “good” way for using up all the memory in your server so you use “unlimited” with care).

SQL> set serverout on size unlimited
SQL>
SQL> exec for i in 1..1000000 loop dbms_output.put_line(lpad('x',10,'x')); end loop;

Without the recursive top subheap dumping we would see output like this (after processing the tracefile with heapdump_analyzer):

This is the usual way for dumping a target process private heap:

SQL> oradebug setorapid 35
Oracle pid: 35, Unix process pid: 26457, image: oracle@linux03
SQL> oradebug dump heapdump 1     <-- level 1 dumps all top level private heaps (top-level PGA,UGA and call heap)
Statement processed.
SQL>

And the output is:

[oracle@linux03 trace]$ heapdump_analyzer LIN11G_ora_26486.trc

  -- Heapdump Analyzer v1.00 by Tanel Poder (  )

  Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
  ---------- ------- ------------ ----------------- ----------------- -----------------
    55065316     841      65476 ,     top uga heap,         freeable,  session heap          <-- session heap is allocated from top uga heap
    41218392    2517      16376 ,     session heap,         freeable,  koh-kghu sessi      <-- koh-kghu session heap is allocated from session heap
    13650208     836      16328 ,     session heap,             free,
       65520       1      65520 ,         pga heap,             free,
       65476       1      65476 ,     top uga heap,         recreate,  session heap
       57736      14       4124 ,     session heap,         freeable,  kxsFrame4kPage
...

We would see that most of the uga memory (roughly 41MB of 55MB) is allocated for for some reason “koh-kghu sessi”. This is a session heap where from allocations for various objects like PL/SQL variables, records and collections are done. So when we’d want to drill down and see inside that heap we could use the HEAPDUMP_ADDR dump with that heap descriptors address as parameter to look inside it.

However with these extra bits mentioned above, Oracle can automatically dump us the contents of biggest subheaps inside the heaps we asked it to dump:

SQL> oradebug setorapid 35
Oracle pid: 35, Unix process pid: 26457, image: oracle@linux03
SQL> oradebug dump heapdump 536870913     <-- 536870913 = 0x20000001 which means private memory dump + 2 levels of subheap dump recursion
Statement processed.
SQL>

The heapdump analyzer output will be more detailed in this case:

[oracle@linux03 trace]$ heapdump_analyzer LIN11G_ora_26486.trc

  -- Heapdump Analyzer v1.00 by Tanel Poder (  )

  Total_size #Chunks  Chunk_size,        From_heap,       Chunk_type,  Alloc_reason
  ---------- ------- ------------ ----------------- ----------------- -----------------
    55065316     841      65476 ,     top uga heap,         freeable,  session heap          <-- session heap is allocated from top uga heap
    41218392    2517      16376 ,     session heap,         freeable,  koh-kghu sessi      <-- koh-kghu session heap is allocated from session heap
    32106828    1963      16356 ,   koh-kghu sessi,         freeable,  pl/sql vc2           <-- pl/sql vc2 (varchar2) collection is allocated from koh-kghu session heap
    13650208     836      16328 ,     session heap,             free,
     9044868     553      16356 ,   koh-kghu sessi,         freeable,  pmucalm coll
       65520       1      65520 ,         pga heap,             free,
       65476       1      65476 ,     top uga heap,         recreate,  session heap
       57736      14       4124 ,     session heap,         freeable,  kxsFrame4kPage
...

As you see above, Oracle has dug a level deeper and figured out that a major part of koh-kghu session heap contains allocations for “pl/sql vc2” reasons (vc2=varchar2).

When you look in a raw heapdump trace you would see “FIVE LARGEST SUB HEAPS” sections, the example below dumps the top-5 subheaps inside a session heap:

FIVE LARGEST SUB HEAPS for heap name="session heap"   desc=0xd66f7c
  Subheap ds=0xd9c600  heap name=  koh-kghu sessi  size=        41217520
   owner=(nil)  latch=(nil)
  Subheap ds=0x74e1b54  heap name=  koh-kghu sessi  size=           17448
   owner=(nil)  latch=(nil)
  Subheap ds=0xd7c4ec  heap name=  koh-kghu sessi  size=            5200
   owner=(nil)  latch=(nil)
  Subheap ds=0xd7ea48  heap name=      PLS SGA hp  size=            4192
   owner=(nil)  latch=(nil)
  Subheap ds=0xd7bc04  heap name=  Alloc environm  size=            4144
   owner=(nil)  latch=(nil)
SUBHEAP 1: desc=0xd9c600
******************************************************
HEAP DUMP heap name="koh-kghu sessi"  desc=0xd9c600
 extent sz=0x224 alt=32767 het=32767 rec=0 flg=2 opc=2
 parent=0xd66f7c owner=(nil) nex=(nil) xsz=0x3fec heap=(nil)
 fl2=0x20, nex=(nil)
EXTENT 0 addr=0x74fd014
  Chunk  74fd01c sz=    16356    freeable  "pl/sql vc2     "
EXTENT 1 addr=0x74e5024
  Chunk  74e502c sz=    16356    freeable  "pl/sql vc2     "
EXTENT 2 addr=0x74e901c
  Chunk  74e9024 sz=    16356    freeable  "pl/sql vc2     "
EXTENT 3 addr=0x74ed014
  Chunk  74ed01c sz=    16356    freeable  "pl/sql vc2     "
EXTENT 4 addr=0x74d5024
  Chunk  74d502c sz=    16356    freeable  "pmucalm coll   "
EXTENT 5 addr=0x74d901c
  Chunk  74d9024 sz=    16356    freeable  "pl/sql vc2     "

If you want to get a little terser overview of the heap/subheap details you can use the following command below:

[oracle@linux03 trace]$ egrep "HEAP DUMP|FIVE LARGEST|^SUBHEAP|Subheap|^Total" LIN11G_ora_26486.trc
[...snip...]
HEAP DUMP heap name="top uga heap"  desc=0xfaa4540 <-- heap which we are dumping
Total heap size    = 55171208
Total free space   =    40416
FIVE LARGEST SUB HEAPS for heap name="top uga heap"   desc=0xfaa4540
  Subheap ds=0xd66f7c  heap name=    session heap  size=        55130792  <-- the first of top-5 subheaps is a session heap
SUBHEAP 1: desc=0xd66f7c
HEAP DUMP heap name="session heap"  desc=0xd66f7c <-- lets dump it
Total heap size    = 55113936
Total free space   = 13704108
FIVE LARGEST SUB HEAPS for heap name="session heap"   desc=0xd66f7c <-- this is a top-5 subheap dump at level 2 (under the session heap found at previous level)
  Subheap ds=0xd9c600  heap name=  koh-kghu sessi  size=        41217520 <-- we've found top-5 biggest subheaps and will dump them below
  Subheap ds=0x74e1b54  heap name=  koh-kghu sessi  size=           17448
  Subheap ds=0xd7c4ec  heap name=  koh-kghu sessi  size=            5200
  Subheap ds=0xd7ea48  heap name=      PLS SGA hp  size=            4192
  Subheap ds=0xd7bc04  heap name=  Alloc environm  size=            4144
SUBHEAP 1: desc=0xd9c600
HEAP DUMP heap name="koh-kghu sessi"  desc=0xd9c600
Total heap size    = 41167104
Total free space   =        0
FIVE LARGEST SUB HEAPS for heap name="koh-kghu sessi"   desc=0xd9c600
SUBHEAP 2: desc=0x74e1b54
HEAP DUMP heap name="koh-kghu sessi"  desc=0x74e1b54
Total heap size    =    17392
Total free space   =    17392
FIVE LARGEST SUB HEAPS for heap name="koh-kghu sessi"   desc=0x74e1b54
SUBHEAP 3: desc=0xd7c4ec
HEAP DUMP heap name="koh-kghu sessi"  desc=0xd7c4ec
Total heap size    =     5144
Total free space   =        0
FIVE LARGEST SUB HEAPS for heap name="koh-kghu sessi"   desc=0xd7c4ec
SUBHEAP 4: desc=0xd7ea48
HEAP DUMP heap name="PLS SGA hp"  desc=0xd7ea48
Total heap size    =     4136
Total free space   =      640
FIVE LARGEST SUB HEAPS for heap name="PLS SGA hp"   desc=0xd7ea48
SUBHEAP 5: desc=0xd7bc04
HEAP DUMP heap name="Alloc environm"  desc=0xd7bc04
Total heap size    =     4108
Total free space   =        0
FIVE LARGEST SUB HEAPS for heap name="Alloc environm"   desc=0xd7bc04

These dumping bits work for shared pool dumps as well by the way (when using level 0x20000002 for example). Remember that shared pool heapdumps are dangerous though as they can hang your instance for the dump duration so that even sysdba can’t log on (that’s why shared pool heapdumps should be used as a last resort when diagnosing memory shortage and leaks).


  1. I am finally close to launching the completely rebuilt 2024 versions of my Linux & AOT classes in my Learning Platform! (Updates to SQL Tuning class in H2 2024):
    Advanced Oracle SQL Tuning training. Advanced Oracle Troubleshooting training, Linux Performance & Troubleshooting training. Check them out!
  2. Get randomly timed updates by email or follow Social/RSS