Dear DBA Friends,
DB version - 19.10 on AIX.
I am trying to improve the run-time of SQL. (SQL & it's execution plan are below).
Execution plan says step - 13 is the longest running step. The cardinality estimates are way off.
It's not using index.. due to null/not in operators?
We locked a SQLT profile and able to get hardly acceptable run-time..
Can you please guide on the tuning approach...or let me know if I can provide further info.
SELECT
T9.CONFLICT_ID,
T9.LAST_UPD,
T9.CREATED,
T9.LAST_UPD_BY,
T9.CREATED_BY,
T9.MODIFICATION_NUM,
T9.ROW_ID,
T9.AGREE_ID,
T6.NAME,
T9.BILL_ACCNT_ID,
T5.DEST_CITY,
T8.NAME,
T5.DEST_ZIPCODE,
T4.X_SNI_DISTANCE,
T5.END_REQ_DELV_TS,
T5.END_REQ_PICK_TS,
T9.X_SNI_INVOICE_HLD_FLG,
T9.ORDER_ID,
T4.ORDER_NUM,
T4.REV_NUM,
T5.ORIGIN_CITY,
T7.NAME,
T5.ORIGIN_ZIPCODE,
T1.PART_NUM,
T1.PROD_CD,
T5.ST_REQ_DELV_TS,
T5.ST_REQ_PICK_TS,
T2.BU_ID,
T5.DEST_COUNTRY,
T5.DEST_STATE,
T5.DIST_UOM_CD,
T5.LINE_OF_BUS_CD,
T5.MILEAGE_PKG_CD,
T4.STATUS_CD,
T5.ORIGIN_COUNTRY,
T5.ORIGIN_STATE,
T3.X_SNI_RATE_DATE,
T9.X_SNI_RATING_STATUS,
:1
FROM
CRM.S_PROD_INT T1,
CRM.S_ORDER T2,
CRM.S_DOC_AGREE T3,
CRM.S_ORDER T4,
CRM.S_ORDER_FMX T5,
CRM.S_ORG_EXT T6,
CRM.S_LOCATION T7,
CRM.S_LOCATION T8,
CRM.S_ORDER_ITEM T9
WHERE
T9.AGREE_ID = T3.ROW_ID (+) AND
T9.BILL_ACCNT_ID = T6.PAR_ROW_ID (+) AND
T5.DEST_LOC_ID = T8.ROW_ID (+) AND
T5.ORIGIN_LOC_ID = T7.ROW_ID (+) AND
T9.ORDER_ID = T4.ROW_ID AND
T9.ORDER_ID = T5.PAR_ROW_ID (+) AND
T9.PROD_ID = T1.ROW_ID (+) AND
T9.ORDER_ID = T2.ROW_ID (+) AND
(T4.STATUS_CD IN ('Complete','Cancelled') AND T4.ACTIVE_FLG = 'Y' AND (T9.X_SNI_RATING_STATUS NOT IN ( 'Rated' ) AND T9.X_SNI_RATING_STATUS NOT IN ( 'Execution Hold' ) OR T9.X_SNI_RATING_STATUS IS NULL OR T9.X_SNI_RATING_STATUS IS NULL )) AND
(T9.AGREE_ID = :2)
ORDER BY
T4.ORDER_NUM
Plan hash value: 716566292
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 2020 (100)| 50 |00:02:55.40 | 2362K| 246K| | | |
| 1 | SORT ORDER BY | | 1 | 6520 | 2814K| 2912K| 2020 (1)| 50 |00:02:55.40 | 2362K| 246K| 478K| 448K| 424K (0)|
| 2 | NESTED LOOPS OUTER | | 1 | 6520 | 2814K| | 1119 (1)| 1020 |00:02:55.39 | 2362K| 246K| | | |
| 3 | NESTED LOOPS OUTER | | 1 | 6520 | 2693K| | 924 (1)| 1020 |00:02:55.37 | 2358K| 246K| | | |
| 4 | NESTED LOOPS OUTER | | 1 | 6520 | 2483K| | 794 (1)| 1020 |00:02:55.36 | 2355K| 246K| | | |
| 5 | NESTED LOOPS OUTER | | 1 | 6520 | 2273K| | 664 (1)| 1020 |00:02:55.33 | 2352K| 246K| | | |
| 6 | NESTED LOOPS OUTER | | 1 | 6520 | 1388K| | 403 (1)| 1020 |00:02:54.69 | 2348K| 245K| | | |
| 7 | NESTED LOOPS | | 1 | 6520 | 1184K| | 275 (1)| 1020 |00:02:54.67 | 2348K| 245K| | | |
|* 8 | HASH JOIN RIGHT OUTER | | 1 | 6520 | 929K| | 79 (0)| 352K|00:02:39.26 | 700K| 235K| 1298K| 1298K| 1646K (0)|
| 9 | TABLE ACCESS FULL | S_PROD_INT | 1 | 597 | 14925 | | 45 (0)| 597 |00:00:00.01 | 1224 | 0 | | | |
|* 10 | HASH JOIN RIGHT OUTER | | 1 | 6520 | 770K| | 34 (0)| 352K|00:02:38.41 | 699K| 235K| 1265K| 1265K| 740K (0)|
| 11 | TABLE ACCESS BY INDEX ROWID| S_DOC_AGREE | 1 | 1 | 29 | | 1 (0)| 1 |00:00:00.01 | 3 | 0 | | | |
|* 12 | INDEX UNIQUE SCAN | S_DOC_AGREE_P1 | 1 | 1 | | | 1 (0)| 1 |00:00:00.01 | 2 | 0 | | | |
|* 13 | TABLE ACCESS BY INDEX ROWID| S_ORDER_ITEM | 1 | 6520 | 585K| | 33 (0)| 352K|00:02:37.45 | 699K| 235K| | | |
|* 14 | INDEX RANGE SCAN | S_ORDER_ITEM_F52 | 1 | 7652 | | | 1 (0)| 399K|00:00:03.29 | 3372 | 2591 | | | |
|* 15 | TABLE ACCESS BY INDEX ROWID | S_ORDER | 352K| 1 | 40 | | 1 (0)| 1020 |00:00:15.08 | 1648K| 10093 | | | |
|* 16 | INDEX UNIQUE SCAN | S_ORDER_P1 | 352K| 1 | | | 1 (0)| 352K|00:00:10.91 | 1052K| 10093 | | | |
| 17 | TABLE ACCESS BY INDEX ROWID | S_ORG_EXT | 1020 | 1 | 32 | | 1 (0)| 1020 |00:00:00.01 | 15 | 0 | | | |
|* 18 | INDEX UNIQUE SCAN | S_ORG_EXT_U3 | 1020 | 1 | | | 1 (0)| 1020 |00:00:00.01 | 14 | 0 | | | |
| 19 | TABLE ACCESS BY INDEX ROWID | S_ORDER_FMX | 1020 | 1 | 139 | | 1 (0)| 1020 |00:00:00.64 | 3592 | 972 | | | |
|* 20 | INDEX RANGE SCAN | S_ORDER_FMX_M5_X | 1020 | 1 | | | 1 (0)| 1020 |00:00:00.43 | 3024 | 652 | | | |
| 21 | TABLE ACCESS BY INDEX ROWID | S_LOCATION | 1020 | 1 | 33 | | 1 (0)| 1020 |00:00:00.02 | 2993 | 0 | | | |
|* 22 | INDEX UNIQUE SCAN | S_LOCATION_P1 | 1020 | 1 | | | 1 (0)| 1020 |00:00:00.01 | 1973 | 0 | | | |
| 23 | TABLE ACCESS BY INDEX ROWID | S_LOCATION | 1020 | 1 | 33 | | 1 (0)| 1020 |00:00:00.02 | 3066 | 0 | | | |
|* 24 | INDEX UNIQUE SCAN | S_LOCATION_P1 | 1020 | 1 | | | 1 (0)| 1020 |00:00:00.01 | 2046 | 0 | | | |
| 25 | TABLE ACCESS BY INDEX ROWID | S_ORDER | 1020 | 1 | 19 | | 1 (0)| 1020 |00:00:00.01 | 3586 | 0 | | | |
|* 26 | INDEX UNIQUE SCAN | S_ORDER_P1 | 1020 | 1 | | | 1 (0)| 1020 |00:00:00.01 | 2985 | 0 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - access("T9"."PROD_ID"="T1"."ROW_ID")
10 - access("T9"."AGREE_ID"="T3"."ROW_ID")
12 - access("T3"."ROW_ID"=:2)
13 - filter(("T9"."X_SNI_RATING_STATUS" IS NULL OR ("T9"."X_SNI_RATING_STATUS"<>'Rated' AND "T9"."X_SNI_RATING_STATUS"<>'Execution Hold')))
14 - access("T9"."AGREE_ID"=:2)
15 - filter((INTERNAL_FUNCTION("T4"."STATUS_CD") AND "T4"."ACTIVE_FLG"='Y'))
16 - access("T9"."ORDER_ID"="T4"."ROW_ID")
18 - access("T9"."BILL_ACCNT_ID"="T6"."PAR_ROW_ID")
20 - access("T9"."ORDER_ID"="T5"."PAR_ROW_ID")
22 - access("T5"."ORIGIN_LOC_ID"="T7"."ROW_ID")
24 - access("T5"."DEST_LOC_ID"="T8"."ROW_ID")
26 - access("T9"."ORDER_ID"="T2"."ROW_ID")
Note
-----
- SQL profile coe_gyuxwwggjww1w_716566292 used for this statement