oracle 尝试分配内存,ORA-04030: 在尝试分配 8388108 字节 时进程内存不足 求解决办法。...

Planning Data Pull Worker Fails With Ora-04030 [ID 1268629.1]

--------------------------------------------------------------------------------

Modified 11-MAR-2011     Type PROBLEM     Status PUBLISHED

In this Document

Symptoms

Changes

Cause

Solution

References

--------------------------------------------------------------------------------

Applies to:

Oracle Advanced Supply Chain Planning - Version: 11.5.10.2 and later   [Release: 11.5 and later ]

Information in this document applies to any platform.

MSCPDPW

ORA-04030

Symptoms

Planning Data Pull Worker completes with error when running a full data collection.

ERROR

=======

Procedure LOAD_HARD_RESERVATION started.

Error pulling data from MRP_AP_HARD_RESERVATIONS_V to MSC_ST_RESERVATIONS.

ORA-04030: out of process memory when trying to allocate 16776728 bytes (QERHJ hash-joi,QERHJ Hash Table Entries)

Changes

Installed

Patch 8671960 - APS COLLECTIONS RUP#32 PATCH FOR 11.5.10 BRANCH

Cause

The problem of Planning Data Pull Procedure LOAD_HARD_RESERVATION failing with

the message:

Error pulling data from MRP_AP_HARD_RESERVATIONS_V to MSC_ST_RESERVATIONS

ORA-04030: out of process memory when trying to allocate 16776728 bytes (QERHJ hash-joi,QERHJ Hash Table Entries)

is known to have been caused by inadequate CBO statistics because the SQL

select

OWNER

, TABLE_NAME

, NUM_ROWS

, SAMPLE_SIZE

, LAST_ANALYZED

FROM

ALL_TABLES

where

TABLE_NAME in -- Tables referenced by MRP_AP_HARD_RESERVATIONS_V

('MSC_ST_RESERVATIONS'

, 'PJM_PROJECT_PARAMETERS'

, 'OE_ODR_LINES_SN'

, 'MTL_SYS_ITEMS_SN'

, 'MTL_RESERVATIONS_SN');

returned null values for the NUM_ROWS, SAMPLE_SIZE, and LAST_ANALYZED columns

for the three snapshots.

Stats had not been gathered for those snapshots since customer's application of

Patch 8671960 - APS COLLECTIONS RUP#32 PATCH FOR 11.5.10 BRANCH .

In the patch, the collections snapshots were moved from the original ERP schemas like INV, MRP, and ONT to the APPS schema. The customer's existing automated stats gathering process had not been updated to track this schema move.

The lack of stats misleads the CBO. The database server then creates an execution plan that tries to use unreasonable amounts of memory, leading to the ORA-04030.

Solution

Regardless of your version, be sure that stats are regularly gathered on the APPS schema.

References

NOTE:353411.1 - Out of Process Memory Planning ODS Load Worker Results in ORA-04030 Error

Related

--------------------------------------------------------------------------------

Products

--------------------------------------------------------------------------------

•More Applications > Value Chain Planning > Supply Chain Planning > Oracle Advanced Supply Chain Planning

Keywords

--------------------------------------------------------------------------------

MSC_ST_RESERVATIONS; MRP_AP_HARD_RESERVATIONS_V; MSCPDPW; CBO; STATS; SNAPSHOT; PLANNING DATA PULL

Errors

--------------------------------------------------------------------------------

ORA-4030