High CPU consumption - query
Hi All,
We have a high cpu intensive query. When it starts running,we keep on getting cpu alerts.
We need your help in fixing it. Please kindly suggest
| 40 | INDEX FAST FULL SCAN | MTL_CST_LAYER_ACT_CST_DTLS_U1 | 4421K| 50M| 10461 (1)| 00:00:01 |
From our understand,above operation seems doing lot of work. I understand the format is not good and hence attached with good format.
SELECT NVL (SUM (FIFO_VALUE), 0) FROM APPS.XXTTD_INV_LAYER_COST_UPDATE_V XILC, APPS.MTL_SECONDARY_INVENTORIES MSINV WHERE 1 = 1 AND XILC.LAYER_SUBINV=MSINV.SECONDARY_INVENTORY_NAME AND MSINV.DESCRIPTION= :B4 AND XILC.ORGANIZATION_ID = :B3 AND XILC.INVENTORY_ITEM_ID = :B2 AND XILC.TRANSACTION_DATE < :B1 Plan hash value: 3141273038 ------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 5258 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | NESTED LOOPS | | 1 | 75 | 7 (0)| 00:00:01 | | 3 | NESTED LOOPS | | 2 | 75 | 7 (0)| 00:00:01 | | 4 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 61 | 4 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 1 | | 3 (0)| 00:00:01 | |* 6 | INDEX RANGE SCAN | MTL_TRANSACTION_ACCOUNTS_N1 | 2 | | 2 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID | MTL_TRANSACTION_ACCOUNTS | 1 | 14 | 3 (0)| 00:00:01 | | 8 | SORT AGGREGATE | | 1 | 111 | | | | 9 | NESTED LOOPS | | 1 | 111 | 58 (0)|
0