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

Last updated on MARCH 08, 2017

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

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