Home » SQL & PL/SQL » SQL & PL/SQL » Query is taking 41 minute in Production environment (Oracle 12.1.0.1)
| Query is taking 41 minute in Production environment [message #681505] |
Sat, 25 July 2020 05:12  |
manoj12
Messages: 208
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hi Sir,
The below query is taking 41 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)
)
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
Regards,
|
|
|
|
| Re: Query is taking 41 minute in Production environment [message #681506 is a reply to message #681505] |
Sat, 25 July 2020 05:40  |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
First, the really obvious: if you want to do a parellel insert, you need to ALTER SESSION ENABLE PARALLEL DML. The execution plan note is telling you that.
Second, I would get rid of that hint that seems to be trying to disable the cost based optimizer. Why is it there? Instead, gather decent statistics including histograms and see what the optimizer comes up with.
Third, this thing: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) |
|
|
|
| Re: Query is taking 41 minute in Production environment [message #681508 is a reply to message #681506] |
Sat, 25 July 2020 06:17 |
manoj12
Messages: 208
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hello Sir,
Thank you very much for the great suggestion.
I will do Parallel DML of the query.
I normally gather the stats after completion of the load but this load takes 40 minutes to load 341 million records.
The below construct highlighted extracts data of 17 months and compares it with D_DATE dimension. This is the functionality of the query.
I will also remove the hint from the below query.
Appreciate your suggestion on this.
Regards,
|
|
|
|
| Re: Query is taking 41 minute in Production environment [message #681511 is a reply to message #681506] |
Sat, 25 July 2020 06:49 |
John Watson
Messages: 8804
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
orclz>
orclz> select
2 (ADD_MONTHS ((TO_DATE (TO_CHAR(TRUNC(SYSDATE-1), 'RRRR-MM-DD'),'RRRR-MM-DD')
3 + TO_NUMBER (
4 TO_CHAR (
5 (TO_DATE (TO_CHAR(TRUNC(SYSDATE-1), 'RRRR-MM-DD'),'RRRR-MM-DD')),
6 'dd'),
7 '99')
8 * -1),
9 -17) ) from dual;
(ADD_MONTHS((TO_DAT
-------------------
2019-01-31:00:00:00
orclz>
orclz>
orclz> select add_months(last_day(trunc(sysdate)),-18) from dual;
ADD_MONTHS(LAST_DAY
-------------------
2019-01-31:00:00:00
orclz> |
|
|
|
| Re: Query is taking 41 minute in Production environment [message #681512 is a reply to message #681511] |
Sat, 25 July 2020 07:19 |
manoj12
Messages: 208
Registered: March 2008
Location: India
|
Senior Member |
|
|
Hello Sir,
Thank you very much for this.
I will implement this functionality in lower environment.
I will also add Enable Parallel DML.
I will also remove hint and generate the stats at the end of each load.
I am adding 8 D_Date dimensions in this query.
Any suggestion in reducing D_Date dimension as it is using this D_Date for 8 times in the query.
Regards
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Mar 24 10:18:19 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
