{"id":1443,"date":"2017-09-03T01:58:20","date_gmt":"2017-09-02T23:58:20","guid":{"rendered":"http:\/\/www.ora-solutions.net\/web\/?p=1443"},"modified":"2018-08-08T19:03:45","modified_gmt":"2018-08-08T17:03:45","slug":"ora-4031-troubleshooting-issue-with-durations-in-11gr2","status":"publish","type":"post","link":"https:\/\/www.ora-solutions.net\/web\/2017\/09\/03\/ora-4031-troubleshooting-issue-with-durations-in-11gr2\/","title":{"rendered":"ORA-4031 Troubleshooting &#8211; Issue with durations in 11gR2"},"content":{"rendered":"<p>I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.\u00a0 Being curious, I digged into the trace files again to confirm and understand the issue here. This blog post describes the method to analyze the issue and how to troubleshoot.<\/p>\n<ul>\n<li>First, when the issue first appears, it creates a 4031 trace file, which does not really give enough information to solve the problem. So I asked Oracle Support for specific trace events to properly diagnose it. He correctly mentioned event 10235 level 65536 and heapdump level 536870914\u00a0 but required an instance restart. After explaining that this is not really possible and researching myself, I found these two events, which do not really need an instance restart. Event 10235 was not really necessary in this case, because the description of the allocations did not matter here.<\/li>\n<\/ul>\n<pre lang=\"sql\">alter system set events '4031 trace name HEAPDUMP level 536870914, lifetime 1; name errorstack level 3, lifetime 1';<\/pre>\n<ul>\n<li>Now, after the issue appeared again, we finally had the relevant diagnostic information.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<pre lang=\"text\">Allocation Request Summary Informaton\r\n=====================================\r\nAllocation request for: kglsim object batch\r\n...\r\nHeap: 0x6007b278, size: 3896\r\n******************************************************\r\nHEAP DUMP heap name=\"sga heap(3,0)\"\u00a0 desc=0x6007b278\r\nextent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=2\r\n...\r\ndurations enabled for this heap\r\nreserved granules for root 0 (granule size 134217728)\r\nTotal heap size    =2684352800. (2559 MB)\r\nTotal free space   =    38360\r\nTotal reserved free space   =120837856 (115MB) \r\nPermanent space    =2563474760\r\n\r\n==============================================\r\nTOP 10 MEMORY USES FOR SGA HEAP SUB POOL 1\r\n----------------------------------------------\r\n\"free memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6597 MB 71%\r\n\"gcs resources\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1080 MB 12%\r\n\"gcs shadows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 748 MB\u00a0 8%\r\n\"db_block_hash_buckets\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200 MB\u00a0 2%\r\n\"kglsim object batch\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 189 MB\u00a0 2%\r\n\"kglsim heap\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 112 MB\u00a0 1%\r\n\"gcs res hash bucket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 64 MB\u00a0 1%\r\n\"ges big msg buffers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 54 MB\u00a0 1%\r\n\"dbktb: trace buffer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 51 MB\u00a0 1%\r\n\"KGLH0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 48 MB\u00a0 1%\r\n-----------------------------------------\r\nfree memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6597 MB\r\nmemory alloc.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2747 MB\r\nSub total\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9344 MB\r\n==============================================\r\nTOP 10 MEMORY USES FOR SGA HEAP SUB POOL 2\r\n----------------------------------------------\r\n\"free memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6641 MB 70%\r\n\"gcs resources\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1080 MB 11%\r\n\"gcs shadows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 747 MB\u00a0 8%\r\n\"db_block_hash_buckets\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 512 MB\u00a0 5%\r\n\"gcs res hash bucket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 64 MB\u00a0 1%\r\n\"kglsim object batch\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 59 MB\u00a0 1%\r\n\"dbktb: trace buffer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 51 MB\u00a0 1%\r\n\"KGLH0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 44 MB\u00a0 0%\r\n\"kglsim heap\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 35 MB\u00a0 0%\r\n\"ges enqueues\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22 MB\u00a0 0%\r\n-----------------------------------------\r\nfree memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6641 MB\r\nmemory alloc.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2831 MB\r\nSub total\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9472 MB\r\n==============================================\r\nTOP 10 MEMORY USES FOR SGA HEAP SUB POOL 3\r\n----------------------------------------------\r\n\"free memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6488 MB 71%\r\n\"gcs resources\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1080 MB 12%\r\n\"gcs shadows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 747 MB\u00a0 8%\r\n\"db_block_hash_buckets\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 200 MB\u00a0 2%\r\n\"kglsim object batch\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 105 MB\u00a0 1%\r\n\"gc name table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 72 MB\u00a0 1%\r\n\"gcs res hash bucket\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 64 MB\u00a0 1%\r\n\"kglsim heap\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 63 MB\u00a0 1%\r\n\"dbktb: trace buffer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 51 MB\u00a0 1%\r\n\"KGLH0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 46 MB\u00a0 1%\r\n-----------------------------------------\r\nfree memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6488 MB\r\nmemory alloc.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2600 MB\r\nSub total\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9088 MB\r\n==============================================\r\nTOP 10 MEMORY USES FOR SGA HEAP SUB POOL 4\r\n----------------------------------------------\r\n\"free memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6663 MB 70%\r\n\"gcs resources\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1080 MB 11%\r\n\"gcs shadows\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 749 MB\u00a0 8%\r\n\"db_block_hash_buckets\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 512 MB\u00a0 5%\r\n\"kglsim object batch\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 59 MB\u00a0 1%\r\n\"dbktb: trace buffer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 51 MB\u00a0 1%\r\n\"KGLH0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 47 MB\u00a0 0%\r\n\"kglsim heap\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 35 MB\u00a0 0%\r\n\"FileOpenBlock\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 35 MB\u00a0 0%\r\n\"ges enqueues\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \"\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22 MB\u00a0 0%\r\n-----------------------------------------\r\nfree memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 6663 MB\r\nmemory alloc.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2809 MB\r\nSub total\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 9472 MB\r\nTOTALS ---------------------------------------\r\nTotal free memory\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 26 GB\r\nTotal memory alloc.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 11 GB\r\nGrand total\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 37 GB\r\n\r\n<\/pre>\n<ul>\n<li>This shows that 4031 was triggered for memory allocation request in sga heap (3,0) with size of 3896. This is smaller than 4400 bytes (_shared_pool_reserved_min_alloc), so the request can not be served the the shared pool reserved area.\u00a0 Next, we can see that we have 4 subpools (kghdsidx_count) and each of them has around 6 GB of free memory. So, in total, we have 37 GB shared pool with 26 GB of it free, but one mini-subpool (duration 0) of subpool 3 still has run out of free contigous memory bigger than 3792 bytes.<\/li>\n<li>Running Tanel\u00b4s <a href=\"http:\/\/blog.tanelpoder.com\/2009\/01\/02\/oracle-memory-troubleshooting-part-1-heapdump-analyzer\/\">heapdump_analyzer<\/a>, I could verify that the largest chunk for sga heap(3,0) was indeed 3792 bytes and that all bigger chunks were allocated by &#8220;PERM&#8221; allocations.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<pre lang=\"text\">\u00a0.\/heapdump_analyzer\u00a0 .\/PROD1_ora_27396.trc > heap.txt\r\n\r\ngrep -e \"---\" -e \"Total_size\" -e \"sga heap(3,0)\"\u00a0 heap.txt\r\n\r\n-- Heapdump Analyzer v1.00 by Tanel Poder ( http:\/\/www.tanelpoder.com )\r\n\r\nTotal_size #Chunks\u00a0 Chunk_size,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 From_heap,\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Chunk_type,\u00a0 Alloc_reason\r\n---------- ------- ------------ ----------------- ----------------- -----------------\r\n\r\n114123720\u00a0\u00a0\u00a0\u00a0\u00a0 17\u00a0\u00a0\u00a0 6713160 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 R-free,\r\n28442152\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0 28442152 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n...\r\n7485000\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0 7485000 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n6713080\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0 6713080 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 R-free,\r\n6712104\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0 6712104 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 R-perm,\u00a0 perm\r\n...\r\n3028568\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0 3028568 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n...\r\n\r\n8928\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2232 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n7864\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 7864 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n5488\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2744 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 perm,\u00a0 perm\r\n3792\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3792 ,\u00a0\u00a0\u00a0 sga heap(3,0),\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 free,\u00a0 <<<<<<<<<<<<<<\r\n...\r\n<\/pre>\n<p>This shows that the first chunk of chunk_type \"free\" is of 3792 bytes, but request was for 3896 bytes. The<br \/>\nsubpool (3,0) only has R-free (for shared_pool_reserved area) and PERM allocations of bigger size. The subpool (3,0) consists of 20 Extents \u00e1 (128M Granule size). Each extent has <\/p>\n<p>Researching the issue with \"PERM\" allocations brought me to:<\/p><\/blockquote>\n<blockquote><p>ORA-4031: unable to allocate 4160 bytes of shared memory (\"shared pool\",\"unknown object\",\"sga heap(4,0)\",\"modification \") (Doc ID 1675470.1)<\/p><\/blockquote>\n<p>This note also confirms that the classification in 4 durations leads to a weak spot of duration 0 in any subpool for memory allocations due to high PERM utilization. The note mentions patch 8857940, which was also finally recommended by the support engineer.<\/p>\n<pre lang=\"text\">\r\nHEAP DUMP heap name=\"sga heap(1,0)\"  desc=0x600680e8\r\nTotal heap size    =2818570440\r\nTotal free space   =    28008 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< \r\nTotal reserved free space   =127549960\r\nHEAP DUMP heap name=\"sga heap(1,1)\"  desc=0x60069940\r\nTotal heap size    =805305840\r\nTotal free space   =632590232\r\nTotal reserved free space   = 40243520\r\nHEAP DUMP heap name=\"sga heap(1,2)\"  desc=0x6006b198\r\nTotal heap size    =1879046960\r\nTotal free space   =1734275840\r\nTotal reserved free space   = 93975928\r\nHEAP DUMP heap name=\"sga heap(1,3)\"  desc=0x6006c9f0\r\nTotal heap size    =4294964480\r\nTotal free space   =4072007248\r\nTotal reserved free space   =214821040\r\nHEAP DUMP heap name=\"sga heap(2,0)\"  desc=0x600719b0\r\nTotal heap size    =2952788080\r\nTotal free space   =    19144 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\r\nTotal reserved free space   =147689440\r\nHEAP DUMP heap name=\"sga heap(2,1)\"  desc=0x60073208\r\nTotal heap size    =671088200\r\nTotal free space   =524561448\r\nTotal reserved free space   = 33565720\r\nHEAP DUMP heap name=\"sga heap(2,2)\"  desc=0x60074a60\r\nTotal heap size    =1879046960\r\nTotal free space   =1738701544\r\nTotal reserved free space   = 93984160\r\nHEAP DUMP heap name=\"sga heap(2,3)\"  desc=0x600762b8\r\nTotal heap size    =4429182120\r\nTotal free space   =4202264536\r\nTotal reserved free space   =221534200\r\nHEAP DUMP heap name=\"sga heap(3,0)\"  desc=0x6007b278\r\nTotal heap size    =2684352800\r\nTotal free space   =    38360 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\r\nTotal reserved free space   =120837856\r\nHEAP DUMP heap name=\"sga heap(3,1)\"  desc=0x6007cad0\r\nTotal heap size    =671088200\r\nTotal free space   =526361840\r\nTotal reserved free space   = 33565720\r\nHEAP DUMP heap name=\"sga heap(3,2)\"  desc=0x6007e328\r\nTotal heap size    =1879046960\r\nTotal free space   =1736230384\r\nTotal reserved free space   = 93977784\r\nHEAP DUMP heap name=\"sga heap(3,3)\"  desc=0x6007fb80\r\nTotal heap size    =4294964480\r\nTotal free space   =4076323080\r\nTotal reserved free space   =214821040\r\nHEAP DUMP heap name=\"sga heap(4,0)\"  desc=0x60084b40\r\nTotal heap size    =2952788080\r\nTotal free space   =     8536 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\r\nTotal reserved free space   =140977632\r\nHEAP DUMP heap name=\"sga heap(4,2)\"  desc=0x60087bf0\r\nTotal heap size    =1879046960\r\nTotal free space   =1735347144\r\nTotal reserved free space   = 93984160\r\nHEAP DUMP heap name=\"sga heap(4,3)\"  desc=0x60089448\r\nTotal heap size    =4429182120\r\nTotal free space   =4198496144\r\nTotal reserved free space   =221534200<\/pre>\n<p>For reference, these are the relevant parameters. Please note that all of these are set to default, except _kghdsidx_count which was reduced from 4 earlier and _ksmg_granule_size which was reduced to 128M.<\/p>\n<pre lang=\"text\">\r\nNAME\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VALUE\r\n---------------------------------------- --------------------\r\n__shared_pool_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 38923141120\r\n_dm_max_shared_pool_pct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1\r\n_enable_shared_pool_durations\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TRUE\r\n_io_shared_pool_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4194304\r\n_kghdsidx_count\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4\r\n_ksmg_granule_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 134217728\r\n_memory_imm_mode_without_autosga\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 TRUE\r\n_shared_pool_max_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0\r\n_shared_pool_minsize_on\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FALSE\r\n_shared_pool_reserved_min_alloc\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4400\r\n_shared_pool_reserved_pct\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 5\r\nshared_pool_reserved_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1946157056\r\nshared_pool_size\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 38654705664<\/pre>\n<p>Thanks to Tanel, <a href=\"https:\/\/orainternals.wordpress.com\/2009\/08\/06\/ora-4031-and-shared-pool-duration\/\">Riyaj<\/a> and <a href=\"https:\/\/mahmoudhatem.wordpress.com\/2015\/04\/22\/oracle-12c-shared-pool-and-durations-enhancement\/\">Hatem Mahmoud <\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>I recently had to troubleshoot an ORA-4031 issue at a client site. The issue reappeared 3 times within 2 months and only after escalating the SR to SEV1 and being quite persistent for an explanation, the second engineer attempting to solve the issue finally got it right.\u00a0 Being curious, I digged into the trace files [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[14,57,5],"tags":[],"class_list":["post-1443","post","type-post","status-publish","format-standard","hentry","category-11g","category-11gr2","category-oracle-database"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1443","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/comments?post=1443"}],"version-history":[{"count":11,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1443\/revisions"}],"predecessor-version":[{"id":1666,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/posts\/1443\/revisions\/1666"}],"wp:attachment":[{"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/media?parent=1443"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/categories?post=1443"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ora-solutions.net\/web\/wp-json\/wp\/v2\/tags?post=1443"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}