Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> DB crashed with ORA-4030 on DBW0 process

DB crashed with ORA-4030 on DBW0 process

From: Steve Perry <sperry_at_sprynet.com>
Date: Mon, 17 Mar 2003 18:48:40 -0800
Message-ID: <F001.0056C23D.20030317184840@fatcity.com>


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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Steve Perry
  INET: sperry_at_sprynet.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Mar 17 2003 - 20:48:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US