RE: ORA-04031 in spite of enough free memory
Date: Mon, 3 Jun 2019 08:05:16 +0000
Message-ID: <93017_1559549132_5CF4D4CC_93017_3836_1_334e15bd481745e6b1e9ed9e1c4b9755_at_vontobel.com>
First and foremost, thank you, Tanel, not only for clarifying the root-cause of the ORA-04031 error, but also for exposing the details of your thought process. Exactly these kind of contributions are the essence of what makes Oracle-l such an invaluable resource. Seriously, this list rocks! I mean where else could you engage in enriching discussions with the Oracle top-notch experts?
With regard to large PL/SQL packages, they doesn’t seem to be a problem in this case, in my humble opinion, as according to x$ksmlru, there were only three 4k allocations causing other objects to be aged out.
Best regards,
Nenad
https://nenadnoveljic.com/blog/
From: Mark W. Farnham <mwf_at_rsiz.com>
Sent: Samstag, 1. Juni 2019 00:53
To: tanel_at_tanelpoder.com; Noveljic Nenad <nenad.noveljic_at_vontobel.com>
Cc: 'ORACLE-L' <oracle-l_at_freelists.org>
Subject: RE: ORA-04031 in spite of enough free memory
and (most assuredly not but) as operational avoidance if you have packages that 1) need a big contiguous chunk and 2) are used just a little too infrequently to always stay in, you can pull them in on instance warm start.
Whether you KEEP them or have a heartbeat routine to keep them in, you CAN avoid space management. The question is whether avoiding the space management and possible errors dynamically pulling something in is worth always sitting on the memory.
When memory was expensive and Ebusiness suite had some really big chunks for month close/month open, some shops did instance warmstarts with different packages loaded for a few days and then another to restart for regular business. One RAC that might be one instance restarted and application affinity used to run all the month end/month open (or year end/year open) stuff on that particular instance.
Good luck and enjoy the free gold Tanel gave you! Reminds me why I like oracle-l so much!
mwf
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tanel Poder
Sent: Wednesday, May 29, 2019 2:31 PM
To: Noveljic Nenad
Cc: Tanel Poder; ORACLE-L (oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>)
Subject: Re: ORA-04031 in spite of enough free memory
The v$shared_pool_reserved.REQUEST_MISSES & REQUEST_FAILURES show that there were cases where the suitable freelists were empty and even after flushing any unpinned recreatable chunks from the reserved area, a large enough chunk of contiguous free memory wasn't available.
_at_kghlu.sql RESERVED MISSES & FREE UNPIN UNSUCCESS confirm it too.
So, despite the heapdump showing plenty of free chunks, your session failed to allocate one. Some possible scenarios:
- There was a spike of shared pool usage for Securefile space management "dispensers" - for example when updating a heavily partitioned securefile table (every segment needs its own dispenser). By the time you hit the error and heapdump kicked in, the usage had dropped (either due to your own call "rolling back" thanks to the error) or due to some other activity ending at the same time
- There actually was enough suitable free space in shared pool, but due to some bug Oracle didn't get to it and bailed out (after flushing lots of small chunks)
As you're not running on a big NUMA system with multiple shared pool subpools, I think #2 is less likely.
This kind of an allocation (50kB+ that apparently isn't broken down to 4kB chunks like is the case with many library cache objects) if one remaining valid reason for increasing the shared_pool_reserved_size (or _shared_pool_reserved_pct) if you anticipate lots of partitions + securefile dispenser allocations to be used. The default is 5% of shared pool though, so if you just increase the shared pool to production size, the reserved area would go up as well (and if you don't have a ridiculous amount of concurrently modified securefile segments in partitioned tables, you should be good).
As a side-note, back in 9i days one would need to do this for session v$parameter allocations if anticipated lots of sessions - as each session allocated a ~28kB contiguous chunk from shared pool for its v$parameter values. Nowadays these parameter table allocations are split to ~2kB chunks, so there's no fragmentation issue. The same with most common library cache objects (cursors, plans, even PL/SQL objects now) - their allocations are split & "standardized" to max 4kB chunks. So much easier to find 10 x 4kB chunk of free memory from shared pool than 1 x 40kB chunk of contiguous memory.
--
Tanel Poder
https://blog.tanelpoder.com/seminar/
On Tue, May 28, 2019 at 4:10 PM Noveljic Nenad <nenad.noveljic_at_vontobel.com<mailto:nenad.noveljic_at_vontobel.com>> wrote: 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
____________________________________________________
Please consider the environment before printing this e-mail.
Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
<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 />
This message is intended only for the individual named. It may contain confidential or privileged information. If you are not the named addressee you should in particular not disseminate, distribute, modify or copy this e-mail. Please notify the sender immediately by e-mail, if you have received this message by mistake and delete it from your system.<br /> Without prejudice to any contractual agreements between you and us which shall prevail in any case, we take it as your authorization to correspond with you by e-mail if you send us messages by e-mail. However, we reserve the right not to execute orders and instructions transmitted by e-mail at any time and without further explanation.<br /> E-mail transmission may not be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also processing of incoming e-mails cannot be guaranteed. All liability of Vontobel Holding Ltd. and any of its affiliates (hereinafter collectively referred to as "Vontobel Group") for any damages resulting from e-mail use is excluded. You are advised that urgent and time sensitive messages should not be sent by e-mail and if verification is required please request a printed version.</br> Please note that all e-mail communications to and from the Vontobel Group are subject to electronic storage and review by Vontobel Group. Unless stated to the contrary and without prejudice to any contractual agreements between you and Vontobel Group which shall prevail in any case, e-mail-communication is for informational purposes only and is not intended as an offer or solicitation for the purchase or sale of any financial instrument or as an official confirmation of any transaction.<br /> The legal basis for the processing of your personal data is the legitimate interest to develop a commercial relationship with you, as well as your consent to forward you commercial communications. You can exercise, at any time and under the terms established under current regulation, your rights. If you prefer not to receive any further communications, please contact your client relationship manager if you are a client of Vontobel Group or notify the sender. Please note for an exact reference to the affected group entity the corporate e-mail signature. For further information about data privacy at Vontobel Group please consult <a href="https://www.vontobel.com">www.vontobel.com</a>.<br />
</p> </body> </html>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jun 03 2019 - 10:05:16 CEST