Out Of Memory Process
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Out Of Memory Process

  1. #1
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Out Of Memory Process

    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.
    funky...

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

  2. #2
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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

  3. #3
    Join Date
    Mar 2002
    Posts
    534
    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:
    Code:
    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;

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    @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.
    funky...

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

  5. #5
    Join Date
    Jun 2004
    Posts
    2
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width