RDBMS -- ORA-04030: out of process memory when trying to allocate X bytes (QERGH hash-agg,qeshHDAlloc.3

This one is for giving you some insights on an ORA-04030 error encountered while executing an expensive query that had lots of hash group by operations in its execution plan..

ORA-04030: 67108888 bytes... (QERGH hash-agg, qeshHDAlloc.3)

This seems like a PGA memory allocation problem, but the problem is actually in the SQL. (supposing your PGA memory is sized properly) 

Tuning the problematic SQL and make it use less memory can be the cure..

If that's not possible, setting the following underscore parameter can be considered...

alter session set "_gby_hash_aggregation_enabled" = false;

Note that, If tthis parameter is set to FALSE (default TRUE) , then the HASH GROUP BY operations are disabled. (setting it in the session level, disables the hash group by operation on the session level of course..)

So, when you set this in the session level, you will see a direct change in the execution plan, and that may be a quick workaround for you..

Before:

After:

We have one more option to be a workaround for this issue. That is, setting the _pga_max_size in the session level, to a lower value. 

By setting the _PGA_MAX_SIZE to a smaller value , we actually internally force Oracle 's hash-join to use the less memory.. (yeah.. there are bugs in these area..)

Ref:

ORA-4030: Out Of Process Memory (QERHJ hash-joi,QERHJ list array) Raised When Using _PGA_MAX_SIZE (Doc ID 1471103.1)

But! as you see, but of the wokarounds are based on underscore parameters, so it is better to get approval from Oracle Support before using these workarounds..