Forum Stats

  • 3,733,705 Users
  • 2,246,809 Discussions
  • 7,856,851 Comments

Discussions

Query taking 40 minutes in Production

user1014019
user1014019 Member Posts: 238 Bronze Badge
edited July 2020 in SQL & PL/SQL

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_IDAND F_AMIS_WORK_ORDER.ACTUAL_FINISH_DATE_ID =D_DATE7.DATE_IDAND F_AMIS_WORK_ORDER.STATUS_COMP_DATE_ID = D_DATE2.DATE_IDAND F_AMIS_WORK_ORDER.SCHEDULED_START_DATE_ID = D_DATE3.DATE_IDAND F_AMIS_WORK_ORDER.SCHEDULED_FINISH_DATE_ID =D_DATE4.DATE_IDAND F_AMIS_WORK_ORDER.TARGET_FINISH_DATE_ID =D_DATE1.DATE_IDAND F_AMIS_WORK_ORDER.TARGET_START_DATE_ID = D_DATE.DATE_IDAND F_AMIS_WORK_ORDER.REPORTED_DATE_ID=D_DATE5.DATE_IDAND F_AMIS_WORK_ORDER.AMIS_WORK_ORDER_ID = D_AMIS_WORK_ORDER.AMIS_WORK_ORDER_IDAND F_AMIS_WORK_ORDER.AMIS_WORK_ORDER_STATUS_ID = D_AMIS_WORK_ORDER_STATUS.AMIS_WORK_ORDER_STATUS_IDAND F_AMIS_WORK_ORDER.AMIS_WORK_TYPE_ACTIVITY_ID = D_AMIS_WORK_TYPE_ACTIVITY.AMIS_WORK_TYPE_ACTIVITY_IDAND F_AMIS_WORK_ORDER.AMIS_LOCATION_ID = D_AMIS_LOCATION.AMIS_LOCATION_IDAND F_AMIS_WORK_ORDER.AMIS_ASSET_ID = D_AMIS_ASSET.AMIS_ASSET_IDAND F_AMIS_WORK_ORDER.AMIS_ASSET_TYPE_ID = D_AMIS_ASSET_TYPE.AMIS_ASSET_TYPE_IDAND 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([email protected]!-1),'RRRR-MM-DD'),'RRRR-MM-DD')+TO_NUMBER(TO_CHAR(TO_DATE(TO_CHAR(TRUNC([email protected]!-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

William Robertson

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited July 2020 Accepted Answer

    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

    William Robertson

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited July 2020 Accepted Answer

    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

    William Robertson
  • EdStevens
    EdStevens Member Posts: 28,135 Gold Crown
    edited July 2020
  • user1014019
    user1014019 Member Posts: 238 Bronze Badge
    edited July 2020

    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

  • user1014019
    user1014019 Member Posts: 238 Bronze Badge
    edited July 2020

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,607 Gold Crown
    edited July 2020
    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

Sign In or Register to comment.