5 Replies Latest reply on Jul 27, 2020 11:47 AM by Jonathan Lewis

    Query taking 40 minutes in Production

    user1014019

      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

        • 1. Re: Query taking 40 minutes in Production
          Jonathan Lewis

          The Note section tells you:
          - PDML is disabled in current session

           

          So it looks as if you haven't executed: "alter session enable parallel DML" - this may be enough to get the performance you want.

           

          Questions: 

          How much time do you think it should take to create and insert your 341M rows ?

          What indexes are there on the table receiving the data - and how much of the work is maintaining indexes?

          Which version or Oracle are you using?

          Are you licensed to use the Diagnostic and Performance packs - if so have you used the SQL Monitor feature to get a report of where the time is being spent?

          You have several hints in the query - presumably you did that because you think you know what Oracle should be doing: what do you think the execution plan should be.

           

          In passing, your hints include:

          • LEADING(F_AMIS_WORK_ORDER)
          • USE_HASH(F_AMIS_WORK_ORDER)

          These two hints are incompatible - if f_amis_work_order is the first table in the join it can never be in a position to be the probe table of a hash join.

           

          You also have the hint

          • PQ_DISTRIBUTE(REP_WORP_WO_DETAIL HASH,HASH)

          But the table is the target of the insert, not a table that will be the second table of a join, so the hint isn't doing whatever you think it ought to be doing. You probably intended to use the  "single-table" version of the hint.

           

           

          Random Observation:

          You've got a call to dbms_lob.substr() - it's possible that that's responsible for a lot of the final time of insert if one process (the coordinator) has to call it 341M times.  Apart from sorting out the optimum plan I'd check to see what impact it had on total time and see if there were any way of bypassing a lot of that time (e.g. if the column is often NULL would wrapping the call in a CASE/END that checked for null help; e.g.2 is a basic substr() more efficient in your version than a call to dbms_lob.substr()).

           

           

          Regards

          Jonathan Lewis

           

           

          • 3. Re: Query taking 40 minutes in Production
            user1014019

            Hello Sir,

             

            Thank you very much for the wonderful suggestions.

            I have implemented Enable.Parallel DML and now the load is completing in 12 minutes.

             

            Hence marking the issue as closed.

             

            Regards

            • 4. Re: Query taking 40 minutes in Production
              user1014019

              Hello Sir,

               

              I have also removed all the hints and kept only Parallel Hint so that it execute in Parallel.

               

              This issue is solved now.

              • 5. Re: Query taking 40 minutes in Production
                Jonathan Lewis

                user1014019 wrote:

                 

                I have also removed all the hints and kept only Parallel Hint so that it execute in Parallel.

                 

                .

                 

                That was a sensible move. Presumably most of the hints were put there by someone else who had a specific plan in mind but didn't describe it fully enough in hints.  You might have noted a couple of places where the optimizer did a serial index full scan and then distributed the resulting rows "serial to parallel" by block address this would have been using excess CPU (and causing contention) in inter-process communication but would have been necessary because of hints that FORCED the optimizer to use an index when a parallell tablescan would have been more efficient.

                 

                Regards

                Jonathan Lewis