Plan hash value: 4142071936 -------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 3 |00:00:01.79 | 1023K| | | | | 1 | WINDOW SORT | | 1 | 4 | 3 |00:00:01.79 | 1023K| 2048 | 2048 | 2048 (0)| | 2 | VIEW | | 1 | 4 | 3 |00:00:03.57 | 1023K| | | | | 3 | UNION-ALL | | 1 | | 3 |00:00:03.57 | 1023K| | | | | 4 | VIEW | | 1 | 2 | 1 |00:00:00.01 | 112 | | | | |* 5 | FILTER | | 1 | | 1 |00:00:00.01 | 112 | | | | |* 6 | CONNECT BY WITH FILTERING | | 1 | | 1 |00:00:00.01 | 112 | 2048 | 2048 | 2048 (0)| | 7 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 99 | | | | | 8 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 98 | | | | | 9 | NESTED LOOPS ANTI | | 1 | 1 | 1 |00:00:00.01 | 95 | | | | | 10 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 92 | | | | | 11 | MERGE JOIN CARTESIAN | | 1 | 1 | 15 |00:00:00.01 | 78 | | | | | 12 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 1 | 3 |00:00:00.01 | 5 | | | | |* 13 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 1 | 1 | 3 |00:00:00.01 | 4 | | | | | 14 | BUFFER SORT | | 3 | 7 | 15 |00:00:00.01 | 73 | 2048 | 2048 | 2048 (0)| | 15 | TABLE ACCESS BY INDEX ROWID BATCHED| MTL_SYSTEM_ITEMS_B | 1 | 7 | 5 |00:00:00.01 | 73 | | | | |* 16 | INDEX SKIP SCAN | MTL_SYSTEM_ITEMS_B_N1 | 1 | 7 | 5 |00:00:00.01 | 68 | | | | | 17 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_RELATED_ITEMS | 15 | 1 | 1 |00:00:00.01 | 14 | | | | |* 18 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 15 | 1 | 1 |00:00:00.01 | 13 | | | | |* 19 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 1 | 409K| 0 |00:00:00.01 | 3 | | | | |* 20 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 1 | 1 |00:00:00.01 | 3 | | | | | 21 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 1 | 1 | 1 |00:00:00.01 | 1 | | | | | 22 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 13 | | | | | 23 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 13 | | | | | 24 | NESTED LOOPS ANTI | | 1 | 1 | 0 |00:00:00.01 | 13 | | | | | 25 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 13 | | | | | 26 | MERGE JOIN CARTESIAN | | 1 | 1 | 3 |00:00:00.01 | 5 | | | | | 27 | TABLE ACCESS BY INDEX ROWID | FND_LOOKUP_VALUES | 1 | 1 | 3 |00:00:00.01 | 5 | | | | |* 28 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 1 | 1 | 3 |00:00:00.01 | 4 | | | | | 29 | BUFFER SORT | | 3 | 1 | 3 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)| |* 30 | CONNECT BY PUMP | | 1 | | 1 |00:00:00.01 | 0 | | | | | 31 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_RELATED_ITEMS | 3 | 1 | 0 |00:00:00.01 | 8 | | | | |* 32 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 3 | 1 | 0 |00:00:00.01 | 8 | | | | |* 33 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 0 | 409K| 0 |00:00:00.01 | 0 | | | | | 34 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 0 | 1 | 0 |00:00:00.01 | 0 | | | | |* 35 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | | | 36 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 0 | 1 | 0 |00:00:00.01 | 0 | | | | |* 37 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | | |* 38 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 0 | 1 | 0 |00:00:00.01 | 0 | | | | | 39 | VIEW | | 1 | 2 | 2 |00:00:01.79 | 1023K| | | | |* 40 | FILTER | | 1 | | 2 |00:00:01.79 | 1023K| | | | |* 41 | CONNECT BY NO FILTERING WITH START-WITH | | 1 | | 2 |00:00:01.79 | 1023K| 26M| 1856K| 23M (0)| | 42 | NESTED LOOPS | | 1 | 10 | 196K|00:00:01.68 | 1023K| | | | | 43 | NESTED LOOPS | | 1 | 10 | 196K|00:00:01.32 | 875K| | | | | 44 | NESTED LOOPS | | 1 | 10 | 196K|00:00:00.91 | 492K| | | | | 45 | NESTED LOOPS | | 1 | 10 | 196K|00:00:00.18 | 6347 | | | | | 46 | TABLE ACCESS BY INDEX ROWID BATCHED | FND_LOOKUP_VALUES | 1 | 1 | 3 |00:00:00.01 | 5 | | | | |* 47 | INDEX RANGE SCAN | FND_LOOKUP_VALUES_U2 | 1 | 1 | 3 |00:00:00.01 | 4 | | | | | 48 | TABLE ACCESS BY INDEX ROWID BATCHED | MTL_RELATED_ITEMS | 3 | 51249 | 196K|00:00:00.15 | 6342 | | | | |* 49 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_N1 | 3 | 51249 | 196K|00:00:00.04 | 588 | | | | | 50 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 196K| 1 | 196K|00:00:00.62 | 486K| | | | |* 51 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 196K| 1 | 196K|00:00:00.32 | 360K| | | | |* 52 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 196K| 1 | 196K|00:00:00.33 | 382K| | | | | 53 | TABLE ACCESS BY INDEX ROWID | MTL_SYSTEM_ITEMS_B | 196K| 1 | 196K|00:00:00.27 | 147K| | | | |* 54 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 1 | 1 | 0 |00:00:00.01 | 3 | | | | |* 55 | INDEX RANGE SCAN | MTL_RELATED_ITEMS_U1 | 1 | 1 | 0 |00:00:00.01 | 3 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - filter(("MSIB1"."ORGANIZATION_ID"=85 AND "MSIB2"."ORGANIZATION_ID"=85 AND SYSDATE@!>=NVL("MRI"."START_DATE",SYSDATE@!-1) AND SYSDATE@!<=NVL("MRI"."END_DATE",SYSDATE@!+1) AND CONNECT_BY_ISCYCLE=0)) 6 - access("MRI"."INVENTORY_ITEM_ID"=PRIOR NULL AND PRIOR NULL='A') 13 - access("LOOKUP_TYPE"='MTL_RELATIONSHIP_TYPES' AND "MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212') filter(("MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212')) 16 - access("MSIB1"."SEGMENT1"='057825130B') filter("MSIB1"."SEGMENT1"='057825130B') 18 - access("MRI"."INVENTORY_ITEM_ID"="MSIB1"."INVENTORY_ITEM_ID" AND "MRI"."RELATIONSHIP_TYPE_ID"=TO_NUMBER("LOOKUP_CODE") AND "MRI"."ORGANIZATION_ID"="MSIB1"."ORGANIZATION_ID") filter(("MRI"."ORGANIZATION_ID"="MSIB1"."ORGANIZATION_ID" AND "MRI"."RELATIONSHIP_TYPE_ID"=TO_NUMBER("LOOKUP_CODE"))) 19 - access("MRI"."RELATED_ITEM_ID"="INVENTORY_ITEM_ID" AND "MRI"."INVENTORY_ITEM_ID"="RELATED_ITEM_ID") 20 - access("MRI"."RELATED_ITEM_ID"="MSIB2"."INVENTORY_ITEM_ID" AND "MRI"."ORGANIZATION_ID"="MSIB2"."ORGANIZATION_ID") 28 - access("LOOKUP_TYPE"='MTL_RELATIONSHIP_TYPES' AND "MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212') filter(("MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212')) 30 - filter(("connect$_by$_pump$_048"."PRIOR flv.meaning "<>'MNAO MKTG KITS' AND "connect$_by$_pump$_048"."PRIOR mri.attr_char1 "='A')) 32 - access("MRI"."INVENTORY_ITEM_ID"="connect$_by$_pump$_048"."PRIOR mri.related_item$POS4469" AND "MRI"."RELATIONSHIP_TYPE_ID"=TO_NUMBER("LOOKUP_CODE")) filter("MRI"."RELATIONSHIP_TYPE_ID"=TO_NUMBER("LOOKUP_CODE")) 33 - access("MRI"."RELATED_ITEM_ID"="INVENTORY_ITEM_ID" AND "MRI"."INVENTORY_ITEM_ID"="RELATED_ITEM_ID") 35 - access("MRI"."INVENTORY_ITEM_ID"="MSIB1"."INVENTORY_ITEM_ID" AND "MRI"."ORGANIZATION_ID"="MSIB1"."ORGANIZATION_ID") 37 - access("MRI"."RELATED_ITEM_ID"="MSIB2"."INVENTORY_ITEM_ID" AND "MRI"."ORGANIZATION_ID"="MSIB2"."ORGANIZATION_ID") 38 - access("INVENTORY_ITEM_ID"=:B1 AND "RELATED_ITEM_ID"=:B2) 40 - filter(("MSIB1"."ORGANIZATION_ID"=85 AND "MSIB2"."ORGANIZATION_ID"=85 AND SYSDATE@!>=NVL("MRI"."START_DATE",SYSDATE@!-1) AND SYSDATE@!<=NVL("MRI"."END_DATE",SYSDATE@!+1) AND CONNECT_BY_ISCYCLE=0)) 41 - access("MRI"."RELATED_ITEM_ID"=PRIOR NULL) filter(("MSIB2"."SEGMENT1"='057825130B' AND "MRI"."ATTR_CHAR1"='AN' AND IS NULL)) 47 - access("LOOKUP_TYPE"='MTL_RELATIONSHIP_TYPES' AND "MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212') filter(("MEANING" LIKE 'MNAO%' AND "ZD_EDITION_NAME"='V_20160910_0212')) 49 - access("MRI"."RELATIONSHIP_TYPE_ID"=TO_NUMBER("LOOKUP_CODE")) 51 - access("MRI"."INVENTORY_ITEM_ID"="MSIB1"."INVENTORY_ITEM_ID" AND "MRI"."ORGANIZATION_ID"="MSIB1"."ORGANIZATION_ID") 52 - access("MRI"."RELATED_ITEM_ID"="MSIB2"."INVENTORY_ITEM_ID" AND "MRI"."ORGANIZATION_ID"="MSIB2"."ORGANIZATION_ID") 54 - access("INVENTORY_ITEM_ID"=:B1 AND "RELATED_ITEM_ID"=:B2) 55 - access("INVENTORY_ITEM_ID"=:B1 AND "RELATED_ITEM_ID"=:B2) Statistics ----------------------------------------------------------- 179 CPU used by this session 179 CPU used when call started 178 DB time 5 Requests to/from client 1023242 consistent gets 738202 consistent gets examination 738202 consistent gets examination (fastpath) 1023242 consistent gets from cache 285040 consistent gets pin 285040 consistent gets pin (fastpath) 4 non-idle wait count 2 opened cursors cumulative 1 opened cursors current 1 pinned cursors current 1023242 session logical reads 5 user calls