SQL Monitoring Report SQL Text ------------------------------ WITH go_up_in_chain AS ( SELECT + CONNECT_BY_FILTERING CASE WHEN CONNECT_BY_ISLEAF = 1 THEN msib1.inventory_item_id END AS from_item_id , CASE WHEN CONNECT_BY_ISLEAF = 1 THEN msib1.segment1 END AS from_item , mri.related_item_id AS to_item_id , msib2.segment1 AS to_item , LEVEL AS priority , 'UP' AS direction FROM apps.mtl_related_items mri INNER JOIN apps.mtl_system_items_b msib1 ON mri.inventory_item_id = msib1.inventory_item_id AND mri.organization_id = msib1.organization_id INNER JOIN apps.mtl_system_items_b msib2 ON mri.related_item_id = msib2.inventory_item_id AND mri.organization_id = msib2.organization_id INNER JOIN apps.fnd_lookup_values flv ON mri.relationship_type_id = TO_NUMBER(TO_CHAR(FLV.lookup_code)) AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES' AND flv.meaning LIKE 'MNAO%' WHERE msib1.organization_id = 85 AND msib2.organization_id = 85 AND SYSDATE BETWEEN NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1) AND CONNECT_BY_ISCYCLE = 0 START WITH msib2.segment1 = '057825130B' AND mri.attr_char1 IN ('AN') AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id , inventory_item_id FROM mtl_related_items ) CONNECT BY NOCYCLE PRIOR mri.inventory_item_id = mri.related_item_id AND mri.attr_char1 IN ('AN') AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id , inventory_item_id FROM mtl_related_items ) ), go_down_in_chain AS ( SELECT + CONNECT_BY_FILTERING CONNECT_BY_ROOT (msib1.inventory_item_id) AS from_item_id , CONNECT_BY_ROOT (msib1.segment1) AS from_item , mri.related_item_id AS to_item_id , msib2.segment1 AS to_item , LEVEL AS priority , 'DOWN' AS direction FROM apps.mtl_related_items mri INNER JOIN apps.mtl_system_items_b msib1 ON mri.inventory_item_id = msib1.inventory_item_id AND mri.organization_id = msib1.organization_id INNER JOIN apps.mtl_system_items_b msib2 ON mri.related_item_id = msib2.inventory_item_id AND mri.organization_id = msib2.organization_id INNER JOIN apps.fnd_lookup_values flv ON TO_NUMBER(TO_CHAR(FLV.lookup_code)) = mri.relationship_type_id AND flv.lookup_type = 'MTL_RELATIONSHIP_TYPES' AND flv.meaning LIKE 'MNAO%' WHERE msib1.organization_id = 85 AND msib2.organization_id = 85 AND SYSDATE BETWEEN NVL(mri.start_date, SYSDATE - 1) AND NVL(mri.end_date, SYSDATE + 1) AND CONNECT_BY_ISCYCLE = 0 START WITH msib1.segment1 = '057825130B' AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id , inventory_item_id FROM mtl_related_items ) CONNECT BY NOCYCLE PRIOR flv.meaning != 'MNAO MKTG KITS' AND mri.inventory_item_id = PRIOR mri.related_item_id AND PRIOR mri.attr_char1 IN ('A') AND ( mri.inventory_item_id, mri.related_item_id ) NOT IN ( SELECT related_item_id , inventory_item_id FROM mtl_related_items ) ), tree AS ( SELECT FROM go_down_in_chain UNION ALL SELECT FROM go_up_in_chain ) SELECT + monitor FIRST_VALUE ( FROM_item ) OVER ( ORDER BY CASE WHEN direction = 'UP' THEN 1 ELSE 2 END, CASE WHEN direction = 'UP' THEN - priority ELSE priority END ) from_item , t.to_item to_item FROM tree t ORDER BY CASE WHEN direction = 'UP' THEN 1 ELSE 2 END , CASE WHEN direction = 'UP' THEN - priority ELSE priority END Global Information ------------------------------ Status DONE (ALL ROWS) Instance ID 1 Session ASAROHA (152538061) SQL ID cdvsvxamf06ck SQL Execution ID 16777217 Execution Started 08262021 065531 First Refresh Time 08262021 065531 Last Refresh Time 08262021 065532 Duration 1s ModuleAction SQL Developer- Service EBSPRD Program SQL Developer Fetch Calls 1 Global Stats ====================================== Elapsed Cpu Fetch Buffer Time(s) Time(s) Calls Gets ====================================== 0.80 0.80 1 636K ====================================== SQL Plan Monitoring Details (Plan Hash Value=3872023562) ================================================================================================================================================================================ Id Operation Name Rows Cost Time Start Execs Rows Mem Activity Activity Detail (Estim) Active(s) Active (Actual) (Max) (%) (# samples) ================================================================================================================================================================================ 0 SELECT STATEMENT 1 +1 1 3 1 WINDOW SORT 4 3800 1 +1 1 3 2048 2 VIEW 4 3800 1 +1 1 3 3 UNION-ALL 1 +1 1 3 4 VIEW 2 79 1 +1 1 1 5 FILTER 1 +1 1 1 6 CONNECT BY WITH FILTERING 1 +1 1 1 7 NESTED LOOPS 1 33 1 +1 1 1 8 NESTED LOOPS 1 33 1 +1 1 1 9 NESTED LOOPS ANTI 1 31 1 +1 1 1 10 NESTED LOOPS 1 30 1 +1 1 1 11 MERGE JOIN CARTESIAN 1 26 1 +1 1 15 12 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES 1 3 1 +1 1 3 13 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 1 2 1 +1 1 3 14 BUFFER SORT 7 23 1 +1 3 15 2048 15 TABLE ACCESS BY INDEX ROWID BATCHED MTL_SYSTEM_ITEMS_B 7 23 1 +1 1 5 16 INDEX SKIP SCAN MTL_SYSTEM_ITEMS_B_N1 7 21 1 +1 1 5 17 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS 1 3 1 +1 15 1 18 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 1 2 1 +1 15 1 19 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 410K 2 1 20 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 1 +1 1 1 21 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 1 +1 1 1 22 NESTED LOOPS 1 44 1 23 NESTED LOOPS 1 43 1 24 NESTED LOOPS ANTI 1 41 1 25 NESTED LOOPS 1 39 1 +1 1 0 26 MERGE JOIN CARTESIAN 1 36 1 +1 1 3 27 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES 1 3 1 +1 1 3 28 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 1 2 1 +1 1 3 29 BUFFER SORT 1 33 1 +1 3 3 2048 30 CONNECT BY PUMP 1 +1 1 1 31 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS 1 3 3 32 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 1 2 3 33 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 410K 2 34 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 35 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 36 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 37 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 38 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 1 2 39 VIEW 2 3721 1 +1 1 2 40 FILTER 1 +1 1 2 41 CONNECT BY WITH FILTERING 1 +1 1 2 42 NESTED LOOPS 1 1241 1 +1 1 1 43 NESTED LOOPS 1 1241 1 +1 1 83215 44 NESTED LOOPS 1 1239 1 +1 1 83215 45 NESTED LOOPS ANTI 1 1238 1 +1 1 83215 46 NESTED LOOPS 3 1233 1 +1 1 84018 47 TABLE ACCESS BY INDEX ROWID BATCHED FND_LOOKUP_VALUES 1 3 1 +1 1 3 48 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 1 2 1 +1 1 3 49 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS 12811 1230 1 +1 3 84018 50 INDEX RANGE SCAN MTL_RELATED_ITEMS_N1 51249 123 1 +1 3 197K 51 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 410K 2 1 +1 84017 803 52 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 1 +1 83215 83215 53 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 1 +1 83215 83215 54 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 1 +1 83215 83215 55 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 1 +1 83215 1 56 NESTED LOOPS 1 2478 1 +1 2 1 57 NESTED LOOPS 1 2477 1 +1 2 1 58 NESTED LOOPS ANTI 1 2475 1 +1 2 1 59 NESTED LOOPS 1 2474 1 +1 2 1 60 MERGE JOIN CARTESIAN 1 1244 1 +1 2 6 61 TABLE ACCESS BY INDEX ROWID FND_LOOKUP_VALUES 1 3 1 +1 2 6 62 INDEX RANGE SCAN FND_LOOKUP_VALUES_U2 1 2 1 +1 2 6 63 BUFFER SORT 1 1241 1 +1 6 6 2048 64 CONNECT BY PUMP 1 +1 2 2 65 TABLE ACCESS BY INDEX ROWID BATCHED MTL_RELATED_ITEMS 1 1230 1 +1 6 1 66 INDEX RANGE SCAN MTL_RELATED_ITEMS_N1 51249 123 1 +1 6 394K 67 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 410K 2 1 68 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 1 +1 1 1 69 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 1 +1 1 1 70 TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B 1 2 1 +1 1 1 71 INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1 1 1 1 +1 1 1 72 INDEX RANGE SCAN MTL_RELATED_ITEMS_U1 1 2 1 ================================================================================================================================================================================