-
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
-
What are the values you set for HASH_JOIN_MULTIBLOCK_IO_COUNT and HASH_AREA_SIZE in init.ora ?
Tamil
-
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 10:03 PM.
-
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
-
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?
-
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"
-
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?
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|