Chinar Aliyev
2021-03-17 15:16:42 UTC
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
*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>
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
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
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)
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
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>
--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
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)
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
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>