1

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.

Improve this question
8
  • docs.oracle.com/cd/E14072_01/server.112/e10820/… - how is your PGA sized?
    – Mat
    Commented Mar 16, 2013 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
    Commented Mar 16, 2013 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
    Commented Mar 16, 2013 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
    Commented Mar 17, 2013 at 0:53
  • What parallel degree do you have? "No indexes" - is that because they would only be useful for this?
    – Mat
    Commented Mar 17, 2013 at 8:44

1 Answer 1

Reset to default
2

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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