Oracle DBA FAQ

Oracle RAC interview questions, Oracle Dataguard, ASM, CRS, Oracle wait events, Performance Tuning


    ORA-04030: out of process memory when trying to allocate 1536280 bytes (PLS non-lib hp,DARWIN)

    Share

    Thomas
    Guest

    ORA-04030: out of process memory when trying to allocate 1536280 bytes (PLS non-lib hp,DARWIN)

    Post  Thomas on Thu Feb 18, 2010 12:56 am

    Review of heap dump trace file Shows excessive growth of PGA components like "PLS non-lib hp" , "koh-kghu call" ( pmuccst: adt/re).

    Cause:
    Bulk insert in PLSQL can consume a large amount of PGA memory which can lead to ORA-4030 errors.
    A heapdump will show lot of free memory in the free lists which is not used but instead fresh allocations are made.

    if heapdump shows that "pl/sql vc2" "pmucalm coll" "pmuccst: adt/re" areas are majority of allocations, then it will be necessary to review applicable pl/sql code.


    Workaround:
    Chunk the FORALL loop. Do a hybrid of FOR & FORALL so that the bulk_rowcount arrays doesnt grow abnormally large.


    Useful SQL for monitoring

    To shows top PGA user..

    Code:
    select pid,spid,substr(username,1,20)
    "USER" ,program,PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,
    PGA_MAX_MEM from v$process where pga_alloc_mem=
    (select max(pga_alloc_mem) from v$process where program not like '%LGWR%');

      Current date/time is Fri Jul 21, 2017 4:36 am