RE: ORA-04031 in spite of enough free memory

Hi Tanel,

I embedded my inputs below.

“How many shared pool sub-pools do you have? The heapdump analyzer output indicates that just one, but confirming to be sure.”
=> Yes, it's only 1.

“You're welcome! The "analyzer" word in heapdump_analyzer is actually a bit of an overkill as it's just a simple shell/AWK script that does a group by on the chunk data. But "analyzer" sounds fancier than "summarizer" :-)”
=> The script might have been simple to implement, but the idea behind is brilliant. It's such a great tool - made by the super-geek for geeks.

“Not sure if you meant freeable as an Oracle term here or in general sense, like memory that is already free + memory chunks that can be discarded by others at will (recreatable).”
=> I was, obviously, mistaken what "freeable" means - mea culpa!

“But in your heapdump output there was plenty of already-free space in "sga heap(1,0)" reserved area anyway (R-free), so your 50kB allocations should have succeeded in theory (assuming that your _shared_pool_reserved_min_alloc is at its default value 4400).”
=> That's what I thought as well.

SQL> SELECT * FROM v$shared_pool_reserved ;

FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE ---------- ------------- ---------- ------------- ---------- ------------- USED_COUNT MAX_USED_SIZE REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE ---------- ------------- ---------- -------------- -------------- ------------- REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS

---------------- ----------------- ------------------------- ----------------
LAST_ABORTED_SIZE     CON_ID
----------------- ----------
  52890512    91823.8056        140       2096960   20882224    36253.8611
       436         52864     433428            116          52864         52864
              49             52864                2147483647                0
                0          0

       SUB       SSUB    FLUSHED   LRU LIST  RECURRENT  TRANSIENT FREE UNPIN
      POOL       POOL     CHUNKS OPERATIONS     CHUNKS     CHUNKS  UNSUCCESS

         1          0    7302284  181073225       9184      11146         49
      52864     433428        116      52864       52864

IDX DUR FLUSHED ALLOC_COMMENT ALLOC_SIZE

--- --- ---------- -------------------- ----------
OBJECT_NAME                      HASH_VALUE SES_ADDR
-------------------------------- ---------- ----------------
  1   0      49336 ktsl_load_disp-2          52864
                                          0 00000000D6ACB0B0

  1   0        128 BAMIMA: Bam Buffer         4248
ST_PARTITION_MANAGEMENT_PAK      1482324023 00000000D689C558

  1   0        128 BAMIMA: Bam Buffer         4272
DBMS_RCVMAN                      2876560350 00000000D6D00D30


IDX DUR    FLUSHED ALLOC_COMMENT        ALLOC_SIZE
--- --- ---------- -------------------- ----------
OBJECT_NAME                      HASH_VALUE SES_ADDR
-------------------------------- ---------- ----------------
  1   0         96 BAMIMA: Bam Buffer         4176
DBMS_RCVMAN                      1700058067 00000000D6BFB268

  1   0         56 kglpda                     4224

  1   0         40 4709.kgght                 4216

IDX DUR FLUSHED ALLOC_COMMENT ALLOC_SIZE

--- --- ---------- -------------------- ----------
OBJECT_NAME                      HASH_VALUE SES_ADDR
-------------------------------- ---------- ----------------
  1   0          8 opndef: qcopCreateO        4200

  1 0 8 kkotbp : kkoipt 4304 WITH awr_params as ( SELECT ... 3496881689 00000000D6FF3330

8 rows selected.

Thanks,

Nenad

https://nenadnoveljic.com/blog/

From: Tanel Poder <tanel_at_tanelpoder.com> Sent: Dienstag, 28. Mai 2019 19:32
To: Noveljic Nenad <nenad.noveljic_at_vontobel.com> Cc: ORACLE-L (oracle-l_at_freelists.org) <oracle-l_at_freelists.org> Subject: Re: ORA-04031 in spite of enough free memory

Hi Nenad,

Yep, interesting. As the failed allocation size was over 50kB, these allocations can (also) use shared pool reserved area. The heapdump analysis shows that there's plenty of large R-free chunks in sub-heap 1 sub-sub-heaps 0 and 3 (12.2 uses only 2 sub-sub-heaps/durations instead of 4).

Looks like the KTSL subheap allocations below are done from duration 0.

 Total_size #Chunks Chunk_size, From_heap, Chunk_type, Alloc_reason

• ------- ------------ ----------------- ----------------- ----------------- ... 73586688 1392 52864 , sga heap(1,0), freeable, KTSL subheap 20987008 397 52864 , sga heap(1,0), R-freeable, KTSL subheap 19408560 11390 1704 , sga heap(1,0), freeable, kcbi io desc sl 16789760 20 839488 , sga heap(1,3), R-free, 12581760 6 2096960 , sga heap(1,0), R-free, 11036608 11 1003328 , sga heap(1,0), R-free,

How many shared pool sub-pools do you have? The heapdump analyzer output indicates that just one, but confirming to be sure.

Also, can you post the output of:

1. SELECT * FROM v$shared_pool_reserved
2. _at_kghlu.sql<https://github.com/tanelpoder/tpt-oracle/blob/master/kghlu.sql>
3. _at_ksmlru.sql<https://github.com/tanelpoder/tpt-oracle/blob/master/ksmlru.sql>

The above commands/scripts are safe to run (unlike x$ksmsp queries) as they don't hold shared pool latches for a long time. As long as you haven't restarted the instance since the problem happened, "current" output should be fine.

A little side note: Thank you, Tanel, for providing such a useful script!

You're welcome! The "analyzer" word in heapdump_analyzer is actually a bit of an overkill as it's just a simple shell/AWK script that does a group by on the chunk data. But "analyzer" sounds fancier than "summarizer" :-)

In particular, I'm interested to know what might have triggered the resizing decision when there was enough consecutive freeable memory to fulfil the request. Further, is there some possibility to trace the resizing decisions?

Not sure if you meant freeable as an Oracle term here or in general sense, like memory that is already free + memory chunks that can be discarded by others at will (recreatable).

Unlike recreatable memory chunks, Freeable memory chunks can't be freed by just anyone at will. It's a bit of a simplification, but only the codepath that allocated the freeable chunk, can free it later on. In other words, freeing freeable chunks is a deliberate effort, like "as we don't need this freeable chunk at address 0x12345678 anymore, let's free exactly that chunk" ... as opposed to "let's free whatever is the first recreatable chunk in the LRU list".

But in your heapdump output there was plenty of already-free space in "sga heap(1,0)" reserved area anyway (R-free), so your 50kB allocations should have succeeded in theory (assuming that your _shared_pool_reserved_min_alloc is at its default value 4400).

--
Tanel Poder
https://blog.tanelpoder.com/seminar

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">p { font-family: Arial;font-size:9pt }</style>
</head>
<body>
<p>
<br>Important Notice</br>
<br />

</p>
</body>
</html>

--
http://www.freelists.org/webpage/oracle-l