Margin Analysis Load Run Performance Issue found in R12 install (Doc ID 1946689.1)

Last updated on DECEMBER 14, 2014

Applies to:

Oracle Cost Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Margin Analysis

When attempting to execute Margin Analysis Load Run under program CSTMRGLD module, the process was exceeding an hour.
Comparing to the previous R11i install this was just taking 10 - 15 minutes.
The expectation was to have this the same as in R11i with threshold expectation of 10 - 20 minutes.

Based from the tracefile tkprof , it was found to have performance degradation  as per below offending sql statements:

 UPDATE CST_MARGIN_SUMMARY TEMP SET TERRITORY_ID = (SELECT TERRITORY_ID FROM
  HZ_CUST_SITE_USES_ALL HCSUA
 WHERE
  NVL(HCSUA.ORG_ID, -999) = NVL(:B4 , NVL(HCSUA.ORG_ID, -999)) AND
  HCSUA.SITE_USE_ID = TEMP.SHIP_TO_SITE_USE_ID ) WHERE SHIP_TO_SITE_USE_ID IS
 .
  NOT NULL AND GL_DATE BETWEEN :B3 AND :B2 AND BUILD_ID = :B1
 .

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Run Margin Analysis Load  program module.
2. Review trace file and log file for performance efficiency.

Cause

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