SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query taking 40 minutes in Production

user1014019Jul 25 2020 — edited Jul 27 2020

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

This post has been answered by Jonathan Lewis on Jul 25 2020

Jump to Answer

Jonathan Lewis

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

Marked as Answer by user1014019 · Sep 27 2020

1 person found this helpful

EdStevens

Also asked at http://orafaq.com/forum/t/206929/

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

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.

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

1 - 5

Added on Jul 25 2020

5 comments

420 views

SQL & PL/SQL

Query taking 40 minutes in Production

Comments

Post Details