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

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?

Quote:

---

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" :rolleyes:

Quote:

---

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" :rolleyes:

---

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.

Quote:

---

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?

Quote:

---

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.

Quote:

---

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.

Hi again Reydp.

This issue was a one of. There have been no repeat incidents since last week and the process has been run several times.

No parameters changes have been made because of this.

I guess what I was really hoping for was some suggestions as to what *could* cause the process to suddenly run out of memory when it has never done so before or since.

Whilst I appreciate Tamil's response, I understand the message and what parameters could be changed to correct it if it were an on going problem. However if the problem can't be replicated (believe me I've tried) then the "solution" cannot be proven to have worked and the original suggested cause cannot be disproven.

I suspect I'll have to wait and see if there is another failure

Until next time....

Nick