ORA-32690: insufficient hash infrastructure memory

ORA-32690: insufficient hash infrastructure memory [Oracle @ sdw0 ~] $ Oerrora3269032690, 00000, HashTableInfrastructureranoutofme

ORA-32690: insufficient hash infrastructure memory [Oracle @ sdw0 ~] $ Oerr ora 32690 32690,000 00, Hash Table Infrastructure ran out of me

ORA-32690: insufficient hash infrastructure memory

[Oracle @ sdw0 ~] $ Oerr ora 1, 32690
32690,000 00, "Hash Table Infrastructure ran out of memory"
// * Cause: Not enough memory.
// * Action: Increase memory.

This is a known bug on database version 10.2.0.3. The bug is fixed in 11.2 release.

Recommended solution always to be on the latest patchset or even 11.1.0.6 where there is one off backport for several platforms.

The bug also has a one off on top of 10.2.0.4, if you are not able to be on top of 10.2.0.4 for the time being, so our option now is to resolve the issue on top of 10.2.0.3

No one off on top of 10.2.0.3 for Solaris platform however the good news that there is a merge Patch 6907160 merge label request on top of 10.2.0.3 for bugs 6471770 6471515, available to Solaris platform.

There are also the available workarounds
1. Disable Hash group-by setting "_ gby_hash_aggregation_enabled" to FALSE in init. ora
2. Use a NO_USE_HASH_AGGREGATION hint in your statements

Cause analysis:

Solution:

1. Increase the pga_aggregate_target size of PGA.

2. Disable hash group by operations bysetting the parameter _ gby_hash_aggregation_enabled to FALSE:

SQL> alter session set "_ gby_hash_aggregation_enabled" = false;

Or

SQL> alter systemset "_ gby_hash_aggregation_enabled" = falsescope = spfile;

A hard parse tothe statement needs to be shortmed, preferably to flush the Shared Pool aftersetting this workaround and then re-run the statement.

-- To make the modification take effect, you need to execute a hard resolution, so you can select flush share pool and then execute SQL.

3. Do not use hash join: select/* + NO_USE_HASH_AGGREGATION */.....