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.
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.
At the time of the error we had 5 concurrent users.
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.