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.

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

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
:cool:

Quote:

---

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:cool:

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

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.

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

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

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

got it Pando..

Thanks Pando;

'4010 is related to PGA'.

I am connecting from the server directly and think it makes a dedicated server connection in this case. I have a plenty of memory unused but still get this error for only one query while the others with more number of record(and so sorts) go through well.

Will increasing the ram help or decreasing the Sort_area_size help. If decreasing sort_area_size can help then why?

Please clarify.

Badrinath

Windows OS? What is the memory usage of Oracle.exe?

Yes It's Windows: Oracle.exe is 572 MB.

One question: How can the Oracle.exe be 572 while the the SGA is ~800MB.

Badrinath

I guess that part of it is paged out.

I have 2GB of Physical memory and 4 GB of VM, even if it paged out, isn't the memory available?

Thanks,
Badrinath

Hi,

How did you fix your problem?

I am also getting same ORA error.

Pl. share...

Thanks,

Sorry! I didn't post back the reply.

I decreased the sort area size to 50M and the SGA size (as I had a lot of unused shared pool)( It's still high and will reduce it after few more testing). I also monitored the memory usage and was at time shocked to see the memory usage going as close as 2GB (the limit for oracle as I have not made any changes to the NT boot.ini file to extend the memory).

Badrinath