In Oracle Cost Management, WIP Distributions Form and view CST_DISTRIBUTION_V exhibits bad performance. (Doc ID 1380718.1)

Last updated on JANUARY 13, 2017

Applies to:

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

Symptoms


On : 12.0.4 version, Performance

ACTUAL BEHAVIOR
---------------
In Oracle Cost Management, WIP Distributions Form and view CST_DISTRIBUTION_V exhibits bad performance.

A certain query:

SELECT TRANSACTION_DATE,BASE_TRANSACTION_VALUE,REVISION,TRANSACTION_TYPE_NAME,
OPERATION_SEQ_NUM,DEPARTMENT_CODE,RESOURCE_SEQ_NUM,SUBINVENTORY_CODE,
TRANSACTION_ID,CURRENCY_CODE,TRANSACTION_VALUE,CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,CURRENCY_CONVERSION_DATE,LINE_TYPE_NAME,
TRANSACTION_SOURCE,PRIMARY_UOM,PRIMARY_QUANTITY,WIP_ENTITY_NAME,LINE_CODE,
BASIS,REASON_NAME,REFERENCE,GL_BATCH_ID,RESOURCE_CODE,COST_ELEMENT,
TRANSACTION_QUANTITY,TRANSACTION_UOM,RATE_OR_AMOUNT,UNIT_COST,
ORGANIZATION_ID,WIP_ENTITY_ID,PRIMARY_ITEM_ID,LINE_ID,ACCT_PERIOD_ID,
INVENTORY_ITEM_ID,REFERENCE_ACCOUNT,REPETITIVE_SCHEDULE_ID,CONTRA_SET_ID,
ACTIVITY,OVERHEAD_BASIS_FACTOR,BASIS_RESOURCE_ID
FROM
CST_DISTRIBUTION_V WHERE organization_id = :1 and (TRANSACTION_DATE BETWEEN
to_date('2011/06/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') AND
to_date('2011/07/25 23:59:59', 'YYYY/MM/DD HH24:MI:SS')) order by
transaction_date desc, transaction_id desc, sign(base_transaction_value)
desc, abs(base_transaction_value) desc

which is part of the standard view (CST_DISTRIBUTION_V), is running very slow. Execution plan indicates full table scans of various tables.


TABLE ACCESS FULL TABLE INV.MTL_SYSTEM_ITEMS_B
TABLE ACCESS FULL TABLE WIP.WIP_ENTITIES
TABLE ACCESS FULL TABLE INV.MTL_TRANSACTION_ACCOUNTS
TABLE ACCESS FULL TABLE INV.MTL_MATERIAL_TRANSACTIONS

EXPECTED BEHAVIOR
-----------------------
expect a better performance

STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Cost Management-->View Transactions-->WIP Distributions.

Changes

 

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