We are using oracle ebs 12.2.5 with Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production.
Below is a slow running query and some business process is getting delayed with it's current run time, can you please guide and provide inputs to reduce the run time by 50% at least.
SQL Monitoring Report
SQL Text
------------------------------
SELECT DB2INVB_RECORD_STATUS,DB2INVB_CORP_CODE,DB2INVB_ITEM_NO,DB2INVB_WAREHOUSE_NUMBER,DB2INVB_LAST_MAINT_DATE, DB2INVB_PRIM_PICK_BIN,DB2INVB_SUB_ITEM_NO,DB2INVB_ALLOC_LEAD_TIME,component_flag,promotion_flag, nvl(trunc(DB2INVB_UNIT_COST,2),0.0) DB2INVB_PU_UNIT_COST, nvl(trunc(DB2INVB_UNIT_COST,4),0.0) "DB2INVB_N_BU_UNIT_COST", DB2INVB_VNDR_NO,DB2INVB_ABC_CODE,DB2INVB_SKU_SFTY_STK_STD,DB2INVB_SKU_ROQ_STD, DB2INVB_DATE_LAST_SHIPPED,DB2INVB_CP_SKU_SHIP_QTY,DATE_LAST_RECPT,
DB2INV_C_SKU_ON_HAND,DB2INV_C_VALUE_ON_HAND, DB2INVB_MOVE_METHOD_CODE, DBINVB_FORECAST_FLAG,DB2INVB_KIT_FLAG,DB2INV_ORDR_MULTIPLE,DB2INV_RETURNABLE_IND ,RN FROM ( SELECT distinct DECODE(upper(mis.attribute1),'INACTIVE','I','A') "DB2INVB_RECORD_STATUS", DECODE(hou.short_code,'MNAO_OU','01','MCI_OU','02',' ') "DB2INVB_CORP_CODE", SUBSTR(( CASE WHEN LENGTH(msib.segment1)<=10 THEN SUBSTR(msib.segment1,1,4) || '-' || SUBSTR(msib.segment1,5,2) || '-' || SUBSTR(msib.segment1,7) ELSE
SUBSTR(msib.segment1,1, 4) || '-' || SUBSTR(msib.segment1,5,2) || '-' || SUBSTR(msib.segment1,7,4) || '-' || SUBSTR(msib.segment1,11) END),1,20) "DB2INVB_ITEM_NO", ood.organization_code "DB2INVB_WAREHOUSE_NUMBER", TO_CHAR(msib.last_update_date,'YYDDD') "DB2INVB_LAST_MAINT_DATE", SUBSTR(NVL( mil.segment1 ||mil.segment2 ||mil.segment3 ||mil.segment4 ||mil.segment5 ||mil.segment6 ,' '),1,12) "DB2INVB_PRIM_PICK_BIN", NVL(msib.source_subinventory,' ') "DB2INVB_SUB_ITEM_NO", msib.lead_time_lot_size
"DB2INVB_ALLOC_LEAD_TIME", msib.pick_components_flag "DB2INVB_COMPONENT_FLAG", (SELECT cic.item_cost FROM cst_item_costs cic WHERE msib.inventory_item_id = cic.inventory_item_id AND msib.organization_id = cic.organization_id ) "DB2INVB_UNIT_COST", NVL( substr(vendor_tbl.vendor_no,1,9),' ') "DB2INVB_VNDR_NO", NVL( (SELECT mac.abc_class_name FROM mtl_abc_classes mac, mtl_abc_assignments maa, mtl_abc_assignment_groups mag WHERE maa.inventory_item_id = msib.inventory_item_id AND mac.organization_id
= msib.organization_id AND mac.abc_class_id = maa.abc_class_id AND mag.assignment_group_id = maa.assignment_group_id AND mac.organization_id = mag.organization_id AND mag.assignment_group_name = xxif01_common_utils.get_fnd_lookup_meaning( 'XXIF_INTERFACE_CONSTANTS', 'ABC_ASSIGNMENT_GROUP_NAME') ),' ')"DB2INVB_ABC_CODE", msib.mrp_safety_stock_code "DB2INVB_SKU_SFTY_STK_STD", NVL(msib.minimum_order_quantity,0.0) "DB2INVB_SKU_ROQ_STD", NVL(TO_CHAR(wddcols.lpd,'YYDDD'),' ')
"DB2INVB_DATE_LAST_SHIPPED", NVL(wddcols.sq,0.0) "DB2INVB_CP_SKU_SHIP_QTY", NVL( (SELECT SUM(moq.transaction_quantity) FROM apps.mtl_onhand_quantities moq WHERE msib.inventory_item_id = moq.inventory_item_id AND msib.organization_id = moq.organization_id GROUP BY moq.inventory_item_id ) ,0.0) "DB2INV_C_SKU_ON_HAND", NVL( (SELECT AVG(moq.transaction_quantity * msib.list_price_per_unit) FROM apps.mtl_onhand_quantities moq WHERE msib.inventory_item_id = moq.inventory_item_id AND
msib.organization_id = moq.organization_id GROUP BY moq.inventory_item_id ) ,0.0)"DB2INV_C_VALUE_ON_HAND", ' ' db2invb_move_method_code, NVL(msib.forecast_horizon,0.0) "DBINVB_FORECAST_FLAG", DECODE(msib.attribute18,NULL,'N','Y') "DB2INVB_KIT_FLAG", NVL(msib.fixed_lot_multiplier,0.0) "DB2INV_ORDR_MULTIPLE", msib.returnable_flag "DB2INV_RETURNABLE_IND", NVL(prom_tbl.status,'N') promotion_flag, DECODE(msib.attribute18,'S','Y','N') component_flag, NVL(TO_CHAR( vendor_tbl.r_date,'YYDDD'),' ')
"DATE_LAST_RECPT" , row_number() over(partition by msib.segment1,ood.organization_code order by ( mil.segment1 ||mil.segment2 ||mil.segment3 ||mil.segment4 ||mil.segment5 ||mil.segment6) ) as rn FROM apps.mtl_system_items_b msib, apps.org_organization_definitions ood, apps.hr_operating_units hou, apps.mtl_item_status mis, mtl_item_locations mil , mtl_onhand_quantities moq , (SELECT pvsa.vendor_site_code vendor_no, msi.organization_id, msi.inventory_item_id,
MAX(DECODE(rt.transaction_type,'RECEIVE',rt.transaction_date,NULL)) r_date FROM apps.po_approved_supplier_list asl, apps.po_vendors pv, apps.po_vendor_sites_all pvsa, apps.org_organization_definitions oodf, apps.mtl_system_items_b msi, apps.po_asl_attributes paa, apps.po_asl_statuses pas, po.rcv_transactions rt WHERE 1 =1 AND pv.vendor_id = asl.vendor_id AND pvsa.vendor_site_id = asl.vendor_site_id AND oodf.organization_id = asl.owning_organization_id AND oodf.operating_unit = pvsa.org_id AND
asl.item_id = msi.inventory_item_id AND asl.owning_organization_id = msi.organization_id AND oodf.organization_id = msi.organization_id AND asl.asl_id = paa.asl_id AND asl.using_organization_id = paa.using_organization_id AND asl.asl_status_id = pas.status_id AND rt.vendor_id = pv.vendor_id AND rt.vendor_site_id = pvsa.vendor_site_id AND msi.organization_id = rt.organization_id(+) GROUP BY pvsa.vendor_site_code , msi.organization_id, msi.inventory_item_id ) vendor_tbl , (SELECT
msi.inventory_item_id, msi.organization_id, 'Y' status FROM qp_qualifiers_v qpv , mtl_system_items_b msi , qp_secu_list_headers_vl qslhv WHERE 1 = 1 AND qpv.qualifier_attr_value =TO_CHAR(msi.inventory_item_id) AND msi.organization_id =fnd_profile.VALUE('XXCM_IM_ORG_ID') AND qpv.list_header_id =qslhv.list_header_id AND upper(qslhv.CONTEXT) ='MODIFIER' AND upper(qslhv.list_type_code)='DLT' AND qslhv.orig_org_id = fnd_profile.VALUE('XXCM_MNAOOU_ID') ) prom_tbl ,(SELECT msi.segment1 itmno,
msi.organization_id itmorg, wdd.shipped_quantity sq, max(wnd.latest_pickup_date) lpd FROM apps.mtl_system_items_b msi, apps.org_organization_definitions oodf, apps.po_approved_supplier_list asl, apps.po_vendors pv, apps.po_vendor_sites_all pvsa, apps.wsh_new_deliveries wnd, apps.wsh_delivery_details wdd, apps.WSH_DELIVERY_ASSIGNMENTS wda WHERE 1 = 1 AND pv.vendor_id = asl.vendor_id AND pvsa.vendor_site_id = asl.vendor_site_id AND asl.item_id = msi.inventory_item_id AND
asl.owning_organization_id = msi.organization_id AND oodf.organization_id = msi.organization_id AND wdd.inventory_item_id = msi.inventory_item_id AND wdd.organization_id = msi.organization_id AND wda.delivery_detail_id = wdd.delivery_detail_id AND wnd.delivery_id = wda.delivery_id group by msi.segment1, msi.organization_id, wdd.shipped_quantity ) wddcols WHERE 1 = 1 AND msib.organization_id = ood.organization_id AND ood.operating_unit = hou.organization_id AND msib.inventory_item_status_code =
mis.inventory_item_status_code AND msib.inventory_item_id = moq.inventory_item_id(+) AND msib.organization_id = moq.organization_id(+) AND mil.organization_id = msib.organization_id AND mil.inventory_location_id(+) = moq.locator_id AND msib.organization_id = prom_tbl.organization_id(+) AND msib.inventory_item_id = prom_tbl.inventory_item_id(+) AND vendor_tbl.organization_id(+) = msib.organization_id AND vendor_tbl.inventory_item_id(+) = msib.inventory_item_id AND wddcols.itmno(+) =
msib.segment1 AND wddcols.itmorg(+)= msib.organization_id AND hou.short_code = fnd_profile.VALUE('XXCM_MNAOOU_CD') AND msib.last_update_date >= to_date('01192019200002','MMDDYYYYHH24MISS') AND msib.last_update_date < (case when '' is null then to_date('01202019200003','MMDDYYYYHH24MISS') else to_date('','MMDDYYYYHH24MISS') end) ) WHERE RN=1
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : XXINT (782:59846)
SQL ID : 4uxz1g1aaruax
SQL Execution ID : 16777216
Execution Started : 01/20/2019 20:00:08
First Refresh Time : 01/20/2019 20:00:12
Last Refresh Time : 01/21/2019 03:01:29
Duration : 25281s
Module/Action : ODI:1467382342072/1/2633/2317828/8/1/9
Service : SYS$USERS
Program : JDBC Thin Client
Fetch Calls : 631
Global Stats
========================================================================================================
| Elapsed | Cpu | IO | Concurrency | PL/SQL | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Time(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
========================================================================================================
| 25300 | 18750 | 6549 | 1.44 | 50 | 631 | 2G | 1M | 9GB | 263 | 11MB |
========================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3157990480)
==============================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 91 | +25159 | 18928 | 18928 | | | | | | | | |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | CST_ITEM_COSTS | 1 | 3 | 92 | +25158 | 18928 | 18928 | 3439 | 27MB | | | | | | |
| 2 | INDEX RANGE SCAN | CST_ITEM_COSTS_U1 | 1 | 2 | 91 | +25159 | 18928 | 18928 | 899 | 7MB | | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 3 | | | | | | | | | | | | |
| 4 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U1 | 1 | 2 | | | | | | | | | | | | |
| 5 | NESTED LOOPS | | 1 | 4 | 91 | +25159 | 18928 | 12752 | | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 3 | 91 | +25159 | 18928 | 12752 | | | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_ASSIGNMENT_GROUPS | 1 | 1 | 91 | +25159 | 18928 | 15424 | | | | | | | | |
| 8 | INDEX UNIQUE SCAN | MTL_ABC_ASSIGNMENT_GROUPS_U2 | 1 | 1 | 91 | +25159 | 18928 | 15424 | 1 | 8192 | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_ASSIGNMENTS | 1 | 2 | 92 | +25159 | 15424 | 12752 | 968 | 8MB | | | | | | |
| 10 | INDEX UNIQUE SCAN | MTL_ABC_ASSIGNMENTS_U1 | 1 | 1 | 91 | +25159 | 15424 | 12752 | 623 | 5MB | | | | | | |
| 11 | TABLE ACCESS BY INDEX ROWID | MTL_ABC_CLASSES | 1 | 1 | 91 | +25159 | 12752 | 12752 | | | | | | | | |
| 12 | INDEX UNIQUE SCAN | MTL_ABC_CLASSES_U1 | 1 | 1 | 91 | +25159 | 12752 | 12752 | | | | | | | | |
| 13 | SORT GROUP BY NOSORT | | 1 | 6 | 91 | +25159 | 18928 | 5549 | | | | | | | | |
| 14 | TABLE ACCESS BY INDEX ROWID | MTL_ONHAND_QUANTITIES_DETAIL | 1 | 6 | 91 | +25159 | 18928 | 16080 | 25 | 200KB | | | | | | |
| 15 | INDEX RANGE SCAN | MTL_ONHAND_QUANTITIES_N4 | 5 | 2 | 91 | +25159 | 18928 | 16080 | 1 | 8192 | | | | | | |
| 16 | SORT GROUP BY NOSORT | | 1 | 6 | 91 | +25159 | 18928 | 5549 | | | | | | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | MTL_ONHAND_QUANTITIES_DETAIL | 1 | 6 | 91 | +25159 | 18928 | 16080 | | | | | | | | |
| 18 | INDEX RANGE SCAN | MTL_ONHAND_QUANTITIES_N4 | 5 | 2 | 91 | +25159 | 18928 | 16080 | | | | | | | | |
| 19 | VIEW | | 1 | 30936 | 33 | +25249 | 1 | 18928 | | | | | | | | |
| 20 | HASH UNIQUE | | 1 | 30936 | 123 | +25159 | 1 | 18928 | | | | | 4M | | | |
| 21 | WINDOW SORT PUSHED RANK | | 1 | 30936 | 25246 | +4 | 1 | 18928 | 110 | 11MB | 263 | 11MB | 9M | 12M | | |
| 22 | NESTED LOOPS OUTER | | 1 | 30915 | 25156 | +4 | 1 | 38098 | | | | | | | | |
| 23 | NESTED LOOPS OUTER | | 1 | 23905 | 25156 | +4 | 1 | 38098 | | | | | | | | |
| 24 | NESTED LOOPS OUTER | | 1 | 23903 | 25156 | +4 | 1 | 38098 | | | | | | | | |
| 25 | NESTED LOOPS | | 1 | 23898 | 25156 | +4 | 1 | 20281 | | | | | | | | |
| 26 | NESTED LOOPS OUTER | | 1 | 23897 | 25156 | +4 | 1 | 20281 | | | | | | | | |
| 27 | NESTED LOOPS OUTER | | 1 | 23866 | 25156 | +4 | 1 | 20281 | | | | | | | | |
| 28 | NESTED LOOPS | | 1 | 23744 | 25156 | +4 | 1 | 18928 | | | | | | | | |
| 29 | NESTED LOOPS | | 1 | 11 | 25156 | +4 | 1 | 15 | | | | | | | | |
| 30 | NESTED LOOPS | | 1 | 10 | 25156 | +4 | 1 | 15 | | | | | | | | |
| 31 | NESTED LOOPS | | 1 | 9 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 32 | NESTED LOOPS | | 1 | 8 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 33 | NESTED LOOPS | | 1 | 7 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 34 | NESTED LOOPS | | 1 | 6 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 35 | NESTED LOOPS | | 1 | 5 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 36 | NESTED LOOPS | | 1 | 4 | 25156 | +4 | 1 | 19 | | | | | | | | |
| 37 | NESTED LOOPS | | 1 | 2 | 25156 | +4 | 1 | 24 | | | | | | | | |
| 38 | INDEX SKIP SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 1 | 25156 | +4 | 1 | 24 | | | | | | | | |
| 39 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 1 | 25156 | +4 | 24 | 24 | | | | | | | | |
| 40 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 1 | 25156 | +4 | 24 | 24 | | | | | | | | |
| 41 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 2 | 25156 | +4 | 24 | 19 | | | | | | | | |
| 42 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 25156 | +4 | 24 | 26 | | | | | | | | |
| 43 | TABLE ACCESS BY INDEX ROWID | MTL_PARAMETERS | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 44 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 45 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 2 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 46 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 47 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 48 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 49 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 50 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 51 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_IX1 | 3 | 1 | 25156 | +4 | 19 | 57 | | | | | | | | |
| 52 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 1 | 25156 | +4 | 19 | 15 | | | | | | | | |
| 53 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 25156 | +4 | 19 | 19 | | | | | | | | |
| 54 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 1 | 25156 | +4 | 15 | 15 | | | | | | | | |
| 55 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_SYSTEM_ITEMS_B | 138 | 23733 | 25156 | +4 | 15 | 18928 | 297K | 2GB | | | | | | |
| 56 | INDEX RANGE SCAN | MTL_SYSTEM_ITEMS_B_N9 | 211K | 665 | 25156 | +4 | 15 | 3M | 10635 | 83MB | | | | | | |
| 57 | VIEW PUSHED PREDICATE | | 1 | 122 | 20692 | +260 | 18928 | 2594 | | | | | | | | |
| 58 | SORT GROUP BY | | 1 | 122 | 20692 | +260 | 18928 | 2594 | | | | | 8192 | | | |
| 59 | NESTED LOOPS | | 1 | 121 | 20692 | +260 | 18928 | 93958 | | | | | | | | |
| 60 | NESTED LOOPS | | 1 | 121 | 20692 | +260 | 18928 | 93958 | | | | | | | | |
| 61 | NESTED LOOPS | | 1 | 119 | 20692 | +260 | 18928 | 95343 | | | | | | | | |
| 62 | NESTED LOOPS | | 1 | 117 | 20806 | +146 | 18928 | 95343 | | | | | | | | |
| 63 | NESTED LOOPS | | 1 | 16 | 20804 | +146 | 18928 | 3018 | | | | | | | | |
| 64 | NESTED LOOPS | | 1 | 15 | 20804 | +146 | 18928 | 3018 | | | | | | | | |
| 65 | NESTED LOOPS | | 1 | 14 | 20804 | +146 | 18928 | 3018 | | | | | | | | |
| 66 | NESTED LOOPS | | 1 | 13 | 25156 | +4 | 18928 | 3018 | | | | | | | | |
| 67 | NESTED LOOPS | | 1 | 10 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 68 | NESTED LOOPS | | 1 | 9 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 69 | NESTED LOOPS | | 1 | 6 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 70 | NESTED LOOPS | | 1 | 5 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 71 | NESTED LOOPS | | 1 | 3 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 72 | NESTED LOOPS | | 1 | 2 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 73 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 74 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 75 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 76 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 77 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 2 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 78 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 79 | TABLE ACCESS BY INDEX ROWID BATCHED | HR_ORGANIZATION_INFORMATION | 1 | 2 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 80 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 81 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_SYSTEM_ITEMS_B | 1 | 2 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 82 | INDEX RANGE SCAN | MTL_SYSTEM_ITEMS_B_N17 | 1 | 2 | 25156 | +4 | 18928 | 18928 | 749 | 6MB | | | | | | |
| 83 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 84 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | 1 | 25156 | +4 | 18928 | 18928 | | | | | | | | |
| 85 | TABLE ACCESS BY INDEX ROWID BATCHED | PO_APPROVED_SUPPLIER_LIST | 1 | 3 | 25156 | +4 | 18928 | 3018 | 1012 | 8MB | | | | | | |
| 86 | INDEX RANGE SCAN | PO_APPROVED_SUPPLIER_LIST_N1 | 1 | 2 | 25156 | +4 | 18928 | 32949 | 398 | 3MB | | | | | | |
| 87 | TABLE ACCESS BY INDEX ROWID | AP_SUPPLIERS | 1 | 1 | 20804 | +146 | 3018 | 3018 | | | | | | | | |
| 88 | INDEX UNIQUE SCAN | AP_SUPPLIERS_U1 | 1 | 1 | 20804 | +146 | 3018 | 3018 | | | | | | | | |
| 89 | INDEX UNIQUE SCAN | AP_SUPPLIER_SITES_U1 | 1 | 1 | 20804 | +146 | 3018 | 3018 | | | | | | | | |
| 90 | INDEX UNIQUE SCAN | HZ_PARTIES_U1 | 1 | 1 | 20804 | +146 | 3018 | 3018 | | | | | | | | |
| 91 | TABLE ACCESS BY INDEX ROWID BATCHED | WSH_DELIVERY_DETAILS | 1 | 101 | 20692 | +260 | 3018 | 95343 | 55953 | 437MB | | | | | | |
| 92 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N9 | 186 | 3 | 20690 | +260 | 3018 | 95343 | 679 | 5MB | | | | | | |
| 93 | TABLE ACCESS BY INDEX ROWID BATCHED | WSH_DELIVERY_ASSIGNMENTS | 1 | 2 | 20692 | +260 | 95343 | 95343 | 41068 | 321MB | | | | | | |
| 94 | INDEX RANGE SCAN | WSH_DELIVERY_ASSIGNMENTS_N3 | 1 | 2 | 20692 | +260 | 95343 | 95343 | 15469 | 121MB | | | | | | |
| 95 | INDEX UNIQUE SCAN | WSH_NEW_DELIVERIES_U1 | 1 | 1 | 25022 | +260 | 96293 | 93958 | 428 | 3MB | | | | | | |
| 96 | TABLE ACCESS BY INDEX ROWID | WSH_NEW_DELIVERIES | 1 | 2 | 20692 | +260 | 93958 | 93958 | | | | | | | | |
| 97 | VIEW PUSHED PREDICATE | | 1 | 31 | | | 20281 | | | | | | | | | |
| 98 | FILTER | | | | | | 20281 | | | | | | | | | |
| 99 | NESTED LOOPS | | 2 | 31 | 131 | +4 | 3150 | 0 | | | | | | | | |
| 100 | NESTED LOOPS | | 224 | 31 | 131 | +4 | 3150 | 17M | | | | | | | | |
| 101 | NESTED LOOPS | | 2 | 18 | 131 | +4 | 3150 | 47250 | | | | | | | | |
| 102 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 2 | 131 | +4 | 3150 | 3150 | 40 | 320KB | | | | | | |
| 103 | VIEW | QP_SECU_LIST_HEADERS_VL | 2 | 16 | 131 | +4 | 3150 | 47250 | | | | | | | | |
| 104 | UNION-ALL | | | | 131 | +4 | 3150 | 47250 | | | | | | | | |
| 105 | NESTED LOOPS | | 1 | 16 | 131 | +4 | 3150 | 47250 | | | | | | | | |
| 106 | HASH JOIN | | 1 | 15 | 131 | +4 | 3150 | 47250 | | | | | 2M | | | |
| 107 | TABLE ACCESS FULL | QP_LIST_HEADERS_B | 1 | 9 | 131 | +4 | 3150 | 63000 | 11 | 160KB | | | | | | |
| 108 | VIEW | | 17 | 6 | 131 | +4 | 3150 | 312K | | | | | | | | |
| 109 | SORT UNIQUE | | 17 | 6 | 131 | +4 | 3150 | 312K | | | | | 6144 | | | |
| 110 | COUNT | | | | 131 | +4 | 3150 | 312K | | | | | | | | |
| 111 | FILTER | | | | 131 | +4 | 3150 | 312K | | | | | | | | |
| 112 | TABLE ACCESS FULL | QP_GRANTS | 17 | 5 | 131 | +4 | 3150 | 312K | 2 | 112KB | | | | | | |
| 113 | INDEX UNIQUE SCAN | QP_LIST_HEADERS_TL_PK | 1 | 1 | 131 | +4 | 47250 | 47250 | | | | | | | | |
| 114 | FILTER | | | | | | 3150 | | | | | | | | | |
| 115 | FILTER | | | | | | 3150 | | | | | | | | | |
| 116 | NESTED LOOPS | | 1 | 11 | | | | | | | | | | | | |
| 117 | NESTED LOOPS | | 1 | 10 | | | | | | | | | | | | |
| 118 | TABLE ACCESS FULL | QP_LIST_HEADERS_B | 1 | 9 | | | | | | | | | | | | |
| 119 | INDEX UNIQUE SCAN | QP_LIST_HEADERS_TL_PK | 1 | 1 | | | | | | | | | | | | |
| 120 | INDEX UNIQUE SCAN | QP_LIST_HEADERS_TL_PK | 1 | 1 | | | | | | | | | | | | |
| 121 | COUNT | | | | | | | | | | | | | | | |
| 122 | FILTER | | | | | | | | | | | | | | | |
| 123 | TABLE ACCESS BY INDEX ROWID BATCHED | QP_GRANTS | 1 | 2 | | | | | | | | | | | | |
| 124 | INDEX RANGE SCAN | QP_GRANTS_N4 | 1 | 2 | | | | | | | | | | | | |
| 125 | INDEX RANGE SCAN | QP_QUALIFIERS_N11 | 112 | 1 | 131 | +4 | 47250 | 17M | 17 | 136KB | | | | | | |
| 126 | TABLE ACCESS BY INDEX ROWID | QP_QUALIFIERS | 1 | 6 | | | 17M | | 149 | 1MB | | | | | | |
| 127 | TABLE ACCESS BY INDEX ROWID | MTL_ITEM_STATUS_TL | 1 | 1 | 25156 | +4 | 20281 | 20281 | | | | | | | | |
| 128 | INDEX UNIQUE SCAN | MTL_ITEM_STATUS_TL_U1 | 1 | 1 | 25156 | +4 | 20281 | 20281 | | | | | | | | |
| 129 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ONHAND_QUANTITIES_DETAIL | 5 | 6 | 24896 | +260 | 20281 | 24515 | 4 | 32768 | | | | | | |
| 130 | INDEX RANGE SCAN | MTL_ONHAND_QUANTITIES_N4 | 5 | 2 | 24898 | +260 | 20281 | 24515 | 322 | 3MB | | | | | | |
| 131 | TABLE ACCESS BY INDEX ROWID | MTL_ITEM_LOCATIONS | 1 | 2 | 24854 | +260 | 38098 | 24514 | 538K | 4GB | | | | | | |
| 132 | INDEX UNIQUE SCAN | MTL_ITEM_LOCATIONS_U1 | 1 | 1 | 24854 | +260 | 38098 | 24514 | | | | | | | | |
| 133 | VIEW PUSHED PREDICATE | | 1 | 7010 | 20694 | +260 | 38098 | 13116 | | | | | | | | |
| 134 | SORT GROUP BY | | 1 | 7010 | 20808 | +146 | 38098 | 13116 | | | | | 2048 | | | |
| 135 | NESTED LOOPS | | 1 | 7009 | 20808 | +146 | 38098 | 4G | | | | | | | | |
| 136 | NESTED LOOPS OUTER | | 1 | 7008 | 20808 | +146 | 38098 | 4G | | | | | | | | |
| 137 | NESTED LOOPS |