DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Resolving ORA-04030

  1. #1
    Join Date
    Jul 2000
    Posts
    521

    Resolving ORA-04030

    Hello All,

    Platform : HP-UX 11i (64-bit), Oracle 9.2.0.5

    A query is failing due to ORA-04030 (out of process memory for callheap). The query involves a FTS on a 18G table. The plan shows a "Hash Join (Outer)" with this large table and a very small one. And then another hash join with these results and records fetched from a table in a remote database.

    I watched the PGA_USED_MEM, PGA_ALLOC_MEM and PGA_MAX_MEM values for the SPID of the session that is running the query. What I noticed was when the PGA_USED_MEM becomes 1073741824 (i.e. 1GB), the process fails with ORA-04030 error.

    There is more than enough RAM available on the server. More than enough swap space.

    I could see it from HP's glancePlus tool also that the "RSS Siz" value became just a little over a GB for this process.

    What limit am I hitting ?

    On OS side, maxdsiz and mazdsiz_64bit are set to 4G. OS limits of the user running the query ('oracle' in this case) for data is also 4G.

    On database side, pga_aggregate_target is 600M, workarea_size_policy is AUTO.

    There is no database parameter that is set to 1GB.

    What limit am I hitting ?

    This is getting very interesting...
    svk

  2. #2
    Join Date
    Mar 2002
    Posts
    534
    The problem may be related to bug 3130972.
    http://metalink.oracle.com/metalink/...T&id=3130972.8

    What do you get when you execute the following query?

    select ksppinm, ksppstvl, ksppdesc
    from x$ksppi x, x$ksppcv y
    where x.indx = y.indx
    and (KSPPINM like '%smm%max_size%' or KSPPINM like '%pga%')

  3. #3
    Join Date
    Jul 2000
    Posts
    521
    O/P of the query you provided :

    KSPPINM KSPPSTVL KSPPDESC
    ---------------------------------------------------------------- -------------------- --------------------------------------------------
    pga_aggregate_target 314572800 Target size for the aggregate PGA memory consumed
    by the instanc

    _pga_max_size 209715200 Maximum size of the PGA memory for one process
    _smm_max_size 15360 maximum work area size in auto mode (serial)
    _smm_px_max_size 92160 maximum work area size in auto mode (global)


    So, I guess I was wrong in mentioning pga_aggregate_target value in original post. But, that was a value we had set and did not make a difference.

    Thanks.
    svk

  4. #4
    Join Date
    Oct 2002
    Posts
    807
    Did you check your ulimit for the OS user? Are they set to unlimited?

  5. #5
    Join Date
    Jul 2000
    Posts
    521
    O/P of the query you provided :

    KSPPINM KSPPSTVL KSPPDESC
    ---------------------------------------------------------------- -------------------- --------------------------------------------------
    pga_aggregate_target 314572800 Target size for the aggregate PGA memory consumed
    by the instanc

    _pga_max_size 209715200 Maximum size of the PGA memory for one process
    _smm_max_size 15360 maximum work area size in auto mode (serial)
    _smm_px_max_size 92160 maximum work area size in auto mode (global)


    So, I guess I was wrong in mentioning pga_aggregate_target value in original post. But, that was a value we had set and did not make a difference.

    Thanks.
    svk

  6. #6
    Join Date
    Jul 2000
    Posts
    521
    OS limits are :

    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) 4292870144
    stack(kbytes) 392192
    memory(kbytes) unlimited
    coredump(blocks) 4194303

    I guess that BUG may be the culprit. I'll wait to see what Oracle has to say...
    svk

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    data(kbytes) 4292870144
    stack(kbytes) 392192
    ==

    Set those 2 values to unlimited.
    If it does not work, then increase pga_aggregate_target and _pga_max_size to 2GB.

    Tamil

  8. #8
    Join Date
    Mar 2002
    Posts
    534
    your pga / work area size parmeter seems to be standart so they shouln't be the reason for that problem.

    But because you said that you got this error when you get close to the 1gb i'm alost sure that the reason for it is the bug 3130972. So I recommand you either to upgrade to 9.2.0.6 or to apply the one off patch.

    Increasing _pga_max_size will not solve the problem but, from my experience, rather increase the probability that you hit that bug. However once you applied the patch and if you often do large sorts/group by or large hash joins you may got a much better performance if you increase _pga_max_size to 1 or 2 GB.

  9. #9
    Join Date
    Jul 2000
    Posts
    521
    Thank you all.

    We have applied the one-off patch and the job that executes the query is currently running. I'll know the outcome in @ 90 minutes. I'll keep you posted.
    svk

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Try change the query to use Nested Loop.
    See what happens.

    Tamil

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