hash_join memory consumption problem.

[bkerr]

I am encountering problems running a piece of SQL for a datawarehouse transformation. I have adjusted the HASH_AREA_SIZE and SORT_AREA_SIZEs to improve performance, and all was going well. However, in the last couple of week the queries have been blowing up, kicking out an ORA-04030 out of process memory error.

When I monitor the sessions memory usage with

select sid,name,value/(1024*1024) as value, s.* from v$statname n,v$sesstat s where n.STATISTIC#
= s.STATISTIC# and name like 'session%memory%'
and sid = 10 order by 3 desc;

the memory is utilized as expected except when performing the final hash join after a series of hash joins. The memory usage rockets up until the process falls over.

Has anyone encountered this type of problem before. The query seems to far exceed the hash_area_size set which I thought was the limiting factor in the memory allocation.

Any help would be much appreciated,
Brendan

[bkerr]

Luckily, I have discovered why this problem was occurring, the indexes on the target table were all enabled and were trying to rebuild simultaneously at the same time the last hash join was feeding records to the table. Indexes have now been disabled and everything is OK.

Sorry to waste your time.
Brendan