The error message is
ORA-32690: Hash Table Infrastructure ran out of memory
Two things to note about the wording: l
It's NOT a message saying something about being unable to allocate PGA
memory
It's not a message about a hash JOIN, it's about a hash TABLE.
I do not know if I'm reading too much into the words, and I don't know
exactly how Oracle handles hash aggregation so I won't be able to answer
anyt questions about the detailed mechanisms that might be causing this
error to appear; but I do know something about how hash joins work and, in
particular, how the code handles the in-memory BUILD TABLE component if it
won't fit in memory.
If you look at the Estimated and Actual rows for the hash aggregation you
can see that the estimate is out by a factor of over 1,000. For hash joins
Oracle sets up an structure for the build table that anticipates splitting
it into partitions that can spill to disc if the volume of data is too
large to handle in memory (hence one-pass and multipass hash joins). It's
likely that Oracle does something similar for hash aggregation but doesn't
allow for the estimate being out by a factor of 1,000 and the enormous
discrepancy might have resulted (for example) in Oracle being able to keep
track of data that it had to dump to disc.
I can see two options (apart from the obvious "set the parameter according
to the workaround suggested" so that the aggregation is a sort aggregate).
The first is simply to find out why the estimate of rows is so bad and
allow Oracle to work out a better estimate. Since there's a non-mergeable
view in the query (operation 20) you may be able to inject a cardinality
(or, to be fussy, an opt_estimate hint) that simply says "there will be
168000000 rows coming out of this query block". That might allow Oracle to
create a suitable internal structure to handle the data volume correctly.
However, I note that the MAX MEM for the hash aggregation is 2GB and that
may be a hard limit (assuming it is an accurate report rather than a
round-up from something much less), so option 2 is to reduce the absolute
memory requirement of the hash aggregation.
Option 2, reducing the memory requirement: the plan is going serial for
the hash aggregation and it would be good to find out why. If it runs
parallel the problem may disappear anyway. However I note that there appear
to be 8 scalar subqueries in the select list; (operations 2 - 18), and I
think they are growing the row length BEFORE the aggregation takes place.
If some of these scalar subqueries can be made to operate AFTER the hash
aggregation then you could be aggregating a much smaller volume of data and
the memory requirment and the need to track a large volume spilled to disc
could be reduced to a level that bypasses the error.
I happen to have published a note only yesterday with an example that
rewrote a query to do a sort before collecting scalar subquery column
values: https://jonathanlewis.wordpress.com/2021/12/01/best-practice/ At
the end of it I pointed out that generally "you don't want to do this kind
of thing unless it's for very special circumstances" - but you may have
exactly those circumstances. Another reason for considering this strategy
is that maybe there's something about one (or more) of your scalar
subqueries that forces you hash aggregation to serialis - e.g. do you call
a pl/sql function that is not parallel enabled - and moving one or more
scalar subqueries out of the hash aggregation may result in parallel
execution taking place.
(As others hav pointed out - you're doing a serlal insert anyway, so maybe
a simple "enable parallel dml" would bypass the issue anyway.)
(On a side note: I am curious about the operations 4 - 11 of your plan.
The code to generate the plan "depth" has some errors in 11g, so I'd like
to know whether that part of the plan represents a single scalar subquery
with a couple of cascading subqueries, or a scalar subquery which uses
decode()/case with further scalar subqueries embedded in the decode()/case,
or has some other structure.)
Regards
Jonathan Lewis
