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 Go to next message
manoj12
Messages: 210
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)
) 
       
The below is the execution plan of the above 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


Appreciate your help in optimizing the above query.

Regards,
Re: Query is taking 41 minute in Production environment [message #681506 is a reply to message #681505] Sat, 25 July 2020 05:40 Go to previous messageGo to next message
John Watson
Messages: 8922
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)
What are you trying to do? I don't suppose it has any effect on performance, but when one sees such an odd construct it makes one question the whole query.
Re: Query is taking 41 minute in Production environment [message #681508 is a reply to message #681506] Sat, 25 July 2020 06:17 Go to previous messageGo to next message
manoj12
Messages: 210
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 Go to previous messageGo to next message
John Watson
Messages: 8922
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>
I expect that there is a neater solution, too.
Re: Query is taking 41 minute in Production environment [message #681512 is a reply to message #681511] Sat, 25 July 2020 07:19 Go to previous messageGo to next message
manoj12
Messages: 210
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

Re: Query is taking 41 minute in Production environment [message #681517 is a reply to message #681505] Sat, 25 July 2020 10:19 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Also asked at https://community.oracle.com/thread/4340484
Re: Query is taking 41 minute in Production environment [message #681528 is a reply to message #681505] Mon, 27 July 2020 06:21 Go to previous messageGo to next message
manoj12
Messages: 210
Registered: March 2008
Location: India
Senior Member
Hello John,

Thank you very much for the wonderful suggestion.
By implementing suggestions now the load is completing in 12 minutes.

Closing this issue

Regards,


Re: Query is taking 41 minute in Production environment [message #681530 is a reply to message #681505] Mon, 27 July 2020 06:25 Go to previous message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Answered at https://community.oracle.com/thread/4340484
Previous Topic: Select Query suggestion
Next Topic: How to get a Output as mentioned below
Goto Forum:
  


Current Time: Thu Mar 28 05:07:16 CDT 2024