ORA-06580: Hash Join ran out of memory while keeping large rows in memory

Products

• Data Loss Prevention Oracle Standard Edition 2

Issue/Introduction

When running URT we see the following messages ORA-06580: Hash Join ran out of memory while keeping large rows in memory

ORA-06580: Hash Join ran out of memory while keeping large rows in memory

Environment

Any version of Oracle

Cause

Setting PGA memory management to Manual mode will lead to "ORA-06580: Hash Join ran out of memory while keeping large rows in memory."

Additionally, the parameter WORKAREA_SIZE_POLICY will be set to "manual" and PGA_AGGREGATE_TARGET=0.

Resolution

Above Oracle 9i, avoid manual PGA management and use Automatic mode.
 
Steps:
 
Set the parameters by logging in as SYSDBA and editing as follows:

1. sqlplus /nolog
2. conn sys as sysdba;
3. alter system set PGA_AGGREGATE_TARGET=<see note below> scope=BOTH;
4. alter system set WORKAREA_SIZE_POLICY=AUTO scope=BOTH;
5. exit;
6. Re-run URT

 
NOTE: PGA_AGGREGATE_TARGET=300M - or any other bigger value as determined by DBA
 
 

Additional Information

Setting scope to BOTH will edit the spfile as well as the current instance.