You might want to test the following on a backup system, but the "new"
tracing/debug mechanism in 11g allows all sorts of interesting variations.
It is possible to set a all sorts of events at the system level but
restrict them in many ways - in particular you can set events to fire ONLY
for a given SQL_ID, not matter who executes it. I've shown examples on my
blog of enabling 10046 (as a named event) system wide whenever a particulr
statement runs, but in your case I think the following should work:
alter system set events 'kg_event[32699][SQL:0hj564hmb98qc] level 32768';
You'll have to substitute the SQL_ID of the interesting statement in the
[SQL:xxxxxxxxxxxxx] clause.
For this event number level 32768 looks as if it might be the "everyrthing"
level. To disable the event after you've got a couple of traces I think
you'll have to run the command again but using level 0.
Regards
Jonathan Lewis
On Fri, 10 Dec 2021 at 09:46, Lok P <loknath.73@xxxxxxxxx> wrote:
Thank You So Much Jonathan.
As this event has to be set at session level rather than system level and
this query is inside a procedure with a global temporary table used in it
and is getting called by the application layer. And We may not be able to
hit the same process from the application multiple times to reproduce the
error. So we are facing a bit of a challenge. Don't see other options but
maybe we will have to set the session level event trace(32699) and somehow
execute the procedure manually multiple times to hit the error and capture
the trace then.
On Thu, Dec 9, 2021 at 3:52 PM Jonathan Lewis <jlewisoracle@xxxxxxxxx>
wrote:
seeing the same plan with 'Hash group by " operation is using~4GB ofAlso Jonathan, I was trying to test the job in a less busy hour and
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).
it took almost the same time though with lesser memory consumption and tempAnd also when I ran the same with 'sort group by' but parallel-8 and
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.
does it mean it's really a memory crunch (even if we have 40% of the memoryHowever, As we had also seen the query/job reruns without failure , so
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
