Re: FW: Error related to hash memory
- From: Jonathan Lewis <jlewisoracle@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 9 Dec 2021 10:22:10 +0000
Also Jonathan, I was trying to test the job in a less busy hour and
seeing the same plan with 'Hash group by " operation is using~4GB of
memory. So it's contradicting the thought of ~2GB memory limitation of the
"hash group by".
Good. That eliminates one possibility. (In fact, looking back at the
original Monitor report you sent, we can that one of the other hash
operations reported 4GB max memory - so that would have been a reasonable
indication that the 2GB wasn't a limit).
And also when I ran the same with 'sort group by' but parallel-8 and it
took almost the same time though with lesser memory consumption and temp
spill. Attached is the sql monitor for both.
I think the only significant thing you can take from this test (since you
changed the degree of parallelism) is the workload of the two lines
relating to the serial group by, which tell us:
Hash group by: 4GB max memory, 23GB max temp, 234seconds CPU, 50 seconds
I/O
Sort group by: 119M max memory, 9GB max temp, 276 seconds CPU, 113 seconds
I/O.
If the problem is something to do with memory consumption the extra CPU and
I/O time is a relatively small prive to pay for "protection"- and that was
with 3% extra data; but that doesn't tell you much about what the
difference would be if the group by and data load was running parallelat
the same DOP.
However, As we had also seen the query/job reruns without failure , so
does it mean it's really a memory crunch (even if we have 40% of the memory
left unutilized in the host during the issue period). Thus bumping
pga_aggregate_target should be the correct approach here?
At present we have no information that tells us it's the correct approach,
and nothing that tells us it's the wrong approach.
We do have Jared Still's comment that he has seen something of the sort
when the hash aggregation took up "all the temporary space".
I should point out, by the way, that I misread the original plan and said
the volume of data going into the hash aggregation was more than 1,000
times the estimated volume.
The number 1,000 came from comparing 168M with 127K - because I misread the
Cost column as the Rows column. The actual was really 168M times the
estimate.
Have you tried running with the tracing event set. You need to do this at
least once simply to see how large the trace file is.
Whatever else you do (if you stick with the hash join) you MIGHT then
decide that the event should ALWAYS be set for this statement so that if it
does crashin the say way in the future you will be able to see from the
trace file where it had got to before crashing.
Regards
Jonathan Lewis
Other related posts:
