FW: Error related to hash memory

all the stuff JL mentioned, plus a little bit that didn’t make it to oracle-l before, probably because my remember to snip circuit is failing:  

Things I would try:  

1. force parallel local for the query
2. if 1) alone fails, do 1) and on the instance of choice run a scan of a non-indexed column of key tables of the query SIMPLY on the instance of choice
3. if 1-2) fails, try a prelude of enumerating the disjoint partition enumerators from “43 | PARTITION RANGE ITERATOR “ and generate the union all query of the separate partitions individually, thus making each hash memory set requirement smaller, doing these serially, rather than in parallel (which might not be possible in 11 anyway) because your problem is memory, not elapsed time. IF a single one of your partitions is nearly everything, it still will probably go splat, but if they are relatively flat each one being significantly smaller might avoid the error. IF figuring out the enumerated list of partition predicates is fast, this also has a *chance* to make everything faster, especially if the CBO finds a better plan for a single partition at a time but also because each hash area creation and probe might be a fraction of the current single one. IF you have time to experiment, this *might* be a consistent winner on both speed and footprint, but your mileage may vary.
4. forcing a sort group by *might* also work and would be easier for most folks to code up.

From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Thursday, December 02, 2021 10:04 AM To: 'loknath.73_at_gmail.com'; 'Pap'
Cc: 'Oracle L'
Subject: RE: Error related to hash memory  

Things I would try:

<snip>

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