Re: What is the hard PGA limit a single process can’t go beyond without spilling to disk

Hello Mohamed,

It might not be direct answer to your questions, but want to do some points:

*"So I tried to understand what is the pga hard limit a parallel (or serial) process can’t go beyond without spilling to TEMP"*

If I were you I would investigate which part of PGA(which heap) was consuming lots of memory (by looking into generated traces, obtaining PGA dump or if possible some info from dynamic perf views). It does not mean that if there is a limit(PGA) defined then the "DATA" has to be spilled to DISK. Not all "DATA" spilled to the TEMP. Therefore, to understand it, need to look at the PGA heaps. Since we don't have the SQL, execution plan and SQL stats (as well as DB version), but I believe (highly) that the most consumed PGA part is not related to HASH JOIN(hash area), SORT MERGE JOIN (sort area) or etc (so workareas). Otherwise it should have been spilled to the TEMP.
Thus, there may not have been (not necessarily) a limit for the data/info other than workareas. For example, SQL statements might require lots of PGA memory to parse and optimize them(not only memory is consumed from SGA but from PGA as well) and in this case you might reach the PGA limit. This kind of information is not able to dump to disk (TEMP) and there is no limit for them.

Best Regards

On Wed, Mar 17, 2021 at 2:00 PM Mohamed Houri <mohamed.houri_at_gmail.com> wrote:

> Hello
>
>
>
> A couple of months ago we hit the *ORA-04036*: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT during
>
> a parallel INSERT/SELECT with DOP 4
>
>
>
> The value of the PGA_AGGREGATE_LIMIT was initially set to 8GB
>
>
>
> Thanks to the *_pga_limit_dump_summary* set to TRUE a dump trace file has been generated where we can clearly see that each parallel slave of the parallel server set n°2
>
> consumes 2GB, reaching as such the 8GB limit.
>
>
>
> So, the DBA thought that the hard pga limit a parallel (or serial) process can’t go beyond without spilling to disk (TEMP) is 2G. He then increased the value of the
>
> PGA_AGGREGATE_LIMIT to 16GB
>
>
>
> But the same parallel insert/select with DOP=4 failed again with the same ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
>
> of 16GB
>
>
>
> This occurred, this time, because each of the 4 parallel slaves of server set 2 consumed up to 4GB of PGA memory without spilling to TEMP disk
>
>
>
> So I tried to understand what is the pga hard limit a parallel (or serial) process can’t go beyond without spilling to TEMP
>
>
>
> I ended up by finding the following formula:
>
>
> max pga memory = 64k*max_map_count
>
>
>
> max_map_count value comes from /proc/sys/vm/max_map_count
>
>
>
> in my case max_map_count = 262144
>
>
> max pga memory = 64k*262144 = 64*262144/power(1024,3) = 16GB
>
> The max_map_count normally should be equal to the realfree_heap_pagesize
> (65536)
>
> SQL> _at_pd realfree
>
> Show all parameters and session values from x$ksppi/x$ksppcv...
>
>
>
> NAME VALUE DESCRIPTION
>
> ---------------------------- ------- ---------------------------------------------
>
> _realfree_heap_max_size 32768 minimum max total heap size, in Kbytes
>
> _realfree_heap_pagesize 65536 hint for real-free page size in bytes
>
> _realfree_pq_heap_pagesize 65536 hint for pq real-free page size in bytes
>
> _realfree_heap_mode 0 mode flags for real-free heap
>
> _use_realfree_heap TRUE use real-free based allocator for PGA memory
>
>
>
> In which case the max pga memory (per process) would have been equal to 4GB according to the above formula
>
>
>
> So my questions are:
>
>
>
> 1. *is the above formula reliable to get the maximum of pga memory per process (serial or parallel)?*
>
> 2. *What this max_map_count parameter stands for? Should it really be equal to realfree_heap_pagesize?*
>
>
>
> Thanks.
>
>
> --
>
> Houri Mohamed
>
> Oracle DBA-Developer-Performance & Tuning
>
> Visit My - Blog <http://www.hourim.wordpress.com/>
>
> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>
> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
> <https://twitter.com/MohamedHouri>
>
>

-- 
*Chinar Aliyev*


Visit My         :Blog <http://chinaraliyev.wordpress.com/>
Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <https://www.linkedin.com/in/chinaraliyev/>*

My Twitter <https://twitter.com/MohamedHouri>      - ChinarAliyev
<https://twitter.com/ChinarAliyev>

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