Task Information:
|
Workload Information:
|
||||||||||||
|
|
|
|
object_id |
sql_id |
Executions |
Parse
time(s) |
Elapsed
time(s) |
CPU
time(s) |
Buffer
gets |
---|---|---|---|---|---|---|
3 | 94rn6s4ba24wn | 3 | .040664 | 8.630688 | 1.83172 | 135782 |
Object ID | : 3 |
---|---|
Schema Name | : GSNX |
SQL ID | : 94rn6s4ba24wn |
Execution Frequency | : 3 |
SQL Text | : /* SQL Analyze(8,1) */ SELECT sh.shipment_id shipmentId ,sh.shipment_no shipmentNo ,sh.ship_from_routing_code shipFromRoutingCode ,sh.ship_from_org_name shi pFromOrgName ,sh.ship_from_site_no shipFromSiteNo ,sh.ship_from_site shipFromSiteName ,sh.ship_from_site shipFromSite ,pb.party_abbrv_code buyerAbbrvCode ,sh.buyer_org_name buyer OrgName ,sh.buyer_site_no buyerSiteNo ,sh.buyer_site buyerSiteName , ps.party_abbrv_code supplierAbbrvCode ,sh.supplier_org_name supplierOrgName ,sh.supplier_site supplierSite Name ,sh.ship_to_org_name shipToOrgName ,sh.ship_to_site_no shipToSiteNo ,sh.ship_to_site shipToSiteName ,TO_CHAR(sh.ship_date, 'yyyy-MM-dd hh24:mi:ss') shipDate ,sl.lot_no lotNo , sh.buyer_org_id BuyerOrgId , sum ( sl.primary_shipped_qty )bulkQty FROM om_shipment sh ,om_shipment_line sl ,core_party pb ,core_party ps WHERE sh.shipment_id = sl.sh ipment_id AND pb.party_id = sh.buyer_org_id AND ps.party_id = sh.supplier_org_id AND sl.EXT_ATTRIBUTE1_VALUE is null AND UPPER(sh.ship_to_org_name) = :1 AND sh.shipment_no IN ( (SELECT DIST INCT h.shipment_no FROM om_shipment h, mfg_wip wip WHERE h.shipment_no = wip.shipment_no AND h.buyer_org_id = wip.buyer_id AND h.ship_to_org_id = wip.supplier_id AND wip.wip_type_code = 'RECEIPT' AND (SELECT SUM(l.primary_shipped_qty) FROM om_shipment_line l WHERE h.shipment_id = l.shipment_id) > (SELECT SUM(q.primary_uom_qty) FROM mfg_wip_lot_qty q WHERE wip.wip_id = q.wip_id) ) union all (SELECT DISTINCT shipment_no FROM om_shipment h minus select distinct shipment_no from mfg_wip WHERE wip_type_code = 'RECEIPT') ) AND NOT EXISTS (SELECT 1 FROM mfg_wip w WHE RE w.shipment_no = sh.shipment_no and w.supplier_id = sh.ship_to_org_id AND w.buyer_lot_no = sl.lot_no AND w.SHIPMENT_LINE_NO=sl.SHIPMENT_LINE_NO AND sl.EXT_ATTRIBUTE1_VALUE is null ) GROUP BY ( sh.shipment_id , sh.shipment_no , sh.ship_from_routing_code , sh.ship_from_org_name , sh.ship_from_site_no , sh.ship_from_site , sh.ship_from_site , pb.party_abbrv_co de , sh.buyer_org_name , sh.buyer_site_no , sh.buyer_site , ps.party_abbrv_code , sh.supplier_org_name , sh.supplier_site , sh.ship_to_org_name , sh.ship_to_si te_no , sh.ship_to_site , TO_CHAR(sh.ship_date, 'yyyy-MM-dd hh24:mi:ss') , sl.lot_no , sh.buyer_org_id ) ORDER BY shipmentNo ASC |
1 | - | (VARCHAR2): | UTAC |
parse_time | : .040664 |
---|---|
cost | : 11331 |
elapsed_time | : 8.630688 |
cpu_time | : 1.83172 |
user_io_time | : .808827 |
buffer_gets | : 135782 |
reads | : 117208 |
writes | : 0 |
io_interconnect_bytes | : 960167936 |
rows | : 60 |
|
Plan Id | : 89713 |
---|---|
Plan Hash Value | : 1328242299 |
Id | Operation | Name | Rows | Bytes | Cost | Time |
---|---|---|---|---|---|---|
0 | SELECT STATEMENT | 1064 | 207480 | 11331 | ||
1 | . SORT GROUP BY | 1064 | 207480 | 11331 | ||
* 2 | .. FILTER | |||||
* 3 | ... HASH JOIN | 1064 | 207480 | 11294 | ||
* 4 | .... TABLE ACCESS BY INDEX ROWID | OM_SHIPMENT_LINE | 5 | 130 | 3 | |
5 | ..... NESTED LOOPS | 1021 | 166423 | 905 | ||
* 6 | ...... HASH JOIN | 210 | 28770 | 275 | ||
7 | ....... TABLE ACCESS FULL | CORE_PARTY | 274 | 4932 | 3 | |
* 8 | ....... HASH JOIN | 210 | 24990 | 271 | ||
9 | ........ TABLE ACCESS FULL | CORE_PARTY | 274 | 4932 | 3 | |
* 10 | ........ TABLE ACCESS FULL | OM_SHIPMENT | 210 | 21210 | 267 | |
* 11 | ...... INDEX RANGE SCAN | OM_SHIPMENT_LINE_N1 | 6 | 2 | ||
12 | .... VIEW | VW_NSO_1 | 19912 | 637184 | 10385 | |
13 | ..... SORT UNIQUE | 19912 | 423284 | 10385 | ||
14 | ...... UNION-ALL | |||||
15 | ....... SORT UNIQUE | 2 | 190 | 8900 | ||
* 16 | ........ FILTER | |||||
17 | ......... SORT GROUP BY | 2 | 190 | 8900 | ||
18 | .......... NESTED LOOPS | 29 | 2755 | 8892 | ||
* 19 | ........... HASH JOIN | 25 | 1975 | 8842 | ||
* 20 | ............ TABLE ACCESS FULL | MFG_WIP | 5361 | 166191 | 8808 | |
21 | ............ INDEX FAST FULL SCAN | OM_SHIPMENT_N2 | 21009 | 1008432 | 27 | |
* 22 | ........... INDEX RANGE SCAN | MFG_WIP_LOT_QTY_N1 | 1 | 16 | 2 | |
23 | ......... SORT AGGREGATE | 1 | 9 | |||
* 24 | .......... INDEX RANGE SCAN | OM_SHIPMENT_LINE_N1 | 40 | 360 | 3 | |
25 | ....... MINUS | |||||
26 | ........ SORT UNIQUE | 19910 | 219010 | |||
27 | ......... INDEX FAST FULL SCAN | OM_SHIPMENT_UK1 | 21009 | 231099 | 19 | |
28 | ........ SORT UNIQUE | 22676 | 204084 | |||
* 29 | ......... INDEX FAST FULL SCAN | MFG_WIP_N5 | 57640 | 518760 | 1296 | |
* 30 | ... FILTER | |||||
* 31 | .... TABLE ACCESS BY INDEX ROWID | MFG_WIP | 1 | 19 | 4 | |
* 32 | ..... INDEX RANGE SCAN | MFG_WIP_N5 | 1 | 3 |