MSC_OPERATION_COMPONENTS Has Been Rapidly Bloated in R12 Only

(Doc ID 2109919.1)

Last updated on MAY 16, 2018

Applies to:

Oracle Advanced Supply Chain Planning - Version 12.1.3 and later
Information in this document applies to any platform.

Goal

In R12 ASCP environment, the data amount of MSC_OPERATION_COMPONENTS has been rapidly bloated. It does not occur in R11i.


Example - MSC_OPERATION_COMPONENTS Size:

Environment Segment Size Expected Size
R12#1 100MB 0.8MB
R12#2 1803MB 4MB
R11i 197MB 165MB

#Calculation method
  Segment Size = dba_segments.BYTES
  Expected Size = dba_tables.NUM_ROWS*AVG_ROW_LEN

It seems that when inserting to MSC_OPERATION_COMPONENTS in ODS load, the /*+ append */ hint has been used in R12(MSCLRTGB.pls MSC_CL_ROUTING_ODS_LOAD).
On the other hand, in R11i , it is not in use. It is a conventional Insert for MSC_OPERATION_COMPONENTS only.

However, according to following mentions, Direct-load INSERT requires more space than conventional path INSERT.

Oracle8 Concepts Release 8.0
 21.Direct-Load INSERT > Space Considerations

Direct-load INSERT requires more space than conventional path INSERT, because
direct-load INSERT ignores existing space in the free lists of the segment.
For parallel direct-load INSERT into nonpartitioned tables, free blocks below
the high water mark of the table segment are also ignored. Additional space
requirements must be considered before using direct-load INSERT.

Therefore, it needs to modify the cause of this rapid bloated in R12.
If the cause is the Direct-load INSERT, it has requested to add to the option to not to use it.

 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms