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 : convert_sqlset
Execution Type : CONVERT SQLSET
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/17/2011 17:40:31
Last Updated : 11/17/2011 17:40:37
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors :

Report Summary

Top 1 SQL Sorted by elapsed_time desc (Cumulative Statistics)
object_id sql_id Execs. Elapsed Time(s) CPU Time(s) Buffer Gets Plans
1 94rn6s4ba24wn 14 278.812006 13.460953 660333 5


Report Details

SQL Details:
Object ID : 1
Schema Name : GSNX
SQL ID : 94rn6s4ba24wn
Execution Frequency : 14
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

SQL Execution Statistics (average):
executions : 14
cost : 28129
elapsed_time : 19.915143
cpu_time : .961497
buffer_gets : 47166
reads : 29925
writes : 0
rows : 21
Note: time statistics are displayed in seconds

Plan Execution Statistics (average):
Name Value
plan hash value
schema name
executions
cost
elapsed_time
cpu_time
buffer_gets
reads
writes
rows
1269035755
GSNX
3
11392
22.259988
1.928707
99106
82678
0
20
2061833870
GSNX
3
19836
18.248295
.713892
32658
19237
0
20
2949292326
GSNX
3
18965
22.065135
.552249
24602
12329
0
20
3571634689
GSNX
2
19318
20.198959
.975852
39430
24297
0
30
4126038404
GSNX
3
71135
16.897944
.641569
37457
9206
0
20

Execution Plans (5):
Plan Hash Value : 1269035755

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 11392
1 . SORT GROUP BY 1127 219765 11392
2 .. FILTER
3 ... HASH JOIN 1127 219765 11353
4 .... HASH JOIN 1079 175877 945
5 ..... TABLE ACCESS FULL CORE_PARTY 274 4932 3
6 ..... HASH JOIN 1079 156455 941
7 ...... TABLE ACCESS FULL CORE_PARTY 274 4932 3
8 ...... HASH JOIN 1079 137033 937
9 ....... TABLE ACCESS FULL OM_SHIPMENT 221 22321 267
10 ....... TABLE ACCESS FULL OM_SHIPMENT_LINE 107715 2800590 667
11 .... VIEW VW_NSO_1 21002 672064 10404
12 ..... SORT UNIQUE 21002 425617 10404
13 ...... UNION-ALL
14 ....... SORT UNIQUE 2 190 8912
15 ........ FILTER
16 ......... SORT GROUP BY 2 190 8912
17 .......... NESTED LOOPS 25 2375 8904
18 ........... HASH JOIN 22 1738 8860
19 ............ INDEX FAST FULL SCAN OM_SHIPMENT_N2 22140 1062720 28
20 ............ TABLE ACCESS FULL MFG_WIP 4528 140368 8808
21 ........... INDEX RANGE SCAN MFG_WIP_LOT_QTY_N1 1 16 2
22 ......... SORT AGGREGATE 1 9
23 .......... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 40 360 3
24 ....... MINUS
25 ........ SORT UNIQUE 21000 231000
26 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 22140 243540 20
27 ........ SORT UNIQUE 21603 194427
28 ......... INDEX FAST FULL SCAN MFG_WIP_N5 48663 437967 1311
29 ... FILTER
30 .... TABLE ACCESS BY INDEX ROWID MFG_WIP 1 19 4
31 ..... INDEX RANGE SCAN MFG_WIP_N5 1 3

Bind Variables:
1 - (VARCHAR2): UTAC
Plan Hash Value : 2061833870

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19836
1 . SORT GROUP BY 1356 264420 19836 00:03:59
2 .. FILTER
3 ... HASH JOIN 1356 264420 17122 00:03:26
4 .... HASH JOIN 1079 175877 2152 00:00:26
5 ..... VIEW index$_join$_004 274 4932 3 00:00:01
6 ...... HASH JOIN
7 ....... INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
8 ....... INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
9 ..... HASH JOIN 1079 156455 2149 00:00:26
10 ...... VIEW index$_join$_003 274 4932 3 00:00:01
11 ....... HASH JOIN
12 ........ INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
13 ........ INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
14 ...... HASH JOIN 1079 137033 2146 00:00:26
15 ....... TABLE ACCESS FULL OM_SHIPMENT 221 22321 611 00:00:08
16 ....... TABLE ACCESS FULL OM_SHIPMENT_LINE 107715 2800590 1533 00:00:19
17 .... VIEW VW_NSO_1 26381 844192 14969 00:03:00
18 ..... HASH UNIQUE 26381 702236 14969 00:03:00
19 ...... UNION-ALL
20 ....... HASH UNIQUE 5381 263669 11679 00:02:21
21 ........ FILTER
22 ......... HASH JOIN 5381 263669 11608 00:02:20
23 .......... INDEX FAST FULL SCAN OM_SHIPMENT_N2 22140 553500 62 00:00:01
24 .......... TABLE ACCESS BY INDEX ROWID MFG_WIP 5381 129144 11545 00:02:19
25 ........... INDEX SKIP SCAN MFG_WIP_N2 57684 616 00:00:08
26 ......... SORT AGGREGATE 1 9
27 .......... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 6 54 3 00:00:01
28 ......... SORT AGGREGATE 1 10
29 .......... INDEX RANGE SCAN MFG_WIP_LOT_QTY_N1 1 10 3 00:00:01
30 ....... MINUS
31 ........ SORT UNIQUE 21000 231000
32 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 22140 243540 44 00:00:01
33 ........ SORT UNIQUE 23063 207567
34 ......... INDEX FAST FULL SCAN MFG_WIP_N5 57684 519156 3007 00:00:37
35 ... FILTER
36 .... TABLE ACCESS BY INDEX ROWID MFG_WIP 1 19 4 00:00:01
37 ..... INDEX RANGE SCAN MFG_WIP_N5 1 3 00:00:01

Bind Variables:
1 - (VARCHAR2): UTAC
Plan Hash Value : 2949292326

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 18965
1 . SORT GROUP BY 1356 264420 18965 00:03:48
2 .. FILTER
3 ... HASH JOIN 1356 264420 16251 00:03:16
4 .... NESTED LOOPS
5 ..... NESTED LOOPS 1079 175877 1281 00:00:16
6 ...... HASH JOIN 221 30277 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 221 26299 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 221 22321 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 26381 844192 14969 00:03:00
20 ..... HASH UNIQUE 26381 702236 14969 00:03:00
21 ...... UNION-ALL
22 ....... HASH UNIQUE 5381 263669 11679 00:02:21
23 ........ FILTER
24 ......... HASH JOIN 5381 263669 11608 00:02:20
25 .......... TABLE ACCESS BY INDEX ROWID MFG_WIP 5381 129144 11545 00:02:19
26 ........... INDEX SKIP SCAN MFG_WIP_N2 57684 616 00:00:08
27 .......... INDEX FAST FULL SCAN OM_SHIPMENT_N2 22140 553500 62 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 21000 231000
34 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 22140 243540 44 00:00:01
35 ........ SORT UNIQUE 23063 207567
36 ......... INDEX FAST FULL SCAN MFG_WIP_N5 57684 519156 3007 00:00:37
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

Bind Variables:
1 - (VARCHAR2): UTAC
Plan Hash Value : 3571634689

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 19318
1 . SORT GROUP BY 1133 220935 19318 00:03:52
2 .. FILTER
3 ... HASH JOIN 1133 220935 17050 00:03:25
4 .... HASH JOIN 1079 175877 2153 00:00:26
5 ..... VIEW index$_join$_004 274 4932 3 00:00:01
6 ...... HASH JOIN
7 ....... INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
8 ....... INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
9 ..... HASH JOIN 1079 156455 2149 00:00:26
10 ...... VIEW index$_join$_003 274 4932 3 00:00:01
11 ....... HASH JOIN
12 ........ INDEX FAST FULL SCAN CORE_PARTY_PK 274 4932 1 00:00:01
13 ........ INDEX FAST FULL SCAN CORE_PARTY_UK2 274 4932 1 00:00:01
14 ...... HASH JOIN 1079 137033 2146 00:00:26
15 ....... TABLE ACCESS FULL OM_SHIPMENT 221 22321 611 00:00:08
16 ....... TABLE ACCESS FULL OM_SHIPMENT_LINE 107715 2800590 1533 00:00:19
17 .... VIEW VW_NSO_1 21115 675680 14896 00:02:59
18 ..... SORT UNIQUE 21115 444202 14896 00:02:59
19 ...... UNION-ALL
20 ....... SORT UNIQUE 115 5635 11607 00:02:20
21 ........ FILTER
22 ......... HASH JOIN 115 5635 11603 00:02:20
23 .......... INDEX FAST FULL SCAN OM_SHIPMENT_N2 22140 553500 62 00:00:01
24 .......... TABLE ACCESS BY INDEX ROWID MFG_WIP 5381 129144 11539 00:02:19
25 ........... INDEX SKIP SCAN MFG_WIP_N2 57684 616 00:00:08
26 ......... SORT AGGREGATE 1 9
27 .......... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 40 360 3 00:00:01
28 ......... SORT AGGREGATE 1 10
29 .......... INDEX RANGE SCAN MFG_WIP_LOT_QTY_N1 2 20 3 00:00:01
30 ....... MINUS
31 ........ SORT UNIQUE 21000 231000
32 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 22140 243540 44 00:00:01
33 ........ SORT UNIQUE 23063 207567
34 ......... INDEX FAST FULL SCAN MFG_WIP_N5 57684 519156 3007 00:00:37
35 ... FILTER
36 .... TABLE ACCESS BY INDEX ROWID MFG_WIP 1 19 4 00:00:01
37 ..... INDEX RANGE SCAN MFG_WIP_N5 1 3 00:00:01

Bind Variables:
1 - (VARCHAR2): UTAC
Plan Hash Value : 4126038404

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT 71135
1 . SORT GROUP BY 1127 219765 71135
2 .. FILTER
3 ... HASH JOIN 1127 219765 71100
4 .... NESTED LOOPS 1079 175877 3087
5 ..... NESTED LOOPS 1079 156455 2008
6 ...... NESTED LOOPS 1079 137033 929
7 ....... TABLE ACCESS FULL OM_SHIPMENT 221 22321 266
8 ....... TABLE ACCESS BY INDEX ROWID OM_SHIPMENT_LINE 5 130 3
9 ........ INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 6 2
10 ...... TABLE ACCESS BY INDEX ROWID CORE_PARTY 1 18 1
11 ....... INDEX UNIQUE SCAN CORE_PARTY_PK 1
12 ..... TABLE ACCESS BY INDEX ROWID CORE_PARTY 1 18 1
13 ...... INDEX UNIQUE SCAN CORE_PARTY_PK 1
14 .... VIEW VW_NSO_1 21002 672064 68009
15 ..... SORT UNIQUE 21002 438757 68009
16 ...... UNION-ALL
17 ....... SORT UNIQUE 2 190 66503
18 ........ FILTER
19 ......... SORT GROUP BY 2 190 66503
20 .......... NESTED LOOPS 28 2660 66495
21 ........... NESTED LOOPS 24 1896 66447
22 ............ INDEX FAST FULL SCAN OM_SHIPMENT_N2 22140 1062720 27
23 ............ TABLE ACCESS BY INDEX ROWID MFG_WIP 1 31 3
24 ............. INDEX RANGE SCAN MFG_WIP_N5 1 2
25 ........... INDEX RANGE SCAN MFG_WIP_LOT_QTY_N1 1 16 2
26 ......... SORT AGGREGATE 1 9
27 .......... INDEX RANGE SCAN OM_SHIPMENT_LINE_N1 40 360 3
28 ....... MINUS
29 ........ SORT UNIQUE 21000 231000
30 ......... INDEX FAST FULL SCAN OM_SHIPMENT_UK1 22140 243540 19
31 ........ SORT UNIQUE 23063 207567
32 ......... INDEX FAST FULL SCAN MFG_WIP_N5 57684 519156 1310
33 ... FILTER
34 .... TABLE ACCESS BY INDEX ROWID MFG_WIP 1 19 4
35 ..... INDEX RANGE SCAN MFG_WIP_N5 1 3

Bind Variables:
1 - (VARCHAR2): UTAC