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

Thread: ora-4030 - Urgent

  1. #1
    Join Date
    Jan 2001
    Posts
    642

    ora-4030 - Urgent

    Hi,

    I have a batch job in which one of the sql will do insert operation reading from a table which has 5+ Million records.

    We have a 314M shared_pool and 100M sort_area. I am getting this error on the first sql, however the rest of the insert statements (reading data from even bigger table go thr' well).

    I am trying to understand this problem and fix: Can anyone throw some light on this.

    Badrinath
    3:05:19 SQL> insert into Remap_Product_new
    13:05:19 2 select /*+ USE_HASH(b r) */ distinct
    13:05:19 3 b.francode,
    13:05:19 4 null,
    13:05:19 5 b.francode || '-' || r.new_globalpartnum,
    13:05:19 6 'GL',
    13:05:19 7 '9999',
    13:05:19 8 trunc(sysdate),
    13:05:19 9 null
    13:05:19 10 from
    13:05:19 11 bookings b,
    13:05:19 12 remap_product_history r
    13:05:19 13 where
    13:05:19 14 b.regioncode = r.current_region
    13:05:20 15 and b.francode = r.current_francode
    13:05:20 16 and b.globalpartnum = r.current_francode || '-' || r.current_globalpartnum
    13:05:20 17 and trim(b.manfpartnum) = r.current_manfpartnum
    13:05:20 18 and r.rejectflag is null
    13:05:20 19 and (b.francode, b.francode || '-' || r.new_globalpartnum)
    13:05:20 20 not in (select francode, globalpartnum
    13:05:20 21 from products
    13:05:20 22 where replacedflag is null);
    b.regioncode = r.current_region
    *
    ERROR at line 14:
    ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key)


    Bookings: 5.5Million rows
    remap: 40-50 rows.
    There is always a better way to do the things.

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Hi Again,

    ERROR at line 14:
    ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key)

    Some of the documents suggest to increase the sort area and some suggest to decrease the sort area. I am not sure which is correct. I see a very little 0.01% of disk sorts compared to the memory sorts.

    I already have 100M sort area, which seemed to be high but looking at disk sorts, does it mean even this sort area size is small?

    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    First it's very good that you took a higher sort_area_size!
    the problem is that oracle does not allocates the sort_area_size at once but piece by piece.
    Your memory is not enough!
    so either you save memory by decreasng sort_area_size.
    or decreasing probably shared_pool_size
    And if you use PARALLEL query then you have to multiply the memory-need per paralle-Query-process

    Orca

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Originally posted by badrinathn
    Hi Again,


    Some of the documents suggest to increase the sort area and some suggest to decrease the sort area. I am not sure which is correct. I see a very little 0.01% of disk sorts compared to the memory sorts.

    Badrinath
    Sorting in memory is almost 100-1000 times faster than with extensive disk-usage!
    Orca

  5. #5
    Join Date
    Jan 2001
    Posts
    642
    Thanks,

    When you said 'memory is not enough', which memory are you talking?
    Is it system memory or SGA or Sort Area?

    How does decreasing the Sort Area or SGA help?


    Badrinath
    There is always a better way to do the things.

  6. #6
    Join Date
    Aug 2001
    Posts
    267
    Anyway your sql statement doesn't use temp space or sort_are_size since you are not using order by in your SQL . You got to increase shared pool size to avoid your problem.
    Raghu

  7. #7
    Join Date
    Jan 2001
    Posts
    642
    Hi,

    Here is a controversy!!

    I have 200MB of free memory on my shared pool. How does increasing the shared pool help?

    Also my understanding is using 'distinct' would use the sort area.

    Comments please.

    Badrinath
    There is always a better way to do the things.

  8. #8
    Join Date
    Jan 2001
    Posts
    642
    Hi,
    My total SGA is 852559116
    Fixed size 70k
    variable size ~320M
    db buffers ~520M
    Redo buffer ~ 1M

    I am running this batch program from the server where the database is running. Having a sort area of 100 MB on this set up- Is this the cause for ora-4030?

    Badrinath
    There is always a better way to do the things.

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    raghud

    hash join uses hash area which means PGA, it also uses TEMP tablespace, also distinct == group by so sort is used, so basically we use

    hash join
    sort

    4030 is related to PGA, the server process cannot get memory from OS, it can be that the server is exhausted of resources or there is a limit memory per process

  10. #10
    Join Date
    Aug 2001
    Posts
    267
    got it Pando..
    Raghu

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