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 : after_change
Execution Type : TEST EXECUTE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/18/2011 16:18:01
Last Updated : 11/18/2011 16:18:18
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
5 94rn6s4ba24wn 3 .167884 1.80847 .98885 57450


Report Details

SQL Details:
Object ID : 5
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 : .167884
cost : 19324
elapsed_time : 1.80847
cpu_time : .98885
user_io_time : .340845
buffer_gets : 57450
reads : 38114
writes : 0
io_interconnect_bytes : 312229888
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 : 89714
Plan Hash Value : 2949292326

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 1296 252720 19324 00:03:52
1 . SORT GROUP BY 1296 252720 19324 00:03:52
* 2 .. FILTER
* 3 ... HASH JOIN 1296 252720 16730 00:03:21
4 .... NESTED LOOPS
5 ..... NESTED LOOPS 1021 166423 1248 00:00:15
* 6 ...... HASH JOIN 210 28770 617 00:00:08
7 ....... VIEW index$_join$_004 274 4932 3 00:00:01
* 8 ........ HASH JOIN
9 ......... INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
10 ......... INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
* 11 ....... HASH JOIN 210 24990 614 00:00:08
12 ........ VIEW index$_join$_003 274 4932 3 00:00:01
* 13 ......... HASH JOIN
14 .......... INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
15 .......... INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
* 16 ........ TABLE ACCESS FULL OM_SHIPMENT 210 21210 611 00:00:08
* 17 ...... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 6 2 00:00:01
* 18 ..... TABLE ACCESS BY INDEX ROWID OM_SHIPMENT_LINE 5 130 3 00:00:01
19 .... VIEW VW_NSO_1 25271 808672 15481 00:03:06
20 ..... HASH UNIQUE 25271 685783 15481 00:03:06
21 ...... UNION-ALL
22 ....... HASH UNIQUE 5361 262689 12234 00:02:27
* 23 ........ FILTER
* 24 ......... HASH JOIN 5361 262689 12230 00:02:27
* 25 .......... TABLE ACCESS BY INDEX ROWID MFG_WIP 5361 128664 12169 00:02:27
* 26 ........... INDEX SKIP SCAN MFG_WIP_N2 57640 647 00:00:08
27 .......... INDEX FAST FULL SCAN OM_SHIPMENT_N2 21009 525225 60 00:00:01
28 ......... SORT AGGREGATE 1 9
* 29 .......... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 6 54 3 00:00:01
30 ......... SORT AGGREGATE 1 10
* 31 .......... INDEX RANGE SCAN MFG_WIP_LOT_QTY_N1 1 10 3 00:00:01
32 ....... MINUS
33 ........ SORT UNIQUE 19910 219010
34 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 21009 231099 42 00:00:01
35 ........ SORT UNIQUE 22676 204084
* 36 ......... INDEX FAST FULL SCAN MFG_WIP_N5 57640 518760 2972 00:00:36
* 37 ... FILTER
* 38 .... TABLE ACCESS BY INDEX ROWID MFG_WIP 1 19 4 00:00:01
* 39 ..... INDEX RANGE SCAN MFG_WIP_N5 1 3 00:00:01

Predicate Information (identified by operation id):