Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 11.2.0.3.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.

share|improve this question
    
docs.oracle.com/cd/E14072_01/server.112/e10820/… - how is your PGA sized? –  Mat Mar 16 '13 at 7:55
    
Right, I need to set sort_area_size instead. But I can't set it to >32bit either. Not sure I understand your question about PGA. I think work area policy is set to auto and the max size for PGA is around 60Gb. –  MK01 Mar 16 '13 at 19:20
    
No, you don't need to and shouldn't set either (assuming you're not running an old version). Please recheck the docs and your parameters, and tell use what you actually have. As for setting hidden parameters, that's usually a bad idea. Posting your actual query and explain plan might also help if you want advice. –  Mat Mar 16 '13 at 22:57
    
@Mat I updated the question with as many details as I can right now. I am not sure what you mean by "don't need and shouldn't set either". It is running with default conservative settings which limit the work area used for hashing. I want to do a crazy override which makes sense to my particular situation (close to full control of a node and need to perform ETL quickly). –  MK01 Mar 17 '13 at 0:53
    
What parallel degree do you have? "No indexes" - is that because they would only be useful for this? –  Mat Mar 17 '13 at 8:44
show 3 more comments

1 Answer

If you have the Partitioning Option then consider hash partitioning the two tables into 32, 64, or 128 etc partitions on the join key. Whether you use parallel or serial query, your memory consumption will be very much reduced.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.