In these recent days we are getting the error ORA-04030: out of process memory when trying to allocate 1008128 bytes (hash-join subh,kllcqas:kllsltba) very frequently. I did RTFM regarding this error many a times, but i still cant find out the root cause, as i always see the memory usage of the process oracle.exe never exceeded 1.5 GB.. When the memory limit on Win NT is 2GB why do i see this error??
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Do you got HASH_AREA_SIZE defined in initora ?
As default it's 2*SORT_AREA_SIZE.
If you do not have HASH_JOIN_ENABLED=FALSE, optimizer prefers it over NESTED LOOP or SORT-MERGE when joining tables. So hash join uses HASH_AREA_SIZE to store information from table 1 and if it's not enough, then Oracle uses TEMP-file. Then it's checking join condition values from table 2 against those stored values.
You may want to reduce/set the value of HASH_AREA_SIZE, so that your system is not out of memory.
Watch the virtual memory, not mem usage. I ran into this problem exp with xml transforms from temp swap. You can watch this via windows 2000 task manager under View - Select columns. I was using 1.7 gig of Vm and getting this error.
Added the /3GB /PAE /NOLOWMEM to the boot.ini and going to add another 4GB of memory to the machine.
Over all I am running 3 instances on this machine with oracle.exe stack at 1Meg, I've seen suggestions that you can run orastack and lower the memory requirements for the exe, agent and lsnr.
Also insuring all the code closes the result sets and free the number of cursors.
I rather despise ORA 4030 and 600 errors esp the mnemonics!
like (koh dur heap d,kghsseg: kolaslCreateCtx) wtf.