Diagnostic event 10104

This event dumps hash join statistics to trace files.

It is somewhat diffiult to get statistics about hash joins with Oracle. However, to investigate a particular query regarding hash joins, this event can be set prior to running the query.

In the trace file, find these two lines: Memory after hash table overhead and Estimated input size. If the latter is larger than the former, then the hash area is (according to Oracle's estimates) going to be too small.
Of course, if the statistics are of poor quality, or Oracle's estimates about the number of rows coming from the first table are wrong otherwise, then the estimated input size will be equally wrong.

Also, find Total number of partitions and Number of partitions that fit in Memory. If the number that fits in memory is smaller than the total number, then hash_area_size must be multiplied by total number / number that fits.