ora-06580 Hash Join out of memory
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: ora-06580 Hash Join out of memory

  1. #1
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233

    ora-06580 Hash Join out of memory

    OS Win 2000 4Gb 2x2.4 Ghz
    DBMS 8.1.7.4.1 (Block Size 8192)
    SOT_AREA_SIZE = 1M
    Peoplesoft 7.60 Financials


    Hi Guys

    Our Peoplesoft application reported an ora-06580 error whilst running a batch job.

    The error description suggests there was a lack of memory space to store the row even though the rowsize in question wouldn't be more than about 200 bytes. Interesting that the message suggests altering HASH_JOIN_MULTIBLOCK_IO_COUNT even though Oracle recommends that we don't.

    Another DBA here suggests this was due to a "clash of transactions" although he couldn't back that up. OK the machine was pretty busy at the time but I can't see how "clashing transaction" would raise this error.

    Any thoughts (or even abuse for being so dumb) would be appreciated.

    Nick

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    What are the values you set for HASH_JOIN_MULTIBLOCK_IO_COUNT and HASH_AREA_SIZE in init.ora ?

    Tamil

  3. #3
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233
    Hi Tamil

    Thanks for the response

    IIRC (at home you see) both are default values.

    i.e. according to the doco, hash_area_size = 2 * sort_area_size therefore 2 Mb and hash_area_multiblock_io_count is system calculated (as per Oracle recommendation).

    Nick

    Woops typo, but then whats 1.8 Gb between friends
    Last edited by ngdh; 09-20-2004 at 11:03 PM.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    There is NO harm in increasing both values.

    I usually set hash_area_size to 64 MB and HASH_JOIN_MULTIBLOCK_IO_COUNT to 32.

    Tamil

  5. #5
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233
    Fair point Tamil,

    I guess I was more interested in any theories that would prove or disprove my colleagues assertion that a "clashing transaction" caused the error.

    AFAIK the hash area requirement would be allocated from process memory so it couldn't be affected by multiple processes running concurrently.

    Any thoughts?

  6. #6
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by ngdh
    Fair point Tamil,

    I guess I was more interested in any theories that would prove or disprove my colleagues assertion that a "clashing transaction" caused the error.

    AFAIK the hash area requirement would be allocated from process memory so it couldn't be affected by multiple processes running concurrently.

    Any thoughts?
    I believe the concern of this thread was already addressed by Tamil, it should be your colleague's turn to give supporting docs to support his theory about "clashing transaction"

  7. #7
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233
    Originally posted by reydp
    I believe the concern of this thread was already addressed by Tamil, it should be your colleague's turn to give supporting docs to support his theory about "clashing transaction"
    Well in a perfect world, yes. However when the colleague in question has been in situ for 13 years and I've been here 6 months it becomes an issue of my credibility not his. If I can't disprove it, it's my word against his! Now I'm not doing this to score points, just 'cos I want to try and get these instances to be a bit more stable.

    BTW the original concern was not the error but the cause. I suspect we feel the same on that. I believe hash area is allocated from the PGA which is defined on a per process basis so it doesn't sound likely that two concurrent processes could cause this error.

    So the question still stands: Given a hash area of 4Mb and maximum of 4000 rows at 200 byte row length, what could cause Oracle to run out of memory?

  8. #8
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    how many concurrent users do you have? Can you observe a swap/virtural memory, cpu utilization much exhaust. How much memory did you spent in your SGA? Is this the only running program/SQL in the server, when that problem arises? Still a lot of info that is yet to be given for us to figure out or at least give an idea as to where the problem is.

  9. #9
    Join Date
    May 2002
    Location
    Western Australia
    Posts
    233
    OK Reydp,

    Thanks for your response.

    At the time of the error we had 5 concurrent users.

    SGA

    Total System Global Area 1638811676 bytes
    Fixed Size 75804 bytes
    Variable Size 747274240 bytes
    Database Buffers 891289600 bytes
    Redo Buffers 172032 bytes

    Cpu usage hit a max of 30% during the period in question. There was no unusual memory paging.

    This incident has only happened once so there is no pattern to confirm.

    Apologies if I was too brief in my original information but since the hash area size refers to PGA for a process I didn't think SGA was patricularly relevant. Also since the error refers specifically to a process memory shortage I didn't feel CPU usage at the server level was poignant either.

  10. #10
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Originally posted by ngdh
    This incident has only happened once so there is no pattern to confirm.
    Does this means that with out touching the configuration/parameter file it just work fine?

    Originally posted by ngdh
    Apologies if I was too brief in my original information but since the hash area size refers to PGA for a process I didn't think SGA was patricularly relevant.
    yes you're right, but I only brought that up thinking that the problem was recurring. And just being curious of how do you manage your physical memory.

    Originally posted by ngdh
    Also since the error refers specifically to a process memory shortage I didn't feel CPU usage at the server level was poignant either.
    again you are right, and the only solution to your problem is what tamil have already told you. Haven't you change the value of
    HASH_MULTIBLOCK_IO_COUNT and HASH_AREA_SIZE and yet your problem just didn't reoccur again? I don't think so.

    But going back to your concern regarding the comment of your colleague about "clashing transaction", I believe this program/query was just use lately, and it will be easily accepted if the real occurence will just be brought up to contradict the "clashing transaction" theory. And that is letting them know that the server needs reconfiguration.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width