Hi Team,
The below query is taking 40 minutes in Production environment,
INSERT
/*+ APPEND PARALLEL(24) NOLOGGING PQ_DISTRIBUTE(REP_WORP_WO_DETAIL HASH,HASH) */
INTO TEMP
(
DEVICE_POSITION ,
REGION ,
SERVICE_AREA ,
SITE ,
ACTUAL_DT_REPORT_DT ,
ACTUAL_DT_SCHD_FIN_DT ,
ACTUAL_DT_SCHD_START_DT ,
WORK_ORDER_STATUS ,
ACTUAL_DT_TAR_FIN_DT ,
ACTUAL_DT_TAR_ST_DT ,
LONG_DESCRIPTION ,
PRIORITY_JUSTIFICATION ,
WORK_ORDER_DESCRIPTION ,
WORK_ORDER_PRIORTY ,
WORK_ORDER ,
ACTIVITY_CODE ,
WORK_TYPE_CODE ,
WORK_ORDER_STATUS_COMP_DT ,
CALC_PRIORITY_RISK ,
WORK_ORDER_STATUS_1 ,
VALID_FROM ,
VALID_TO ,
AMIS_WORK_ORDER_ID ,
PRIORITY_RISK ,
WORK_ORDER_COUNT ,
OUTAGE_REQUIRED ,
AMIS_WORK_ORDER_STATUS_ID ,
ASSET_TYPE_DESCRIPTION ,
ASSET_NUMBER ,
ASSET_TYPE ,
SNAPSHOT_DATE ,
DW_MODIFIED_BY ,
ODI_SESSION_ID ,
LATEST_FLAG ,
ACTUAL_START_DATE ,
ACTUAL_FINISH_DATE
)
SELECT
/*+ LEADING(F_AMIS_WORK_ORDER) PARALLEL(24) INDEX(F_AMIS_WORK_ORDER F_AMIS_WORK_ORDER_IDX10 F_AMIS_WORK_ORDER_IDX11 F_AMIS_WORK_ORDER_IDX12 F_AMIS_WORK_ORDER_IDX15 F_AMIS_WORK_ORDER_IDX16 F_AMIS_WORK_ORDER_IDX17 F_AMIS_WORK_ORDER_IDX18 F_AMIS_WORK_ORDER_IDX19 F_AMIS_WORK_ORDER_IDX20 F_AMIS_WORK_ORDER_IDX31 F_AMIS_WORK_ORDER_IDX8 F_AMIS_WORK_ORDER_IDX9) USE_HASH(F_AMIS_WORK_ORDER) USE_HASH(D_AMIS_WORK_ORDER) INDEX(D_DATE DDTE_PK) INDEX(D_AMIS_LD D_AMIS_LD_PK) INDEX(D_AMIS_WORK_ORDER_STATUS D_AMIS_WORK_ORDER_STATUS_PK) INDEX(D_AMIS_WORK_ORDER D_AMIS_WORK_ORDER_IDX3 D_AMIS_WORK_ORDER_PK D_AMIS_WORK_ORD_WORK_ORDER_UK) INDEX(D_AMIS_ASSET D_AMIS_ASSET_PK) INDEX(D_AMIS_LOCATION D_AMIS_LOCATION_PK) INDEX(D_AMIS_WORK_TYPE_ACTIVITY D_AMIS_WORK_TYPE_PK) INDEX(D_AMIS_ASSET_TYPE D_AMIS_ASSET_TYPE_PK) INDEX(D_AMIS_ASSET D_AMIS_ASSET_PK) */
D_AMIS_LOCATION.DEVICE_POSITION ,
(CASE WHEN D_AMIS_LOCATION.REGION = 'NNI-STNS' THEN 'NNI'
WHEN D_AMIS_LOCATION.REGION = 'NNI-LINES' THEN 'NNI'
WHEN D_AMIS_LOCATION.REGION = 'SNI-STNS' THEN 'SNI'
WHEN D_AMIS_LOCATION.REGION = 'SNI-LINES' THEN 'SNI'
WHEN D_AMIS_LOCATION.REGION = 'SI-STNS' THEN 'SI'
WHEN D_AMIS_LOCATION.REGION = 'SI-LINES' THEN 'SI'
WHEN D_AMIS_LOCATION.REGION = 'HVDC' THEN 'HVDC'
ELSE ''
END) ,
D_AMIS_LOCATION.SERVICE_AREA ,
D_AMIS_LOCATION.SITE ,
D_DATE5.ACTUAL_DATE ,
D_DATE4.ACTUAL_DATE ,
D_DATE3.ACTUAL_DATE ,
D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS ,
D_DATE1.ACTUAL_DATE ,
D_DATE.ACTUAL_DATE ,
(DBMS_LOB.SUBSTR(D_AMIS_LD.LONG_DESCRIPTION,4000,1)) ,
D_AMIS_WORK_ORDER.PRIORITY_JUSTIFICATION ,
D_AMIS_WORK_ORDER.WORK_ORDER_DESCRIPTION ,
D_AMIS_WORK_ORDER.WORK_ORDER_PRIORITY ,
D_AMIS_WORK_ORDER.WORK_ORDER ,
D_AMIS_WORK_TYPE_ACTIVITY.ACTIVITY_CODE ,
D_AMIS_WORK_TYPE_ACTIVITY.WORK_TYPE_CODE ,
(CASE WHEN D_DATE2.ACTUAL_DATE IS NULL THEN 'Active' ELSE 'Completed' END) ,
(CASE
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK IS NULL
OR NOT REGEXP_LIKE (D_AMIS_WORK_ORDER.PRIORITY_RISK,
'^[+-]?(\d+(\.\d*)?|\.\d+)$')
THEN
'UNRATED'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK <= 0
THEN
'UNRATED'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 0
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 100
THEN
'0-100'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 100
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 200
THEN
'101-200'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 200
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 300
THEN
'201-300'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 300
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 400
THEN
'301-400'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 400
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 500
THEN
'401-500'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 500
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 600
THEN
'501-600'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 600
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 700
THEN
'601-700'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 700
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 800
THEN
'701-800'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 800
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 900
THEN
'801-900'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 900
AND D_AMIS_WORK_ORDER.PRIORITY_RISK <= 1000
THEN
'901-1000'
WHEN D_AMIS_WORK_ORDER.PRIORITY_RISK > 1000
THEN
'UNRATED'
ELSE
'UNRATED'
END) ,
(CASE WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'NEW' THEN 10
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'WVALID' THEN 20
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'VALID' THEN 30
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'PLANNED' THEN 40
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'WAPPR' THEN 50
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'WAPPR' THEN 51
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'APPR' THEN 60
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'COMP' THEN 70
WHEN D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS = 'CLOSE' THEN 80
END) ,
F_AMIS_WORK_ORDER.VALID_FROM ,
F_AMIS_WORK_ORDER.VALID_TO ,
D_AMIS_WORK_ORDER.AMIS_WORK_ORDER_ID ,
D_AMIS_WORK_ORDER.PRIORITY_RISK ,
F_AMIS_WORK_ORDER.WORK_ORDER_COUNT ,
D_AMIS_WORK_ORDER.OUTAGE_REQUIRED ,
D_AMIS_WORK_ORDER_STATUS.AMIS_WORK_ORDER_STATUS_ID ,
D_AMIS_ASSET_TYPE.ASSET_TYPE_DESCRIPTION ,
D_AMIS_ASSET.ASSET_NUMBER ,
D_AMIS_ASSET_TYPE.ASSET_TYPE ,
SYSDATE ,
'ODI' ,
40930939 ,
'Y' ,
D_DATE6.ACTUAL_DATE ,
D_DATE7.ACTUAL_DATE
FROM
BI_ADS.D_AMIS_WORK_ORDER D_AMIS_WORK_ORDER , BI_ADS.D_DATE D_DATE , BI_ADS.D_DATE D_DATE1 , BI_ADS.D_DATE D_DATE2 , BI_ADS.D_DATE D_DATE3 , BI_ADS.D_DATE D_DATE4 , BI_ADS.D_DATE D_DATE5 , BI_ADS.D_AMIS_ASSET D_AMIS_ASSET , BI_ADS.D_AMIS_ASSET_TYPE D_AMIS_ASSET_TYPE , BI_ADS.D_AMIS_LD D_AMIS_LD , BI_ADS.D_AMIS_LOCATION D_AMIS_LOCATION , BI_ADS.D_AMIS_WORK_TYPE_ACTIVITY D_AMIS_WORK_TYPE_ACTIVITY , BI_ADS.F_AMIS_WORK_ORDER F_AMIS_WORK_ORDER , BI_ADS.D_AMIS_WORK_ORDER_STATUS D_AMIS_WORK_ORDER_STATUS , BI_ADS.D_DATE D_DATE6 , BI_ADS.D_DATE D_DATE7
WHERE
(F_AMIS_WORK_ORDER.ACTUAL_START_DATE_ID =D_DATE6.DATE_ID
AND F_AMIS_WORK_ORDER.ACTUAL_FINISH_DATE_ID =D_DATE7.DATE_ID
AND F_AMIS_WORK_ORDER.STATUS_COMP_DATE_ID = D_DATE2.DATE_ID
AND F_AMIS_WORK_ORDER.SCHEDULED_START_DATE_ID = D_DATE3.DATE_ID
AND F_AMIS_WORK_ORDER.SCHEDULED_FINISH_DATE_ID =D_DATE4.DATE_ID
AND F_AMIS_WORK_ORDER.TARGET_FINISH_DATE_ID =D_DATE1.DATE_ID
AND F_AMIS_WORK_ORDER.TARGET_START_DATE_ID = D_DATE.DATE_ID
AND F_AMIS_WORK_ORDER.REPORTED_DATE_ID=D_DATE5.DATE_ID
AND F_AMIS_WORK_ORDER.AMIS_WORK_ORDER_ID = D_AMIS_WORK_ORDER.AMIS_WORK_ORDER_ID
AND F_AMIS_WORK_ORDER.AMIS_WORK_ORDER_STATUS_ID = D_AMIS_WORK_ORDER_STATUS.AMIS_WORK_ORDER_STATUS_ID
AND F_AMIS_WORK_ORDER.AMIS_WORK_TYPE_ACTIVITY_ID = D_AMIS_WORK_TYPE_ACTIVITY.AMIS_WORK_TYPE_ACTIVITY_ID
AND F_AMIS_WORK_ORDER.AMIS_LOCATION_ID = D_AMIS_LOCATION.AMIS_LOCATION_ID
AND F_AMIS_WORK_ORDER.AMIS_ASSET_ID = D_AMIS_ASSET.AMIS_ASSET_ID
AND F_AMIS_WORK_ORDER.AMIS_ASSET_TYPE_ID = D_AMIS_ASSET_TYPE.AMIS_ASSET_TYPE_ID
AND D_AMIS_WORK_ORDER.DESCRIPTION_LD_ID = D_AMIS_LD.AMIS_LD_ID
) AND (D_AMIS_WORK_TYPE_ACTIVITY.WORK_TYPE_CODE IN('PDM', 'PDM-C', 'PDM-L','MPJ')
) AND (D_AMIS_WORK_ORDER_STATUS.WORK_ORDER_STATUS IN('APPR','NEW', 'PLANNED','VALID','WAPPR','WAPR','WVALID','COMP','CLOSE','CAN')
) AND ((ADD_MONTHS ((TO_DATE (TO_CHAR(TRUNC(SYSDATE-1), 'RRRR-MM-DD'),'RRRR-MM-DD')
+ TO_NUMBER (
TO_CHAR (
(TO_DATE (TO_CHAR(TRUNC(SYSDATE-1), 'RRRR-MM-DD'),'RRRR-MM-DD')),
'dd'),
'99')
* -1),
-17) <
D_DATE2.ACTUAL_DATE
OR D_DATE2.ACTUAL_DATE IS NULL)
)
The below is the execution plan of the below query.
Plan hash value: 1047337321
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1410K| 851M| | 1044K (1)| 00:00:15 | | | |
| 1 | LOAD AS SELECT | REP_WORP_WO_DETAIL | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10026 | 1410K| 851M| | 1044K (1)| 00:00:15 | Q1,26 | P->S | QC (RAND) |
| 4 | OPTIMIZER STATISTICS GATHERING | | 1410K| 851M| | 1044K (1)| 00:00:15 | Q1,26 | PCWC | |
|* 5 | HASH JOIN BUFFERED | | 1410K| 851M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 6 | PX RECEIVE | | 420 | 12600 | | 15 (0)| 00:00:01 | Q1,26 | PCWP | |
| 7 | PX SEND BROADCAST | :TQ10017 | 420 | 12600 | | 15 (0)| 00:00:01 | Q1,17 | P->P | BROADCAST |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED | D_AMIS_ASSET_TYPE | 420 | 12600 | | 15 (0)| 00:00:01 | Q1,17 | PCWP | |
| 9 | BUFFER SORT | | | | | | | Q1,17 | PCWC | |
| 10 | PX RECEIVE | | 420 | | | 1 (0)| 00:00:01 | Q1,17 | PCWP | |
| 11 | PX SEND HASH (BLOCK ADDRESS) | :TQ10013 | 420 | | | 1 (0)| 00:00:01 | Q1,13 | S->P | HASH (BLOCK|
| 12 | PX SELECTOR | | | | | | | Q1,13 | SCWC | |
| 13 | INDEX FULL SCAN | D_AMIS_ASSET_TYPE_PK | 420 | | | 1 (0)| 00:00:01 | Q1,13 | SCWP | |
|* 14 | HASH JOIN | | 1410K| 811M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 15 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10018 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,18 | P->P | BROADCAST |
| 17 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,18 | PCWC | |
| 18 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,18 | PCWP | |
|* 19 | HASH JOIN | | 1410K| 792M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 20 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 21 | PX SEND BROADCAST | :TQ10019 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,19 | P->P | BROADCAST |
| 22 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,19 | PCWC | |
| 23 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,19 | PCWP | |
|* 24 | HASH JOIN | | 1410K| 773M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 25 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 26 | PX SEND BROADCAST | :TQ10020 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,20 | P->P | BROADCAST |
| 27 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,20 | PCWC | |
| 28 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,20 | PCWP | |
|* 29 | HASH JOIN | | 1410K| 754M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 30 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 31 | PX SEND BROADCAST | :TQ10021 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,21 | P->P | BROADCAST |
| 32 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,21 | PCWC | |
| 33 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,21 | PCWP | |
|* 34 | HASH JOIN | | 1410K| 735M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 35 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 36 | PX SEND BROADCAST | :TQ10022 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,22 | P->P | BROADCAST |
| 37 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,22 | PCWC | |
| 38 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,22 | PCWP | |
|* 39 | HASH JOIN | | 1410K| 717M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 40 | PX RECEIVE | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,26 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ10023 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,23 | P->P | BROADCAST |
| 42 | PX BLOCK ITERATOR | | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,23 | PCWC | |
| 43 | INDEX FAST FULL SCAN | DDTE_UK3 | 108K| 1483K| | 5 (0)| 00:00:01 | Q1,23 | PCWP | |
| 44 | NESTED LOOPS | | 1410K| 698M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 45 | NESTED LOOPS | | 1410K| 698M| | 1044K (1)| 00:00:15 | Q1,26 | PCWP | |
| 46 | NESTED LOOPS | | 1410K| 679M| | 979K (1)| 00:00:14 | Q1,26 | PCWP | |
|* 47 | HASH JOIN | | 1410K| 618M| | 841K (1)| 00:00:12 | Q1,26 | PCWP | |
| 48 | PX RECEIVE | | 1410K| 308M| | 722K (1)| 00:00:11 | Q1,26 | PCWP | |
| 49 | PX SEND HYBRID HASH | :TQ10024 | 1410K| 308M| | 722K (1)| 00:00:11 | Q1,24 | P->P | HYBRID HASH|
| 50 | STATISTICS COLLECTOR | | | | | | | Q1,24 | PCWC | |
|* 51 | HASH JOIN BUFFERED | | 1410K| 308M| | 722K (1)| 00:00:11 | Q1,24 | PCWP | |
| 52 | PX RECEIVE | | 1453K| 113M| | 50108 (1)| 00:00:01 | Q1,24 | PCWP | |
| 53 | PX SEND HYBRID HASH | :TQ10014 | 1453K| 113M| | 50108 (1)| 00:00:01 | Q1,14 | P->P | HYBRID HASH|
| 54 | STATISTICS COLLECTOR | | | | | | | Q1,14 | PCWC | |
| 55 | TABLE ACCESS BY INDEX ROWID BATCHED | D_AMIS_WORK_ORDER | 1453K| 113M| | 50108 (1)| 00:00:01 | Q1,14 | PCWP | |
| 56 | BUFFER SORT | | | | | | | Q1,14 | PCWC | |
| 57 | PX RECEIVE | | 1453K| | | 1542 (1)| 00:00:01 | Q1,14 | PCWP | |
| 58 | PX SEND HASH (BLOCK ADDRESS) | :TQ10010 | 1453K| | | 1542 (1)| 00:00:01 | Q1,10 | S->P | HASH (BLOCK|
| 59 | PX SELECTOR | | | | | | | Q1,10 | SCWC | |
| 60 | INDEX FULL SCAN | D_AMIS_WORK_ORDER_PK | 1453K| | | 1542 (1)| 00:00:01 | Q1,10 | SCWP | |
| 61 | PX RECEIVE | | 1410K| 197M| | 672K (1)| 00:00:10 | Q1,24 | PCWP | |
| 62 | PX SEND HYBRID HASH | :TQ10015 | 1410K| 197M| | 672K (1)| 00:00:10 | Q1,15 | P->P | HYBRID HASH|
|* 63 | HASH JOIN BUFFERED | | 1410K| 197M| | 672K (1)| 00:00:10 | Q1,15 | PCWP | |
| 64 | PX RECEIVE | | 72951 | 997K| | 5 (0)| 00:00:01 | Q1,15 | PCWP | |
| 65 | PX SEND HYBRID HASH | :TQ10011 | 72951 | 997K| | 5 (0)| 00:00:01 | Q1,11 | P->P | HYBRID HASH|
| 66 | STATISTICS COLLECTOR | | | | | | | Q1,11 | PCWC | |
| 67 | PX BLOCK ITERATOR | | 72951 | 997K| | 5 (0)| 00:00:01 | Q1,11 | PCWC | |
|* 68 | INDEX FAST FULL SCAN | DDTE_UK3 | 72951 | 997K| | 5 (0)| 00:00:01 | Q1,11 | PCWP | |
| 69 | PX RECEIVE | | 1410K| 178M| | 672K (1)| 00:00:10 | Q1,15 | PCWP | |
| 70 | PX SEND HYBRID HASH | :TQ10012 | 1410K| 178M| | 672K (1)| 00:00:10 | Q1,12 | P->P | HYBRID HASH|
|* 71 | HASH JOIN BUFFERED | | 1410K| 178M| | 672K (1)| 00:00:10 | Q1,12 | PCWP | |
| 72 | JOIN FILTER CREATE | :BF0000 | 10 | 90 | | 3 (0)| 00:00:01 | Q1,12 | PCWP | |
| 73 | PX RECEIVE | | 10 | 90 | | 3 (0)| 00:00:01 | Q1,12 | PCWP | |
| 74 | PX SEND HYBRID HASH | :TQ10008 | 10 | 90 | | 3 (0)| 00:00:01 | Q1,08 | P->P | HYBRID HASH|
| 75 | STATISTICS COLLECTOR | | | | | | | Q1,08 | PCWC | |
|* 76 | TCHED TABLE ACCESS BY INDEX ROWID BA | D_AMIS_WORK_ORDER_STATUS | 10 | 90 | | 3 (0)| 00:00:01 | Q1,08 | PCWP | |
| 77 | BUFFER SORT | | | | | | | Q1,08 | PCWC | |
| 78 | PX RECEIVE | | 21 | | | 1 (0)| 00:00:01 | Q1,08 | PCWP | |
| 79 | ) PX SEND HASH (BLOCK ADDRESS | :TQ10005 | 21 | | | 1 (0)| 00:00:01 | Q1,05 | S->P | HASH (BLOCK|
| 80 | PX SELECTOR | | | | | | | Q1,05 | SCWC | |
| 81 | INDEX FULL SCAN | D_AMIS_WORK_ORDER_STATUS_PK | 21 | | | 1 (0)| 00:00:01 | Q1,05 | SCWP | |
| 82 | PX RECEIVE | | 1975K| 233M| | 672K (1)| 00:00:10 | Q1,12 | PCWP | |
| 83 | PX SEND HYBRID HASH | :TQ10009 | 1975K| 233M| | 672K (1)| 00:00:10 | Q1,09 | P->P | HYBRID HASH|
| 84 | JOIN FILTER USE | :BF0000 | 1975K| 233M| | 672K (1)| 00:00:10 | Q1,09 | PCWP | |
|* 85 | HASH JOIN BUFFERED | | 1975K| 233M| | 672K (1)| 00:00:10 | Q1,09 | PCWP | |
| 86 | JOIN FILTER CREATE | :BF0001 | 14 | 154 | | 4 (0)| 00:00:01 | Q1,09 | PCWP | |
| 87 | PX RECEIVE | | 14 | 154 | | 4 (0)| 00:00:01 | Q1,09 | PCWP | |
| 88 | PX SEND HYBRID HASH | :TQ10006 | 14 | 154 | | 4 (0)| 00:00:01 | Q1,06 | P->P | HYBRID HASH|
| 89 | STATISTICS COLLECTOR | | | | | | | Q1,06 | PCWC | |
|* 90 | D BATCHED TABLE ACCESS BY INDEX ROWI | D_AMIS_WORK_TYPE_ACTIVITY | 14 | 154 | | 4 (0)| 00:00:01 | Q1,06 | PCWP | |
| 91 | BUFFER SORT | | | | | | | Q1,06 | PCWC | |
| 92 | PX RECEIVE | | 61 | | | 1 (0)| 00:00:01 | Q1,06 | PCWP | |
| 93 | RESS) PX SEND HASH (BLOCK ADD | :TQ10002 | 61 | | | 1 (0)| 00:00:01 | Q1,02 | S->P | HASH (BLOCK|
| 94 | PX SELECTOR | | | | | | | Q1,02 | SCWC | |
| 95 | INDEX FULL SCAN | D_AMIS_WORK_TYPE_PK | 61 | | | 1 (0)| 00:00:01 | Q1,02 | SCWP | |
| 96 | PX RECEIVE | | 7335K| 790M| | 672K (1)| 00:00:10 | Q1,09 | PCWP | |
| 97 | PX SEND HYBRID HASH | :TQ10007 | 7335K| 790M| | 672K (1)| 00:00:10 | Q1,07 | P->P | HYBRID HASH|
| 98 | JOIN FILTER USE | :BF0001 | 7335K| 790M| | 672K (1)| 00:00:10 | Q1,07 | PCWP | |
|* 99 | HASH JOIN BUFFERED | | 7335K| 790M| 805M| 672K (1)| 00:00:10 | Q1,07 | PCWP | |
| 100 | PX RECEIVE | | 7682K| 717M| | 613K (1)| 00:00:09 | Q1,07 | PCWP | |
| 101 | PX SEND HYBRID HASH | :TQ10003 | 7682K| 717M| | 613K (1)| 00:00:09 | Q1,03 | P->P | HYBRID HASH|
| 102 | STATISTICS COLLECTOR | | | | | | | Q1,03 | PCWC | |
| 103 | OWID BATCHED TABLE ACCESS BY INDEX R | F_AMIS_WORK_ORDER | 7682K| 717M| | 613K (1)| 00:00:09 | Q1,03 | PCWP | |
| 104 | BUFFER SORT | | | | | | | Q1,03 | PCWC | |
| 105 | PX RECEIVE | | | | | | | Q1,03 | PCWP | |
| 106 | ADDRESS) PX SEND HASH (BLOCK | :TQ10000 | | | | | | Q1,00 | S->P | HASH (BLOCK|
| 107 | PX SELECTOR | | | | | | | Q1,00 | SCWC | |
| 108 | TO ROWIDS BITMAP CONVERSION | | | | | | | Q1,00 | SCWC | |
| 109 | SCAN BITMAP INDEX FULL | F_AMIS_WORK_ORDER_IDX31 | | | | | | Q1,00 | SCWP | |
| 110 | PX RECEIVE | | 648K| 9502K| | 23613 (1)| 00:00:01 | Q1,07 | PCWP | |
| 111 | PX SEND HYBRID HASH | :TQ10004 | 648K| 9502K| | 23613 (1)| 00:00:01 | Q1,04 | P->P | HYBRID HASH|
| 112 | WID BATCHED TABLE ACCESS BY INDEX RO | D_AMIS_ASSET | 648K| 9502K| | 23613 (1)| 00:00:01 | Q1,04 | PCWP | |
| 113 | BUFFER SORT | | | | | | | Q1,04 | PCWC | |
| 114 | PX RECEIVE | | 648K| | | 676 (1)| 00:00:01 | Q1,04 | PCWP | |
| 115 | DDRESS) PX SEND HASH (BLOCK A | :TQ10001 | 648K| | | 676 (1)| 00:00:01 | Q1,01 | S->P | HASH (BLOCK|
| 116 | PX SELECTOR | | | | | | | Q1,01 | SCWC | |
| 117 | INDEX FULL SCAN | D_AMIS_ASSET_PK | 648K| | | 676 (1)| 00:00:01 | Q1,01 | SCWP | |
| 118 | PX RECEIVE | | 2517K| 554M| | 118K (1)| 00:00:02 | Q1,26 | PCWP | |
| 119 | PX SEND HYBRID HASH | :TQ10025 | 2517K| 554M| | 118K (1)| 00:00:02 | Q1,25 | P->P | HYBRID HASH|
| 120 | TABLE ACCESS BY INDEX ROWID BATCHED | D_AMIS_LD | 2517K| 554M| | 118K (1)| 00:00:02 | Q1,25 | PCWP | |
| 121 | BUFFER SORT | | | | | | | Q1,25 | PCWC | |
| 122 | PX RECEIVE | | 2517K| | | 2587 (1)| 00:00:01 | Q1,25 | PCWP | |
| 123 | PX SEND HASH (BLOCK ADDRESS) | :TQ10016 | 2517K| | | 2587 (1)| 00:00:01 | Q1,16 | S->P | HASH (BLOCK|
| 124 | PX SELECTOR | | | | | | | Q1,16 | SCWC | |
| 125 | INDEX FULL SCAN | D_AMIS_LD_PK | 2517K| | | 2587 (1)| 00:00:01 | Q1,16 | SCWP | |
| 126 | TABLE ACCESS BY INDEX ROWID | D_AMIS_LOCATION | 1 | 45 | | 0 (0)| 00:00:01 | Q1,26 | PCWP | |
|*127 | INDEX UNIQUE SCAN | D_AMIS_LOCATION_PK | 1 | | | 0 (0)| 00:00:01 | Q1,26 | PCWP | |
|*128 | INDEX UNIQUE SCAN | DDTE_PK | 1 | | | 0 (0)| 00:00:01 | Q1,26 | PCWP | |
| 129 | TABLE ACCESS BY INDEX ROWID | D_DATE | 1 | 14 | | 0 (0)| 00:00:01 | Q1,26 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("F_AMIS_WORK_ORDER"."AMIS_ASSET_TYPE_ID"="D_AMIS_ASSET_TYPE"."AMIS_ASSET_TYPE_ID")
14 - access("F_AMIS_WORK_ORDER"."ACTUAL_FINISH_DATE_ID"="D_DATE7"."DATE_ID")
19 - access("F_AMIS_WORK_ORDER"."ACTUAL_START_DATE_ID"="D_DATE6"."DATE_ID")
24 - access("F_AMIS_WORK_ORDER"."REPORTED_DATE_ID"="D_DATE5"."DATE_ID")
29 - access("F_AMIS_WORK_ORDER"."SCHEDULED_FINISH_DATE_ID"="D_DATE4"."DATE_ID")
34 - access("F_AMIS_WORK_ORDER"."SCHEDULED_START_DATE_ID"="D_DATE3"."DATE_ID")
39 - access("F_AMIS_WORK_ORDER"."TARGET_FINISH_DATE_ID"="D_DATE1"."DATE_ID")
47 - access("D_AMIS_WORK_ORDER"."DESCRIPTION_LD_ID"="D_AMIS_LD"."AMIS_LD_ID")
51 - access("F_AMIS_WORK_ORDER"."AMIS_WORK_ORDER_ID"="D_AMIS_WORK_ORDER"."AMIS_WORK_ORDER_ID")
63 - access("F_AMIS_WORK_ORDER"."STATUS_COMP_DATE_ID"="D_DATE2"."DATE_ID")
68 - filter("D_DATE2"."ACTUAL_DATE">ADD_MONTHS(TO_DATE(TO_CHAR(TRUNC(SYSDATE@!-1),'RRRR-MM-DD'),'RRRR-MM-DD')+TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(TRUNC(SYSDATE@!-1),'RRR
R-MM-DD'),'RRRR-MM-DD'),'dd'),'99')*(-1),-17) OR "D_DATE2"."ACTUAL_DATE" IS NULL)
71 - access("F_AMIS_WORK_ORDER"."AMIS_WORK_ORDER_STATUS_ID"="D_AMIS_WORK_ORDER_STATUS"."AMIS_WORK_ORDER_STATUS_ID")
76 - filter("D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='APPR' OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='CAN' OR
"D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='CLOSE' OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='COMP' OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='NEW'
OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='PLANNED' OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='VALID' OR
"D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='WAPPR' OR "D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='WAPR' OR
"D_AMIS_WORK_ORDER_STATUS"."WORK_ORDER_STATUS"='WVALID')
85 - access("F_AMIS_WORK_ORDER"."AMIS_WORK_TYPE_ACTIVITY_ID"="D_AMIS_WORK_TYPE_ACTIVITY"."AMIS_WORK_TYPE_ACTIVITY_ID")
90 - filter("D_AMIS_WORK_TYPE_ACTIVITY"."WORK_TYPE_CODE"='MPJ' OR "D_AMIS_WORK_TYPE_ACTIVITY"."WORK_TYPE_CODE"='PDM' OR
"D_AMIS_WORK_TYPE_ACTIVITY"."WORK_TYPE_CODE"='PDM-C' OR "D_AMIS_WORK_TYPE_ACTIVITY"."WORK_TYPE_CODE"='PDM-L')
99 - access("F_AMIS_WORK_ORDER"."AMIS_ASSET_ID"="D_AMIS_ASSET"."AMIS_ASSET_ID")
127 - access("F_AMIS_WORK_ORDER"."AMIS_LOCATION_ID"="D_AMIS_LOCATION"."AMIS_LOCATION_ID")
128 - access("F_AMIS_WORK_ORDER"."TARGET_START_DATE_ID"="D_DATE"."DATE_ID")
Note
-----
- dynamic statistics used: dynamic sampling (level=AUTO)
- Degree of Parallelism is 24 because of hint
- PDML is disabled in current session
The above query loads 341 million records in Production.
Appreciate your help in optimizing the above query.
Regards