Hi All,
I am in need of inputs to get the below query fixed, please help.
SQL Monitoring Report
SQL Text
------------------------------
SELECT msib.segment1 ITEM, msib.description ITEM_DESCRIPTION, (SELECT mc2.segment1 || '-' || mc2.segment2 || '-' || mc2.segment3 FROM apps.MTL_ITEM_CATEGORIES mic2, apps.mtl_category_sets_tl mcst2, apps.mtl_categories mc2 WHERE 1 = 1 AND mic2.inventory_item_id = msib.inventory_item_id AND mic2.organization_id = 85 AND mcst2.CATEGORY_SET_NAME = 'MZ PRODUCT HIERARCHY' AND mcst2.category_set_id = mic2.category_set_id AND mic2.category_id = mc2.category_id ) PRODUCT_GRP_TYP_FAM,
milk.CONCATENATED_SEGMENTS LOCATORS, flv.meaning STORAGE_TYPE, onhand.on_hand ON_HAND, (SELECT MAX (transaction_date) FROM apps.MTL_MATERIAL_TRANSACTIONS MMT WHERE mmt.inventory_item_id = msib.inventory_item_id AND mmt.organization_id = msib.organization_id AND MMT.SUBINVENTORY_CODE = onhand.subinventory_code AND ( mmt.locator_id = onhand.locator_id OR mmt.transfer_locator_id = onhand.locator_id) ) DATE_OF_MAINTENANCE, mp1.ORGANIZATION_code ORG, NULL INDICATORS, primary_uom_code UOM,
abc_class_name ABC_CODE, 'Pending' PACKAGE_QTY, (SELECT ROUND (cic.item_cost, 5) FROM apps.cst_cost_types cct, apps.cst_item_costs cic WHERE cct.cost_type_id = cic.cost_type_id AND cic.inventory_item_id = msib.inventory_item_id AND cic.organization_id = msib.organization_id AND cct.cost_type = 'Average' ) ITEM_COST, (msib.DIMENSION_UOM_CODE || '-' || msib.UNIT_LENGTH || '-' || msib.unit_width || '-' || msib.unit_height) SIZE_UOM_LWH, apps.xxif01_common_utils.get_itemcat_con_value
(msib.inventory_item_id, msib.organization_id, 'MZ WMS COMMODITY CD', 'ORG') COMMODITY_CATEGORY, apps.xxif01_common_utils.get_itemcat_con_value (msib.inventory_item_id, msib.organization_id, 'MZ EOQ', 'ORG') EOQ_CATEGORY, SYS_GUID() RECORD_ID, SYSDATE CREATION_DATE, SYSDATE LAST_UPDATE_DATE, msib.unit_weight WEIGHT, msib.WEIGHT_UOM_CODE WEIGHT_UOM, mms.status_code STATUS_OF_LOCATOR FROM apps.MTL_SYSTEM_ITEMS_B msib, apps.mtl_parameters mp1, apps.mtl_item_locations_kfv milk,
mtl_material_statuses mms, apps.FND_LOOKUP_values flv, (SELECT A.ORGANIZATION_ID , B.INVENTORY_ITEM_ID , A.ASSIGNMENT_GROUP_NAME , D.ABC_CLASS_NAME FROM MTL_ABC_ASSIGNMENT_GROUPS A, MTL_ABC_ASSIGNMENTS B, MTL_ABC_CLASSES D WHERE A.ASSIGNMENT_GROUP_ID = B.ASSIGNMENT_GROUP_ID AND B.ABC_CLASS_ID = D.ABC_CLASS_ID AND A.ASSIGNMENT_GROUP_NAME = 'Putaway Classes' ) maav, (SELECT /*+ MATERIALIZE */ NVL (SUM (mq.transaction_quantity), 0) on_hand, mq.inventory_item_id, mq.organization_id,
mq.subinventory_code, mq.LOCATOR_ID FROM apps.mtl_onhand_quantities mq WHERE ORGANIZATION_ID in (92,90,88,89,91,87,86) GROUP BY mq.inventory_item_id, mq.organization_id, mq.subinventory_code, mq.LOCATOR_ID )onhand WHERE onhand.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID AND onhand.ORGANIZATION_ID = MSIB.ORGANIZATION_ID AND mp1.ORGANIZATION_ID = MSIB.ORGANIZATION_ID AND milk.INVENTORY_LOCATION_ID = onhand.LOCATOR_ID AND milk.status_id = mms.status_id AND flv.lookup_code =
milk.inventory_location_type AND flv.lookup_type = 'MTL_LOCATOR_TYPES' AND maav.inventory_item_id(+) = msib.inventory_item_id AND maav.organization_id(+) = MSIB.ORGANIZATION_ID AND MSIB.ORGANIZATION_ID in (92,90,88,89,91,87,86)
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : INT (2859:47593)
SQL ID : gvwsq7uh5jg4b
SQL Execution ID : 16777217
Execution Started : 03/20/2018 14:33:59
First Refresh Time : 03/20/2018 14:34:03
Last Refresh Time : 03/20/2018 14:52:30
Duration : 1111s
Module/Action : ODI:1467382342072/1/3454/1815456/8/1/9
Service : SYS$USERS
Program : JDBC Thin Client
Fetch Calls : 11689
Global Stats
========================================================================================
| Elapsed | Cpu | IO | Concurrency | PL/SQL | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Calls | Gets | Reqs | Bytes |
========================================================================================
| 1145 | 383 | 762 | 0.00 | 7.47 | 11689 | 116M | 3M | 25GB |
========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=4040412079)
===============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1002 | +110 | 345K | 345K | | | | 1.74 | Cpu (19) |
| 1 | NESTED LOOPS | | 1 | 11 | 1002 | +110 | 345K | 345K | | | | | |
| 2 | NESTED LOOPS | | 1 | 10 | 1002 | +110 | 345K | 345K | | | | | |
| 3 | NESTED LOOPS | | 1 | 8 | 1002 | +110 | 345K | 345K | | | | | |
| 4 | TABLE ACCESS FULL | MTL_CATEGORY_SETS_TL | 1 | 6 | 1002 | +110 | 345K | 345K | | | | 0.64 | Cpu (7) |
| 5 | INDEX RANGE SCAN | MTL_ITEM_CATEGORIES_U1 | 1 | 2 | 1002 | +110 | 345K | 345K | 39117 | 306MB | | 3.39 | Cpu (3) |
| | | | | | | | | | | | | | db file sequential read (34) |
| 6 | TABLE ACCESS BY INDEX ROWID | MTL_CATEGORIES_B | 1 | 2 | 1002 | +110 | 345K | 345K | | | | 0.27 | Cpu (3) |
| 7 | INDEX UNIQUE SCAN | MTL_CATEGORIES_B_U1 | 1 | 1 | 1002 | +110 | 345K | 345K | | | | 0.09 | Cpu (1) |
| 8 | INDEX UNIQUE SCAN | MTL_CATEGORIES_TL_U1 | 1 | 1 | 1002 | +110 | 345K | 345K | | | | 0.09 | Cpu (1) |
| 9 | SORT AGGREGATE | | 1 | | 1002 | +110 | 351K | 351K | | | | 0.09 | Cpu (1) |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_MATERIAL_TRANSACTIONS | 1 | 64 | 1002 | +110 | 351K | 5M | 3M | 20GB | | 46.75 | Cpu (188) |
| | | | | | | | | | | | | | db file parallel read (235) |
| | | | | | | | | | | | | | db file scattered read (1) |
| | | | | | | | | | | | | | db file sequential read (86) |
| 11 | INDEX RANGE SCAN | XXMTL_MATERIAL_TRANSACTIONS_N1 | 112 | 3 | 1002 | +110 | 351K | 123M | 129K | 1GB | | 10.91 | Cpu (20) |
| | | | | | | | | | | | | | db file sequential read (99) |
| 12 | NESTED LOOPS | | 1 | 3 | 1002 | +110 | 348K | 348K | | | | | |
| 13 | NESTED LOOPS | | 1 | 3 | 1002 | +110 | 348K | 348K | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | CST_COST_TYPES | 1 | 2 | 1002 | +110 | 348K | 348K | | | | | |
| 15 | INDEX RANGE SCAN | CST_COST_TYPES_U2 | 1 | 1 | 1002 | +110 | 348K | 348K | | | | | |
| 16 | INDEX UNIQUE SCAN | CST_ITEM_COSTS_U1 | 1 | 1 | 1002 | +110 | 348K | 348K | 8512 | 67MB | | 0.92 | db file sequential read (10) |
| 17 | TABLE ACCESS BY INDEX ROWID | CST_ITEM_COSTS | 1 | 2 | 1002 | +110 | 348K | 348K | 32713 | 256MB | | 1.47 | Cpu (1) |
| | | | | | | | | | | | | | db file sequential read (15) |
| 18 | NESTED LOOPS OUTER | | 115K | 491K | 1002 | +110 | 1 | 351K | | | | 0.09 | Cpu (1) |
| 19 | HASH JOIN | | 115K | 75512 | 1108 | +4 | 1 | 351K | | | 933K | 0.09 | Cpu (1) |
| 20 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_MATERIAL_STATUSES_TL | 9 | 3 | 1 | +4 | 1 | 9 | | | | | |
| 21 | INDEX SKIP SCAN | MTL_MATERIAL_STATUSES_TL_PK | 9 | 1 | 1 | +4 | 1 | 9 | | | | | |
| 22 | HASH JOIN | | 115K | 75507 | 1108 | +4 | 1 | 351K | | | 1M | | |
| 23 | INDEX SKIP SCAN | MTL_MATERIAL_STATUSES_B_PK | 9 | 1 | 1 | +4 | 1 | 9 | | | | | |
| 24 | HASH JOIN | | 115K | 75505 | 1108 | +4 | 1 | 351K | | | 913K | 0.18 | Cpu (2) |
| 25 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 27 | 5 | 1 | +4 | 1 | 7 | | | | | |
| 26 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 27 | 2 | 1 | +4 | 1 | 7 | 1 | 8192 | | | |
| 27 | HASH JOIN | | 119K | 75499 | 1100 | +12 | 1 | 351K | | | 55M | | |
| 28 | HASH JOIN | | 120K | 69198 | 107 | +4 | 1 | 351K | | | 26M | 0.18 | Cpu (2) |
| 29 | VIEW | | 240K | 5414 | 3 | +2 | 1 | 351K | | | | 0.09 | Cpu (1) |
| 30 | HASH GROUP BY | | 240K | 5414 | 1 | +4 | 1 | 351K | | | 33M | | |
| 31 | TABLE ACCESS FULL | MTL_ONHAND_QUANTITIES_DETAIL | 240K | 3613 | 1 | +4 | 1 | 581K | | | | | |
| 32 | NESTED LOOPS | | 708K | 59719 | 107 | +4 | 1 | 3M | | | | | |
| 33 | NESTED LOOPS | | 708K | 59719 | 107 | +4 | 1 | 3M | | | | | |
| 34 | INLIST ITERATOR | | | | 45 | +4 | 1 | 1 | | | | | |
| 35 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 7 | 2 | 45 | +4 | 7 | 7 | | | | | |
| 36 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 7 | 1 | 45 | +4 | 7 | 7 | | | | | |
| 37 | INDEX RANGE SCAN | MTL_SYSTEM_ITEMS_B_N9 | 74522 | 658 | 107 | +4 | 6109 | 3M | 4732 | 37MB | | 0.09 | db file sequential read (1) |
| 38 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 101K | 8531 | 108 | +3 | 3M | 3M | 150K | 1GB | | 9.53 | Cpu (7) |
| | | | | | | | | | | | | | db file parallel read (7) |
| | | | | | | | | | | | | | db file sequential read (90) |
| 39 | TABLE ACCESS FULL | MTL_ITEM_LOCATIONS | 440K | 4392 | 1002 | +110 | 1 | 442K | 16051 | 125MB | | 2.38 | Cpu (2) |
| | | | | | | | | | | | | | db file sequential read (24) |
| 40 | VIEW PUSHED PREDICATE | | 1 | 4 | 1002 | +110 | 351K | 344K | | | | | |
| 41 | FILTER | | | | 1002 | +110 | 351K | 344K | | | | | |
| 42 | NESTED LOOPS | | 1 | 4 | 1002 | +110 | 351K | 344K | | | | 0.09 | Cpu (1) |
| 43 | NESTED LOOPS | | 1 | 3 | 1002 | +110 | 351K | 344K | | | | | |
| 44 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_ASSIGNMENT_GROUPS | 1 | 1 | 1002 | +110 | 351K | 351K | | | | 0.09 | Cpu (1) |
| 45 | INDEX UNIQUE SCAN | MTL_ABC_ASSIGNMENT_GROUPS_U2 | 1 | 1 | 1002 | +110 | 351K | 351K | | | | 0.09 | Cpu (1) |
| 46 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_ASSIGNMENTS | 1 | 2 | 1002 | +110 | 351K | 344K | 6289 | 49MB | | 0.18 | db file sequential read (2) |
| 47 | INDEX UNIQUE SCAN | MTL_ABC_ASSIGNMENTS_U1 | 1 | 1 | 1002 | +110 | 351K | 344K | 4654 | 36MB | | 0.46 | Cpu (2) |
| | | | | | | | | | | | | | db file sequential read (3) |
| 48 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_CLASSES | 1 | 1 | 1002 | +110 | 344K | 344K | | | | | |
| 49 | INDEX UNIQUE SCAN | MTL_ABC_CLASSES_U1 | 1 | 1 | 1002 | +110 | 344K | 344K | | | | | |
===============================================================================================================================================================================================================