My Oracle Support Banner

Cost Manager Running Slow on Query - update /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) (Doc ID 2187196.1)

Last updated on AUGUST 31, 2018

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, Performance

ACTUAL BEHAVIOR
---------------
Cost manager is processing around 1 transaction per second. We create more than 100K transactions daily so it will not be able to catch up.

The query causing the slowdown appears to be:
update /*+ INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N10) +*/
mtl_material_transactions MMT set transaction_group_id=:b0,
last_update_date=SYSDATE,last_updated_by=:b1,last_update_login=:b2,
program_application_id=:b3,program_id=:b4,program_update_date=SYSDATE
where
((((((costed_flag='N' and ((nvl(parent_transaction_id,transaction_id)=
transaction_id or (nvl(parent_transaction_id,transaction_id)
<>transaction_id and transaction_action_id in (15,22))) or
((((parent_transaction_id is not null and
parent_transaction_id<>transaction_id) and transaction_action_id=17) and
transaction_source_type_id=7) and 1=(select
MOD(sum(DECODE(MP.process_enabled_flag,'Y',1,2)),2) from mtl_parameters mp
where (mp.organization_id=mmt.organization_id or mp.organization_id=
mmt.transfer_organization_id))))) and (transaction_date<TO_DATE(:b5,
'YYYY/MM/DD HH24:MI:SS') or (((:b6=0 and transaction_date=TO_DATE(:b5,
'YYYY/MM/DD HH24:MI:SS')) and transaction_date<TO_DATE(:b8,'YYYY/MM/DD
HH24:MI:SS')) and transaction_id<:b9))) and DECODE(transaction_action_id,12,
DECODE(transfer_organization_id,:b10,NVL(shipment_costed,'N'),'N'),
DECODE(organization_id,:b10,NVL(shipment_costed,'N'),'N'))<>'Y') and not
((MMT.transaction_source_type_id=5 and NVL(MMT.flow_schedule,'N')='Y') and
MMT.transaction_action_id in (1,27,33,34))) and (((organization_id=:b10 and
 not exists (select null from mtl_material_transactions_temp MMTP where
((MMT.transaction_action_id in (30,31) and MMT.organization_id=
MMTP.organization_id) and (MMT.COMPLETION_TRANSACTION_ID=
MMTP.COMPLETION_TRANSACTION_ID or MMT.MOVE_TRANSACTION_ID=
MMTP.move_transaction_id)) union all select null from
wip_move_txn_interface WMTI where (MMT.transaction_action_id in (30,31) and
WMTI.TRANSACTION_ID=MMT.MOVE_TRANSACTION_ID))) and not
(MMT.transaction_action_id in (3,12) and (exists (select null from
mtl_parameters where (organization_id=MMT.transfer_organization_id and
primary_cost_method in (2,5,6))) and (((transaction_action_id=3 and
primary_quantity>0) and transaction_cost is null ) or
((MMT.transaction_action_id=12 and MMT.shipment_costed is null ) and exists
(select null from mtl_interorg_parameters MIP where
((MIP.to_organization_id=MMT.organization_id and MIP.from_organization_id=
MMT.transfer_organization_id) and NVL(MMT.fob_point,MIP.fob_point)=2)))))))
or (MMT.transfer_organization_id=:b10 and ((exists (select null from
mtl_parameters where (organization_id=MMT.organization_id and
primary_cost_method in (2,5,6))) and exists (select null from
mtl_interorg_parameters MIP where (((((MMT.transaction_action_id=21 and
MIP.to_organization_id=MMT.transfer_organization_id) and
MIP.from_organization_id=MMT.organization_id) and NVL(MMT.fob_point,
MIP.fob_point)=1) and MMT.shipment_costed='Y') or
((((MMT.transaction_action_id=12 and MIP.from_organization_id=
MMT.transfer_organization_id) and MIP.to_organization_id=
MMT.organization_id) and NVL(MMT.fob_point,MIP.fob_point)=2) and
MMT.shipment_costed is null )))) or (exists (select null from
mtl_parameters where ((organization_id=MMT.organization_id and
primary_cost_method=1) and MMT.transaction_date<NVL(cost_cutoff_date,
(SYSDATE+1)))) and (exists (select null from mtl_interorg_parameters MIP
where ((((MMT.transaction_action_id=12 and MIP.from_organization_id=
MMT.transfer_organization_id) and MIP.to_organization_id=
MMT.organization_id) and NVL(MMT.fob_point,MIP.fob_point)=2) or
(((MMT.transaction_action_id=21 and MIP.to_organization_id=
MMT.transfer_organization_id) and MIP.from_organization_id=
MMT.organization_id) and NVL(MMT.fob_point,MIP.fob_point)=1))) or
(MMT.transaction_action_id=3 and MMT.primary_quantity>0))))))) and not
(transaction_action_id=36 and (exists (select null from
cst_revenue_cogs_match_lines where ((cogs_om_line_id=trx_source_line_id and
pac_cost_type_id is null ) and original_shipped_qty is null )) or :b14=1)))

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.