Re: DB crashed with ORA-4030 on DBW0 process

Steve Perry wrote:
>
> Our database crashed over the weekend with the following lines from the
> trace.
>
> ORA-00449: background process 'DBW0' unexpectedly terminated with error 4030
> ORA-04030: out of process memory when trying to allocate bytes (,)
> ORA-04030: out of process memory when trying to allocate 8512 bytes (pga
> heap,ksm stack)
> The statement was insert into "/BIC/EZPCA_C03" ... select ... from ...
> the table "/BIC/EZPCA_C03" has 73 partitions and 13 bitmap indexes.
>
> I found this on metalink (bug 1428288) and it looks like it abended due to a
> combination of large sort_area_size, DML against a partitioned table with
> abnormally high number of bitmap indexes on it. Oracle will defer index
> maintenance, for bitmap indexes, until the dml operation is complete. It
> buffers the index entries in the session's PGA memory and the formula for
> memory usage is sort_area_size x #of indexes x #of partitions.
>
> According to that, then I need 19,901,972,480 bytes for the job to run. ( 20
> meg (sort_area_size * 73 partitions * 13 bitmap indexes) . I'm sure I
> missed something.
> This formula won't be used for "insert into.. values ( )", but will for
> "insert into ... select * from ...". Is this correct? Any other conditions
> that would cause this formula to be used.
>
> The system is an SAP BW on 8.1.7.4.1/Win2K
>
> Can somebody add to this?
>
> Thanks,
> Steve

I can't comment on the formula, although the argument for it seems reasonable although perhaps its more an upper bound. In any event, could a workaround be to do the work 1 partition at a time.

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"