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 : compare_performance
Execution Type : COMPARE PERFORMANCE
Description :
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 11/18/2011 16:18:54
Last Updated : 11/18/2011 16:18:57
Global Time Limit : UNLIMITED
Per-SQL Time Limit : UNUSED
Number of Errors : 0

Analysis Information:
Before Change Execution:
After Change Execution:
Execution Name : before_change
Execution Type : TEST EXECUTE
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
Execution Name : after_change
Execution Type : TEST EXECUTE
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
Comparison Metric: ELAPSED_TIME
Workload Impact Threshold: 1%
SQL Impact Threshold: 1%

Report Summary

Projected Workload Change Impact:
Overall Impact : 79.05%
Improvement Impact : 79.05%
Regression Impact : 0%

SQL Statement Count
SQL Category SQL Count Plan Change Count
Overall 1 1
Improved 1 1

Top 1 SQL Sorted by Absolute Value of Change Impact on the Workload

object_id
sql_id 		Impact on
Workload 		Execution
Frequency 		Metric
Before 		Metric
After 		Impact
on SQL 		Plan
Change
6 94rn6s4ba24wn 79.05% 3 8630688 1808470 79.05% y
Note: time statistics are displayed in microseconds


Report Details

SQL Details:
Object ID : 6
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:

Stat Name 		Impact on
Workload 		Value
Before 		Value
After 		Impact
on SQL
elapsed_time 79.05% 8.630688 1.80847 79.05%
parse_time -312.86% .040664 .167884 -312.86%
cpu_time 46.02% 1.83172 .98885 46.02%
user_io_time 57.86% .808827 .340845 57.86%
buffer_gets 57.69% 135782 57450 57.69%
cost -70.54% 11331 19324 -70.54%
reads 67.48% 117208 38114 67.48%
writes 0% 0 0 0%
io_interconnect_bytes 67.48% 960167936 312229888 67.48%
rows 60 60
Note: time statistics are displayed in seconds

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

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


Findings (3):
  1. The performance of this SQL has improved.
  2. The structure of the SQL execution plan has changed.
  3. The structure of the SQL plan in execution 'before_change' is different than its corresponding plan which is stored in the SQL Tuning Set.


Execution Plan Before Change:
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):

Execution Plan After Change:
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):