General Information

Task Information:
Workload Information:
Task Name : Task01
Task Owner : SYS
Description : Task for sql_id 94rn6s4ba24wn
SQL Tuning Set Name : STS01
SQL Tuning Set Owner : SYS
Total SQL Statement Count : 1

Execution Information:
Execution Name : before_change
Execution Type : TEST EXECUTE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/18/2011 16:16:39
Last Updated : 11/18/2011 16:17:11
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Report Summary

Top 1 SQL Sorted by elapsed_time desc

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



Report Details


SQL Details:

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

Bind Variables:
1 - (VARCHAR2): UTAC

Execution Statistics:
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
Note: time statistics are displayed in seconds

Notes:
  1. The statement was first executed to warm the buffer cache.
  2. Statistics shown were from the second execution.


Execution Plan:
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

Predicate Information (identified by operation id):