Oracle memory troubleshooting using analysis on heapdumps
This blogpost is about analysing Oracle heap dumps. It is an extension to earlier work, Tanel Poder’s heap dump analyzer. So hat tip to Tanel, he’s done the hard work, I merely increased the presentation options. The heap analyser script that I wrote analyses Oracle heapdumps from the trace file that the dump was written to by the Oracle database. Because the heap dump representation is the same between PGA and SGA memory, it can work on both. The reason for this is that memory management is done by the same memory manager, and is commonly called ‘kgh’ (kernel generic heap) managed memory.
Please mind that for PGA analysis, not all memory is managed by the kgh memory manager. For example memory used for networking (sqlnet) is allocated totally outside of the kgh memory manager.
Let’s take the output of a PGA heap dump at level 29 (PGA, UGA, CGA, top call heaps, call heaps, session heap; executed via ‘alter session set events ‘immediate trace name heapdump level 29”):
$ ./heap_analyze.awk /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_6515.trc Heap dump analyzer v0.1 by Frits Hoogland heap size totals (all sizes in bytes) ============================================================================================== heap name total size ---------------------------------------------------------------------------------------------- top call heap 393096 callheap 36200 pga heap 1016816 top uga heap 262048 session heap 261744 ------------------------------------------ total 1969904 top 5 allocation by total size per alloc reason per heap ============================================================================================== heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- top call heap 5 354456 top call heap callheap 11 37616 top call heap perm 2 1024 heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- callheap 10 35920 callheap ctxtcds: kksQui 1 160 callheap perm 1 120 heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- pga heap perm 44 290256 pga heap Fixed UGA heap 1 226232 pga heap diag pga 21 214048 pga heap 7 62616 pga heap kfkio bucket 1 40984 heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- top uga heap session heap 4 261928 top uga heap perm 1 120 heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- session heap perm 3 63608 session heap 8 63528 session heap koh-kghu sessi 5 28288 session heap kxsFrame4kPage 6 24912 session heap kxsFrame8kPage 3 24744 top 5 allocations by total size per alloc reason, type, chunk size per heap ============================================================================================== heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- top call heap 1 free 131048 131048 top call heap 2 free 65512 131024 top call heap 1 free 63496 63496 top call heap 1 free 28888 28888 top call heap callheap 6 freeable 4224 25344 heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- callheap 6 free 4184 25104 callheap 2 free 4192 8384 callheap 1 free 1784 1784 callheap 1 free 648 648 callheap ctxtcds: kksQui 1 freeable 160 160 heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- pga heap Fixed UGA heap 1 recreate 226232 226232 pga heap perm 1 perm 71720 71720 pga heap diag pga 1 freeable 55048 55048 pga heap diag pga 2 freeable 27456 54912 pga heap 1 free 47296 47296 heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- session heap perm 1 perm 43584 43584 session heap 1 free 36544 36544 session heap kxsFrame4kPage 6 freeable 4152 24912 session heap kxsFrame8kPage 3 freeable 8248 24744 session heap 1 free 20352 20352 heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- top uga heap session heap 3 freeable 65512 196536 top uga heap session heap 1 recreate 65392 65392 top uga heap perm 1 perm 120 120 top 5 allocations by total size per heap, alloc reason, type, chunk size ============================================================================================== heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- pga heap Fixed UGA heap 1 recreate 226232 226232 top uga heap session heap 3 freeable 65512 196536 top call heap 1 free 131048 131048 top call heap 2 free 65512 131024 pga heap perm 1 perm 71720 71720
I figured that the first thing you want to see, are the heaps that are in the dump, and their sizes. That’s what is visible in rows 2-12.
I summed the heap sizes, which might make sense, or it might not.
In this case, with a heap dump that includes PGA, UGA and CGA plus session and call heaps, it means there are heaps in the dump that are part of another heap that is in the dump. So the total size here is bogus. This means that you need to have an understanding of what is actually dumped.
The next section, top 5 allocations by total size per alloc reason per dump, shows a per-heap summary by the allocation reason in the dump. If there’s no alloc reason, it’s free memory. Because this is a heap dump of a session that has done nothing (I just started sqlplus and ran the dump of its own PGA memory), you see that a lot of memory chunks are free memory. If you look closely to the allocation reasons, you can see that the ‘top call heap’ has a memory section that is called ‘callheap’ which is slightly larger than the ‘callheap’ section in the heap totals, and the ‘top uga heap’ section has a memory section called ‘session heap’ that is slightly larger than the ‘session heap’ section in the heap totals. In this case, it means that you can actually see the subheads in the parent heap allocation totals. The subheap size must be slightly larger in the parent heap because of headers in the memory allocations which are needed memory management. Please mind that this is based on my knowledge of how process memory is created, the only way to be absolutely sure is that a heap is part of another heap is to look at the memory addresses. This output only shows the heap names, not the addresses. The purpose of this section is to have an understanding of where memory is allocated to in a heap.
The following section, top 5 allocations by total size per alloc reason, type, chunk size per heap shows a per-heap summary by reason, type and chunksize, so you can investigate if specific types and sizes of chunks are causing issues or weird behaviour.
The last section is the same as the previous section, but doesn’t do it per heap. This is identical to what Tanel’s heapdump_analyzer shows.
You can find the heap_analyze.awk script here: https://gitlab.com/FritsHoogland/oracle_memory_analyze/blob/master/heap_analyze.awk
This is the output of a dump of the SGA of my small test database (oradebug dump heapdump 2):
$ ./heap_analyze.awk /u01/app/oracle/diag/rdbms/tt/tt/trace/tt_ora_9461.trc Heap dump analyzer v0.1 by Frits Hoogland heap size totals (all sizes in bytes) ============================================================================================== heap name total size ---------------------------------------------------------------------------------------------- sga heap(1,0) 285210904 sga heap(1,3) 83885560 ------------------------------------------ total 369096464 top 5 allocation by total size per alloc reason per heap ============================================================================================== heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- sga heap(1,0) perm 22 188068904 sga heap(1,0) 141 17975320 sga heap(1,0) SO private sga 17 14268008 sga heap(1,0) KQR PO 8271 7568912 sga heap(1,0) KGLHD 9381 7023960 heap alloc reason #chunks total size ---------------------------------------------------------------------------------------------- sga heap(1,3) 51 14120288 sga heap(1,3) SQLA^6d9b8a7e 337 1380352 sga heap(1,3) SQLA^31cc505b 167 684032 sga heap(1,3) SQLA^aab93e92 162 663552 sga heap(1,3) PLDIA^191e0a8d 155 634880 top 5 allocations by total size per alloc reason, type, chunk size per heap ============================================================================================== heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- sga heap(1,0) perm 1 perm 15937496 15937496 sga heap(1,0) perm 1 perm 15931312 15931312 sga heap(1,0) perm 1 perm 15811464 15811464 sga heap(1,0) perm 1 perm 15741952 15741952 sga heap(1,0) perm 1 perm 15723584 15723584 heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- sga heap(1,3) 1 free 9883208 9883208 sga heap(1,3) 4 R-free 839480 3357920 sga heap(1,3) SQLA^6d9b8a7e 336 freeableU 4096 1376256 sga heap(1,3) 1 R-free 839360 839360 sga heap(1,3) SQLA^31cc505b 166 freeableU 4096 679936 top 5 allocations by total size per heap, alloc reason, type, chunk size ============================================================================================== heap alloc reason #chunks type chunk size total size ---------------------------------------------------------------------------------------------- sga heap(1,0) perm 1 perm 15937496 15937496 sga heap(1,0) perm 1 perm 15931312 15931312 sga heap(1,0) perm 1 perm 15811464 15811464 sga heap(1,0) perm 1 perm 15741952 15741952 sga heap(1,0) perm 1 perm 15723584 15723584
It’s interesting to see only subpool 1 sub-sub pool 1 and 3 are used. Subpool 1,1 contains a lot of permanent allocations and a lot of allocations that might have a more permanent nature, like KQR (dictionary cache) allocations, Subpool 1,3 seems to have allocations that are deemed more transient in nature, like SQLA (sql area) allocations. This might be wildly different in databases that are actually heavily used, this is an idle lab database.
Please mind it’s important to understand that dumping the shared pool requires obtaining the respective latches, so doing that on a live production system might lead to (severe) issues. Only do this if you know what you are doing. For the PGA there can only be one using process by definition, but be careful there too, if you request a PGA dump you are interacting with memory that is deemed private by the process that is using that.
If you require more rows to be shown than the 5 that are shown, set the ‘group_sum_nr’ variable to the amount you need on row 4 of the script.
Frits Hoogland Weblog 
Pingback: Oracle memory troubleshooting using analysis on heapdumps, part 2 | Frits Hoogland Weblog