-
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.
-
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.
-
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
-
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
-
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.
-
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
-
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.
-
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.
-
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
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|