Out Of Memory Process

[abhaysk]

Hi Guys..

Oracle Version : 9.2.0.4
OS : Win NT

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??

Rgds
Abhay.

[reydp]

Hi Abhay,

see this note: 116076.1 in metalink

or quoted from Metalink notes:

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.

regards
reydp

[mike9]

Using 9.2.0.4 I guess that you have set WORKAREA_SIZE_POLICY = Auto which means that the size of HASH_AREA and SORT_AREA will have no impact.

Which is the size of PGA_AGGREGATE_TARGET?
What kind of application is it OLTP or DWH?
Could you post the output of the following query:

select case 
       when low_optimal_size < 1024*1024 
       then to_char(low_optimal_size/1024,'999999') || 'kb <= PGA < ' || 
            (HIGH_OPTIMAL_SIZE+1)/1024|| 'kb' 
       else to_char(low_optimal_size/1024/1024,'999999') || 'mb <= PGA < ' || 
            (high_optimal_size+1)/1024/1024|| 'mb' 
       end pga_size, 
       optimal_executions, 
       onepass_executions, 
       multipasses_executions 
  from v$sql_workarea_histogram 
 where total_executions <> 0 
order by low_optimal_size;

[abhaysk]

@reydp

yes hash is enabled and is 16M sort area is 5M

@mike

we still have not gone to workarea auto.. i have suggested this many a times but i dunno why our colleagues dont wana go with it..

but this time, we are going for it..


Rgds
Abhay.

[JohnDough]

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.