Standard Cost Update Performance

Oracle ERP 11.5.8,The Standard Cost Update Report is running for long time for different organizations.

[@more@]

Applies to:

Oracle Cost Management - Version: 11.5.10
This problem can occur on any platform.

Symptoms

In Oracle Cost Management 11.5.10, Standard Cost Update program takes a long time to complete.

Cause

Following are the scripts in the Standard cost update program which are expensive:

This can be found out by checking the TKPROF file of the Standard cost update:

1) insert into cst_std_cost_adj_values
2) insert into cst_std_cost_adj_temp
3) insert into WIP_SCRAP_VALUES

Solution

For Standard cost update performance issues on 11.5.10 :

These are the pre-requisite or Pro-active actions which needs to be done before any standard cost update is run:

1. Purge the cost types that you do not need.

2. Delete the negative cost_type_id from the cst_item_costs and cst_item_cost_details tables. The negative cost_type_id is created whenever a cost update is aborted, so you may want to periodically check for negative cost_type_id records and delete them.

The following Scripts can be used to identify the same :
Select * from Cst_item_costs where cost_type_id <0
Select * from Cst_item_cost_details where cost_type_id <0

3. Periodically purge the cost update history. This clears out some of the tables and benefits the performance. Obtain a count on tables where the trace file indicates a performance issue. Performance issues arise when tables have large amounts of records.

4. Check the count of rows contained in the following tables. If the number of rows in Cst_item_costs and Cst_item_cost_details table are huge, then it is one of the most probable reasons that standard cost update program takes more time to complete.

5. Truncate temporary tables, i.e., cst_std_cost_adj_temp. These tables grow if cost update is run with the CST debug profile set to Full.

The following tables can be truncated for performance. Be sure there are no cost processes running when truncating these tables:

CST_LOW_LEVEL_CODES
CST_STD_COST_ADJ_TEMP
BOM_LISTS
CST_ROLLUP_DELETE_TEMP
BOM_SMALL_EXPL_TEMP
CST_LISTS
BOM_EXPLOSION_TEMP
CST_EXPLOSION_TEMP

6. Verify that you have the latest versions of the following files:
Aall files are found at the following path: $BOM_TOP/bin/CMCICU
cmcicu.opp
cmlccu.ppc
cmlicu.ppc
cmliru.ppc
cmlutl.ppc
cmlwcu.ppc
CSTPUPDB.pls
CSTPUPDS.pls
cmlwru.ppc


7. For 11i, Run Gather Schema Stats for the CST_ITEM_COSTS and CST_ITEM_COST_DETAILS tables.
==================================

After completing the above tasks, Search for the patches available. Following is the indicative list of patches available. The patches contained in this note contain the highest version patches available to date. Any new patches released after this needs to be found out through MetaLink by searching for the corresponding files.

On 11.5.10 the following patches are available.

Base version of cmlicu.ppc in $BOM_TOP/bin/CMCICU should be 110.26. Patch 3208793


Patch 5520835
File version upgraded...
1) $BOM_TOP/src/cmcicu.opc 115.26.115100.10
2) $BOM_TOP/src/cmlwru.lpc 115.32.115100.13
3) $BOM_TOP/patch/115/import/US/cst5520835.ldt 115.0.115100.3


Patch 5606455 and and Patch 5649449
File version upgraded...
$BOM_TOP/patch/115/sql/CSTVIVTB.pls 115.25.115100.16
$BOM_TOP/src/cmliru.lpc 115.23.115100.11

Patch 5931035 ( This patch can be specifically used for standard cost adjustments for intransit quantities)

File version upgraded...
$BOM_TOP/src/cmlicu.lpc 115.34.11510.11

Patch 6088203
File version upgraded....
highest version of cstcinv.odf 115.56.115100.4


This will improve the performance of standard cost update program overall..

++++++++++++++++++++++++++++++++++++++++

For In Coin performance on 11.5.10.2, the following patches can be applied.
Patch 5329676
File version upgraded....
1) $BOM_TOP/patch/115/sql/CSTPUMEB.pls 115.19.115100.3
2) $BOM_TOP/patch/115/sql/CSTACOVB.pls 115.9.115100.3

Patch 5395791
File version upgraded.......
$BOM_TOP/patch/115/sql/CSTPUMEB.pls 115.19.115100.4


+++++++++++++++++++++++++++++++++++++++

References

Patch 3208793 - CST:STANDARD COST UPDATE PROGRAM RUNS SLOW.
Patch 5329676 - CODEFIX::AFTER UPGRADING TO 11.5.10 CU2 INCOIN HAS PERFORMANCE ISSUE
Patch 5395791 - 5329676:11.5.10 CU2 & DB 10GR2 UPGRADE - INCOIN PERFORMANCE ISSUE
Patch 5520835 - GATHER STATS ON TABLES INSIDE THE UPDATE STANDARD COSTS PROGRAM CODE
Patch 5606455 - ONDEMAND CMCICU UPDATE STANDARD COSTS PERFORMANCE ISSUE
Patch 5649449 - DATAFIX: DUPLICATE MMT RECORD OF INTRANSIT WHEN RUNNIGN STANDARED COST UPDATE
Patch 5931035 - CST: PROBLEM WITH QUANTITY SIGN IN STD COST UPDATE INTRANSIT ADJUSTMENTS
Patch 6088203 - CST:COST HISTORY FORM HAS PERFORMANCE ISSUE

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15385/viewspace-1003722/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15385/viewspace-1003722/