I am wondering what is the maximum setting for hash_area_size parameter? I'm trying to perform a huge join which ends up allocating 10Gb of temp space and completes in an hour. 10Gb is not that much by today's standards. I have the RAM available in the system. Should be possible to process that in memory. Is there a way to tell oracle to do that? When I try to set hash_area_size to 10Gb it complains because it seems to be limited to 32bit integers.
I've also tried alter session set "_smm_max_size" = 10000000; -- 8 zeros
because I read that it is in Kb, but it doesn't seem to make a difference.
UPDATE: 11g Enterprise Edition Release 184.108.40.206.0 - 64bit The query is a join of 3 wide tables of equal size by 4 columns. Each column has about 10M rows. No indexes. The plan is 2 HASH JOINs. Hash joins are performed in TEMP space.