SPA sample compare report

 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 CategorySQL CountPlan Change Count
Overall11
Improved11

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
694rn6s4ba24wn79.05%38630688180847079.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_time79.05%8.6306881.8084779.05%
parse_time-312.86%.040664.167884-312.86%
cpu_time46.02%1.83172.9888546.02%
user_io_time57.86%.808827.34084557.86%
buffer_gets57.69%1357825745057.69%
cost-70.54%1133119324-70.54%
reads67.48%1172083811467.48%
writes0%000%
io_interconnect_bytes67.48%96016793631222988867.48%
rows6060
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

IdOperationNameRowsBytesCostTime
0SELECT STATEMENT106420748011331
1. SORT GROUP BY106420748011331
* 2.. FILTER
* 3... HASH JOIN106420748011294
* 4.... TABLE ACCESS BY INDEX ROWIDOM_SHIPMENT_LINE51303
5..... NESTED LOOPS1021166423905
* 6...... HASH JOIN21028770275
7....... TABLE ACCESS FULLCORE_PARTY27449323
* 8....... HASH JOIN21024990271
9........ TABLE ACCESS FULLCORE_PARTY27449323
* 10........ TABLE ACCESS FULLOM_SHIPMENT21021210267
* 11...... INDEX RANGE SCANOM_SHIPMENT_LINE_N162
12.... VIEWVW_NSO_11991263718410385
13..... SORT UNIQUE1991242328410385
14...... UNION-ALL
15....... SORT UNIQUE21908900
* 16........ FILTER
17......... SORT GROUP BY21908900
18.......... NESTED LOOPS2927558892
* 19........... HASH JOIN2519758842
* 20............ TABLE ACCESS FULLMFG_WIP53611661918808
21............ INDEX FAST FULL SCANOM_SHIPMENT_N221009100843227
* 22........... INDEX RANGE SCANMFG_WIP_LOT_QTY_N11162
23......... SORT AGGREGATE19
* 24.......... INDEX RANGE SCANOM_SHIPMENT_LINE_N1403603
25....... MINUS
26........ SORT UNIQUE19910219010
27......... INDEX FAST FULL SCANOM_SHIPMENT_UK12100923109919
28........ SORT UNIQUE22676204084
* 29......... INDEX FAST FULL SCANMFG_WIP_N5576405187601296
* 30... FILTER
* 31.... TABLE ACCESS BY INDEX ROWIDMFG_WIP1194
* 32..... INDEX RANGE SCANMFG_WIP_N513

Predicate Information (identified by operation id):
  • 2 - filter( NOT EXISTS (SELECT 0 FROM "MFG_WIP" "W" WHERE :B1 IS NULL AND "W"."BUYER_LOT_NO"=:B2 AND "W"."SHIPMENT_NO"=:B3 AND "W"."SHIPMENT_LINE_NO"=:B4 AND "W"."SUPPLIER_ID"=:B5))
  • 3 - access("SH"."SHIPMENT_NO"="SHIPMENT_NO")
  • 4 - filter("SL"."EXT_ATTRIBUTE1_VALUE" IS NULL)
  • 6 - access("PS"."PARTY_ID"="SH"."SUPPLIER_ORG_ID")
  • 8 - access("PB"."PARTY_ID"="SH"."BUYER_ORG_ID")
  • 10 - filter(UPPER("SH"."SHIP_TO_ORG_NAME")=:1)
  • 11 - access("SH"."SHIPMENT_ID"="SL"."SHIPMENT_ID")
  • 16 - filter( (SELECT SUM("L"."PRIMARY_SHIPPED_QTY") FROM "OM_SHIPMENT_LINE" "L" WHERE "L"."SHIPMENT_ID"=:B1)>SUM("Q"."PRIMARY_UOM_QTY"))
  • 19 - access("H"."SHIPMENT_NO"="WIP"."SHIPMENT_NO" AND "H"."BUYER_ORG_ID"="WIP"."BUYER_ID" AND "H"."SHIP_TO_ORG_ID"="WIP"."SUPPLIER_ID")
  • 20 - filter("WIP"."SHIPMENT_NO" IS NOT NULL AND "WIP"."WIP_TYPE_CODE"='RECEIPT')
  • 22 - access("WIP"."WIP_ID"="Q"."WIP_ID")
  • 24 - access("L"."SHIPMENT_ID"=:B1)
  • 29 - filter("WIP_TYPE_CODE"='RECEIPT')
  • 30 - filter(:B1 IS NULL)
  • 31 - filter("W"."SHIPMENT_LINE_NO"=:B1)
  • 32 - access("W"."SHIPMENT_NO"=:B1 AND "W"."BUYER_LOT_NO"=:B2 AND "W"."SUPPLIER_ID"=:B3)
  • 32 - filter("W"."SUPPLIER_ID"=:B1)

Execution Plan After Change:
Plan Id: 89714
Plan Hash Value: 2949292326

IdOperationNameRowsBytesCostTime
0SELECT STATEMENT12962527201932400:03:52
1. SORT GROUP BY12962527201932400:03:52
* 2.. FILTER
* 3... HASH JOIN12962527201673000:03:21
4.... NESTED LOOPS
5..... NESTED LOOPS1021166423124800:00:15
* 6...... HASH JOIN2102877061700:00:08
7....... VIEWindex$_join$_0042744932300:00:01
* 8........ HASH JOIN
9......... INDEX FAST FULL SCANCORE_PARTY_PK2744932100:00:01
10......... INDEX FAST FULL SCANCORE_PARTY_UK22744932100:00:01
* 11....... HASH JOIN2102499061400:00:08
12........ VIEWindex$_join$_0032744932300:00:01
* 13......... HASH JOIN
14.......... INDEX FAST FULL SCANCORE_PARTY_PK2744932100:00:01
15.......... INDEX FAST FULL SCANCORE_PARTY_UK22744932100:00:01
* 16........ TABLE ACCESS FULLOM_SHIPMENT2102121061100:00:08
* 17...... INDEX RANGE SCANOM_SHIPMENT_LINE_N16200:00:01
* 18..... TABLE ACCESS BY INDEX ROWIDOM_SHIPMENT_LINE5130300:00:01
19.... VIEWVW_NSO_1252718086721548100:03:06
20..... HASH UNIQUE252716857831548100:03:06
21...... UNION-ALL
22....... HASH UNIQUE53612626891223400:02:27
* 23........ FILTER
* 24......... HASH JOIN53612626891223000:02:27
* 25.......... TABLE ACCESS BY INDEX ROWIDMFG_WIP53611286641216900:02:27
* 26........... INDEX SKIP SCANMFG_WIP_N25764064700:00:08
27.......... INDEX FAST FULL SCANOM_SHIPMENT_N2210095252256000:00:01
28......... SORT AGGREGATE19
* 29.......... INDEX RANGE SCANOM_SHIPMENT_LINE_N1654300:00:01
30......... SORT AGGREGATE110
* 31.......... INDEX RANGE SCANMFG_WIP_LOT_QTY_N1110300:00:01
32....... MINUS
33........ SORT UNIQUE19910219010
34......... INDEX FAST FULL SCANOM_SHIPMENT_UK1210092310994200:00:01
35........ SORT UNIQUE22676204084
* 36......... INDEX FAST FULL SCANMFG_WIP_N557640518760297200:00:36
* 37... FILTER
* 38.... TABLE ACCESS BY INDEX ROWIDMFG_WIP119400:00:01
* 39..... INDEX RANGE SCANMFG_WIP_N51300:00:01

Predicate Information (identified by operation id):
  • 2 - filter( NOT EXISTS (SELECT 0 FROM "MFG_WIP" "W" WHERE :B1 IS NULL AND "W"."BUYER_LOT_NO"=:B2 AND "W"."SHIPMENT_NO"=:B3 AND "W"."SHIPMENT_LINE_NO"=:B4 AND "W"."SUPPLIER_ID"=:B5))
  • 3 - access("SH"."SHIPMENT_NO"="SHIPMENT_NO")
  • 6 - access("PS"."PARTY_ID"="SH"."SUPPLIER_ORG_ID")
  • 8 - access(ROWID=ROWID)
  • 11 - access("PB"."PARTY_ID"="SH"."BUYER_ORG_ID")
  • 13 - access(ROWID=ROWID)
  • 16 - filter(UPPER("SH"."SHIP_TO_ORG_NAME")=:1)
  • 17 - access("SH"."SHIPMENT_ID"="SL"."SHIPMENT_ID")
  • 18 - filter("SL"."EXT_ATTRIBUTE1_VALUE" IS NULL)
  • 23 - filter( (SELECT SUM("L"."PRIMARY_SHIPPED_QTY") FROM "OM_SHIPMENT_LINE" "L" WHERE "L"."SHIPMENT_ID"=:B1)> (SELECT SUM("Q"."PRIMARY_UOM_QTY") FROM "MFG_WIP_LOT_QTY" "Q" WHERE "Q"."WIP_ID"=:B2))
  • 24 - access("H"."SHIPMENT_NO"="WIP"."SHIPMENT_NO" AND "H"."BUYER_ORG_ID"="WIP"."BUYER_ID" AND "H"."SHIP_TO_ORG_ID"="WIP"."SUPPLIER_ID")
  • 25 - filter("WIP"."SHIPMENT_NO" IS NOT NULL)
  • 26 - access("WIP"."WIP_TYPE_CODE"='RECEIPT')
  • 26 - filter("WIP"."WIP_TYPE_CODE"='RECEIPT')
  • 29 - access("L"."SHIPMENT_ID"=:B1)
  • 31 - access("Q"."WIP_ID"=:B1)
  • 36 - filter("WIP_TYPE_CODE"='RECEIPT')
  • 37 - filter(:B1 IS NULL)
  • 38 - filter("W"."SHIPMENT_LINE_NO"=:B1)
  • 39 - access("W"."SHIPMENT_NO"=:B1 AND "W"."BUYER_LOT_NO"=:B2 AND "W"."SUPPLIER_ID"=:B3)
  • 39 - filter("W"."SUPPLIER_ID"=:B1)

Comments

Post a Comment

Popular posts from this blog

How To Purge Optimizer Statistics Advisor Old Records From 12.2 Onwards (Doc ID 2660128.1)

  In this Document Goal Solution References APPLIES TO: Oracle Database - Enterprise Edition - Version 12.2.0.1 and later Oracle Cloud Infrastructure - Database Service - Version N/A and later Information in this document applies to any platform. GOAL Goal of this document is to provide the method to purge the old records of Optimizer Statistics Advisor Task namely AUTO_STATS_ADVISOR_TASK that consumes huge SYSAUX space. Huge no.of old records retained in WRI$_ADV_OBJECTS for AUTO_STATS_ADVISOR_TASK or INDIVIDUAL_STATS_ADVISOR_TASK incurs heavy SYSAUX space. AUTO_STATS_ADVISOR_TASK is meant for Automatic Statistics Advisor task while INDIVIDUAL_STATS_ADVISOR_TASK is for Manual Statistics Advisor task.  DBA_ADVISOR_PARAMETERS displays all advisor task parameters and their current values in the database. There is a parameter name called EXECUTION_DAYS_TO_EXPIRE. This parameter is set in no.of days. Executions older than the value(no.of days) set would be purged automaticall...
Read more

Oracle session snapper

-- use dbms_session.sleep instead of dbms_lock on oracle 18c or higher -- last column  is sid  -- scipt_name   stat_to_collect   secods_of_snap  how_many_snap   sessions_to_snap -- @snapper all 100 1 lgdr@* -- @snapper all 100 1 sqlid@* -- @snapper all 190 1 all -- @snapper all 190 1 584 -- @snapper all 30 3 584 -- @snapper all 5 1 869 -- @snapper all 30 1 584 -- @snapper ash 5 1 144 -- @snapper ash=sql_id+event+wait_class+blocking_session+p2+p3 5 1 144 -- @snapper ash 5 1 156 -- @snapper ash 190  1 all -- @snapper ash 5 1 all -- @snapper ash=sid+event+wait_class 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id 5 1 all -- @snapper ash=sid+event+wait_class,ash1=plsql_object_id+plsql_subprogram_id+sql_id,ash2=program+module+action 5 1 all -- @snapper ash=event+wait_class,stats,gather=ts,tinclude=CPU,sinclude=parse 5 1 all -- @snapper ash=sid+event+wait_class,ash1=sid+sqlid+module,stats,gather...
Read more

Oracle Materialized View In-Depth and Materialized View refresh issues in 19c

   We    had  situation demanding  tuning of  MV  refresh and  options  like  TUNE_MVIEW  and Atomic_Refresh was explored . Hence  Documenting materialized view sample commands  and views in this  blog .     After migration if we are  facing slowness  in  Materialized View refresh , we can explore to re-create  Materialized View   Since  main topic for this blog   was MV issues  in 19c ,  posting that topic   first in line below  We also   have Real-Time Materialized Views  to explore .  To Note :  1) We can  create Mv  on ON PREBUILT TABLE  2) We can create Index on Mv  3) We can  gather stats on mv  just like other table .  4) Mv can be REFRESH_MODE ON COMMIT / REFRESH ON DEMAND. 5) Mv  REFRESH_METHOD can be  Complete / FAST or FORCE . A FORCE attempts a FAST...
Read more