Skip to Main Content
  • Questions
  • Plan Hash Values Changed For the Same SQL ID || How to determine why ?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Rohit.

Asked: April 12, 2017 - 8:08 pm UTC

Last updated: August 03, 2023 - 10:39 am UTC

Version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Viewed 10K+ times! This question is

You Asked

Hello Team,

I have a particular sql query which runs on a daily basis for around 40 mins on an average. Today it has been running since 4:50 BST and has not completed at all.

I analysed what went wrong today and have detailed out what I could :

SQL Query :
--------------

INSERT INTO ald_extract_temp_GEA_R_CMP_2 (record_value)
   SELECT    FAULT_ID
          || '¦'
          || TO_CHAR (REC_EFF_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || CUPID
          || '¦'
          || CUST_ACC_NAME
          || '¦'
          || TO_CHAR (FAULT_RCVD_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || CARELEVEL_CODE
          || '¦'
          || TRIM (TO_CHAR (SLA_CLOCK, '9999999999999999999999'))
          || '¦'
          || TO_CHAR (KCI3_CLEAR_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || TO_CHAR (FIRST_KCI3_CLEAR_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || BILLING_ACC_NUM
          || '¦'
          || DUNS_NUM
          || '¦'
          || CIRCUIT_ID
          || '¦'
          || MDF_SITE_CODE
          || '¦'
          || DISTRICT_CODE
          || '¦'
          || EXCH_GRP_CODE
          || '¦'
          || PROD_CODE
          || '¦'
          || TEST_PRODUCT
          || '¦'
          || FAULT_SRC
          || '¦'
          || FAULT_STATUS
          || '¦'
          || FAULT_SUB_STATUS
          || '¦'
          || FAULT_DESC
          || '¦'
          || TO_CHAR (COMPLTD_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || CANCLTN_RSN_TXT
          || '¦'
          || USER_ID
          || '¦'
          || BUYERS_ID
          || '¦'
          || DIRECTORY_NUM
          || '¦'
          || TEST_REF
          || '¦'
          || CLEAR_CODE
          || '¦'
          || CANCLTN_FLAG
          || '¦'
          || RJCTD_FLAG
          || '¦'
          || CSS_FAULT_ID
          || '¦'
          || TO_CHAR (KCI1_ACCPT_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || TO_CHAR (REJECT_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || TO_CHAR (CANCEL_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || KCI_CODE
          || '¦'
          || TO_CHAR (KCI_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || APNTMT_REF
          || '¦'
          || TO_CHAR (APNTMT_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || TO_CHAR (EARLIEST_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || APNTMT_SLOT
          || '¦'
          || EARLIEST_SLOT
          || '¦'
          || TO_CHAR (EARLIEST_AVAILABLE_APPOINTMENT,
                      'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || TO_CHAR (CLOSE_DATE, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || CLEAR_CODE_ADD_IND
          || '¦'
          || ENGINEER_ID
          || '¦'
          || TEMP_CLEAR_FLAG
          || '¦'
          || TO_CHAR (PARKED_TIME, '99999999.99')
          || '¦'
          || ASSET_CARE_LEVEL
          || '¦'
          || TO_CHAR (LATEST_ACCESS_TIME, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          ||                               /* ver1.0 TO_CHAR HAS BEEN ADDED */
            TO_CHAR (EARLIEST_NEXT_ACCESS_TIME, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || ACCESS_FLAG_24_HR
          || '¦'
          || FOLLOW_ON_FLT_FLAG
          || '¦'
          || EXPIDITE_FAULT_FLAG
          || '¦'
          || TO_CHAR (ORIGINAL_COMMIT_TIME, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || CASE
                WHEN     (KCI3_CLEAR_DATE IS NULL)
                     AND (KCI_CODE NOT IN ('3302', '3300')) /* 28/01/2011 Changes in RCT calculation. */
                THEN
                   NULL
                ELSE
                   TO_CHAR (NVL (FCT, ORIGINAL_COMMIT_TIME),
                            'DD-MM-YYYY HH24:MI:SS')
             END
          || '¦'
          || UPSTRM_BANDWIDTH
          || '¦'
          || DOWNSTRM_BANDWIDTH
          || '¦'
          || MBORC_REF
          || '¦'
          || TO_CHAR (MBORC_START_TIME, 'DD-MM-YYYY HH24:MI:SS')
          || '¦'
          || MBORC_CLEAR_TIME
          || '¦'
          || TO_CHAR (CARE_SERVICE_START_DATE, 'DD-MON-YYYY')
          || '¦'
          || CASE
                WHEN CARE_SERVICE_START_DATE IS NOT NULL
                THEN
                   TO_CHAR (CARE_SERVICE_START_DATE + 365, 'DD-MON-YYYY')
                ELSE
                   NULL
             END
          || '¦'
          || FAULT_TYPE
     FROM (SELECT DISTINCT
                  FLT1.FAULT_ID,
                  ACT.START_DATE_TIME REC_EFF_DATE,
                  FLT1.CUPID,
                  C.CUST_ACC_NAME,
                  FLT1.FAULT_RCVD_DATE,
                  FLT1.CARELEVEL_CODE,
                  FLT1.SLA_CLOCK,
                  FLT1.KCI3_CLEAR_DATE,
                  FLT1.FIRST_KCI3_CLEAR_DATE,
                  FLT1.BILLING_ACC_NUM,
                  FLT1.DUNS_NUM,
                  FLT1.CIRCUIT_ID,
                  FLT1.MDF_SITE_CODE,
                  FLT1.PROD_CODE,
                  FLT1.TEST_PRODUCT,
                  FLT1.FAULT_SRC,
                  FLT1.FAULT_STATUS,
                  FLT1.FAULT_SUB_STATUS,
                  FLT1.FAULT_DESC,
                  FLT1.COMPLTD_DATE,
                  FLT1.CANCLTN_RSN_TXT,
                  FLT1.USER_ID,
                  FLT1.BUYERS_ID,
                  FLT1.DIRECTORY_NUM,
                  FLT1.TEST_REF,
                  FLT1.CLEAR_CODE,
                  CASE
                     WHEN FLT1.CANCEL_DATE IS NOT NULL THEN 'Y'
                     ELSE 'N'
                  END
                     CANCLTN_FLAG,
                  CASE
                     WHEN FLT1.REJECT_DATE IS NOT NULL THEN 'Y'
                     ELSE 'N'
                  END
                     RJCTD_FLAG,
                  FLT1.CSS_FAULT_ID,
                  FLT1.KCI1_ACCPT_DATE,
                  FLT1.REJECT_DATE,
                  FLT1.CANCEL_DATE,
                  FLT1.KCI_CODE,
                  FLT1.KCI_DATE,
                  FLT1.APNTMT_REF,
                  FLT1.APNTMT_DATE,
                  FLT1.EARLIEST_DATE,
                  FLT1.APNTMT_SLOT,
                  FLT1.EARLIEST_SLOT,
                  FLT1.EARLIEST_AVAILABLE_APPOINTMENT,
                  FLT1.KCI4_CLOSE_DATE CLOSE_DATE,
                  FLT1.CLEAR_CODE_ADD_IND,
                  FLT1.ENGINEER_ID,
                  CASE
                     WHEN UPPER (CLEAR_TYPE_FLAG) = 'TEMPORARY CLEARED'
                     THEN
                        'Y'
                     ELSE
                        'N'
                  END
                     TEMP_CLEAR_FLAG,
                  CASE
                     WHEN UPPER (FLT1.MDF_SITE_CODE) <> 'NO_DATA'
                     THEN
                        SUBSTR (FLT1.MDF_SITE_CODE, 3)
                     ELSE
                        NULL
                  END
                     EXCH_GRP_CODE,
                  CASE
                     WHEN UPPER (FLT1.MDF_SITE_CODE) <> 'NO_DATA'
                     THEN
                        SUBSTR (FLT1.MDF_SITE_CODE, 1, 2)
                     ELSE
                        NULL
                  END
                     DISTRICT_CODE,
                  FLT1.PARKED_TIME,
                  FLT1.TIME_TO_CLEAR,
                  FLT1.PROD_NAME,
                  FLT1.ASSET_CARE_LEVEL,
                  FLT1.LATEST_ACCESS_TIME,
                  FLT1.EARLIEST_NEXT_ACCESS_TIME,
                  FLT1.ACCESS_FLAG_24_HR,
                  FLT1.FOLLOW_ON_FLT_FLAG,
                  FLT1.EXPIDITE_FAULT_FLAG,
                  CASE
                     WHEN FLT1.FAULT_RCVD_DATE <
                             (SELECT TO_DATE (code_desc, 'dd-mm-yyyy')
                                FROM compensation_ref
                               WHERE code_type = '006' AND code_value = '18')
                     THEN
                        FLT1.ORIGINAL_COMMIT_TIME
                     ELSE
                        CASE
                           WHEN O.ASSET_CARE_LEVEL IN ('1', '2', '2.5')
                           THEN
                                TRUNC (
                                   FN_FAULT_TRGT_DATE_FLT_BH (
                                      O.ASSET_CARE_LEVEL,
                                      O.FAULT_RCVD_DATE,
                                      NULL))
                              + 1
                              - 1 / 86400
                           WHEN O.ASSET_CARE_LEVEL = '3'
                           THEN
                              CASE
                                 WHEN O.FAULT_RCVD_DATE >
                                         (  TRUNC (O.FAULT_RCVD_DATE)
                                          + (1 / 2)
                                          + 1 / 24
                                          - 1 / 86400)
                                 THEN
                                    (  (TRUNC (O.FAULT_RCVD_DATE) + (1 / 2))
                                     + 1
                                     + (1 / 24 - 1 / 86400))
                                 ELSE
                                      (TRUNC (O.FAULT_RCVD_DATE) + 1)
                                    - (1 / 86400)
                              END
                           WHEN O.ASSET_CARE_LEVEL = '4'
                           THEN
                              CASE
                                 WHEN PKG_SH_RCT_CALC.FN_RET_COMMIT_DATE (
                                         O.FAULT_ID,
                                         '010') <>
                                         TO_DATE ('01-JAN-1753',
                                                  'dd-mon-yyyy')
                                 THEN
                                    PKG_SH_RCT_CALC.FN_RET_COMMIT_DATE (
                                       O.FAULT_ID,
                                       '010')
                                 WHEN (    (O.LATEST_ACCESS_TIME >=
                                               O.FAULT_RCVD_DATE)
                                       AND ( (  O.LATEST_ACCESS_TIME
                                              - O.FAULT_RCVD_DATE) > (1 / 4)))
                                 THEN --- 28/01/2011 Changes in OCT calculation.
                                    O.FAULT_RCVD_DATE + (1 / 4)
                                 WHEN (    (O.LATEST_ACCESS_TIME >=
                                               O.FAULT_RCVD_DATE)
                                       AND (O.EARLIEST_NEXT_ACCESS_TIME >
                                               O.FAULT_RCVD_DATE)
                                       AND ( (  O.LATEST_ACCESS_TIME
                                              - O.FAULT_RCVD_DATE) < (1 / 4)))
                                 THEN --- 28/01/2011 Changes in OCT calculation.
                                    O.EARLIEST_NEXT_ACCESS_TIME + (1 / 4)
                                 WHEN O.ACCESS_FLAG_24_HR = 'Y'
                                 THEN
                                    O.FAULT_RCVD_DATE + (1 / 4)
                                 ELSE
                                    O.FAULT_RCVD_DATE + (1 / 4)
                              END
                        END
                  END
                     ORIGINAL_COMMIT_TIME,
                  RCT.REVISED_COMMITMENT_DATE FCT,
                  ASSET.UPSTRM_BANDWIDTH,
                  ASSET.DOWNSTRM_BANDWIDTH,
                  FLT1.MBORC_REF,
                  PORTL.START_TIME MBORC_START_TIME,
                  FLT1.MBORC_CLEAR_TIME,
                  ASSET.CARE_SERVICE_START_DATE,
                  FLT1.FAULT_TYPE
             FROM GEA_FAULT_DETAIL_FACT FLT1
                  INNER JOIN ALD_CONTROL_TABLE ACT
                     ON     FLT1.REC_EFF_DATE = ACT.START_DATE_TIME
                        AND FLT1.PROD_CODE IN (SELECT PROD_CODE
                                                 FROM NGA_PROD_CODE
                                                WHERE GEA_R_ALL = 'Y') /*ORC2M-19559*/
                        AND FLT1.CLOSE_FLAG = 'Y'
                  INNER JOIN os_gea_faults O
                     ON     FLT1.FAULT_ID = O.FAULT_ID
                        AND (   UPPER (O.fault_status) = 'CLOSED'
                             OR UPPER (O.fault_status) = 'REJECTED')
                  LEFT OUTER JOIN CUPID C
                     ON     FLT1.CUPID = C.CUPID
                        AND C.REC_END_DATE =
                               TO_DATE ('31/12/9999 23:59:59',
                                        'DD/MM/YYYY HH24:MI:SS')
                  LEFT OUTER JOIN
                  (SELECT *
                     FROM GEA_ASSET
                    WHERE REC_END_DATE =
                             TO_DATE ('31/12/9999 23:59:59',
                                      'DD/MM/YYYY HH24:MI:SS')) ASSET
                     ON FLT1.CIRCUIT_ID = ASSET.CIRCUIT_ID
                  LEFT OUTER JOIN
                  (SELECT FAULT_ID, REVISED_COMMITMENT_DATE, INITIAL_MFL
                     FROM (SELECT FAULT_ID,
                                  REVISED_COMMITMENT_DATE,
                                  INITIAL_MFL,
                                  ROW_NUMBER ()
                                  OVER (PARTITION BY FAULT_ID
                                        ORDER BY SRC_ACTVTY_DATE DESC)
                                     ROW_IND
                             FROM WORK_ARRANGE_GEA_FAULTS)
                    WHERE ROW_IND = 1) RCT
                     ON RCT.FAULT_ID = FLT1.FAULT_ID
                  LEFT OUTER JOIN PORTL_MBORC_DTLS PORTL
                     ON FLT1.MBORC_REF = PORTL.MBORC_ID)

Now the plans for today starkly differ from what it was yesterday :

Todays' sub-optimal plan :
-----------------------------

Plan hash value: 3268238723
 
----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                              |                              |     1 |  2256 | 63756   (1)| 00:00:05 |       |       |
|   1 |  LOAD TABLE CONVENTIONAL                      | ALD_EXTRACT_TEMP_GEA_R_CMP_2 |       |       |            |          |       |       |
|*  2 |   TABLE ACCESS FULL                           | COMPENSATION_REF             |     1 |    69 |     7   (0)| 00:00:01 |       |       |
|   3 |   VIEW                                        |                              |     1 |  2256 | 63756   (1)| 00:00:05 |       |       |
|   4 |    HASH UNIQUE                                |                              |     1 |  1520 | 63756   (1)| 00:00:05 |       |       |
|*  5 |     HASH JOIN SEMI                            |                              |     1 |  1520 | 63755   (1)| 00:00:05 |       |       |
|   6 |      NESTED LOOPS OUTER                       |                              |     1 |  1501 | 63751   (1)| 00:00:05 |       |       |
|*  7 |       HASH JOIN OUTER                         |                              |     1 |  1484 | 63751   (1)| 00:00:05 |       |       |
|   8 |        NESTED LOOPS OUTER                     |                              |     1 |  1428 | 63737   (1)| 00:00:05 |       |       |
|*  9 |         HASH JOIN                             |                              |     1 |  1358 | 63737   (1)| 00:00:05 |       |       |
|  10 |          NESTED LOOPS OUTER                   |                              |     1 |  1349 | 63734   (1)| 00:00:05 |       |       |
|  11 |           NESTED LOOPS                        |                              |     1 |  1064 | 63477   (1)| 00:00:05 |       |       |
|  12 |            PARTITION RANGE ALL                |                              |     1 |    38 | 62454   (1)| 00:00:05 |     1 |   127 |
|* 13 |             TABLE ACCESS FULL                 | OS_GEA_FAULTS                |     1 |    38 | 62454   (1)| 00:00:05 |     1 |   127 |
|  14 |            PARTITION RANGE ALL                |                              |     2 |  2052 |  1023   (0)| 00:00:01 |     1 |   127 |
|  15 |             PARTITION LIST ALL                |                              |     2 |  2052 |  1023   (0)| 00:00:01 |     1 |     4 |
|* 16 |              TABLE ACCESS BY LOCAL INDEX ROWID| GEA_FAULT_DETAIL_FACT        |     2 |  2052 |  1023   (0)| 00:00:01 |     1 |   508 |
|* 17 |               INDEX RANGE SCAN                | GEA_FAULT_DETAIL_FACT_PK     |     7 |       |  1016   (0)| 00:00:01 |     1 |   508 |
|  18 |           PARTITION RANGE ALL                 |                              |     1 |   285 |   257   (0)| 00:00:01 |     1 |   127 |
|* 19 |            TABLE ACCESS BY LOCAL INDEX ROWID  | GEA_ASSET                    |     1 |   285 |   257   (0)| 00:00:01 |     1 |   127 |
|* 20 |             INDEX RANGE SCAN                  | FTTC_ASSET_IDX               |     2 |       |   254   (0)| 00:00:01 |     1 |   127 |
|  21 |          TABLE ACCESS FULL                    | ALD_CONTROL_TABLE            |     1 |     9 |     3   (0)| 00:00:01 |       |       |
|* 22 |         TABLE ACCESS BY INDEX ROWID           | CUPID                        |     1 |    70 |     0   (0)| 00:00:01 |       |       |
|* 23 |          INDEX RANGE SCAN                     | CUPID_PK                     |     1 |       |     0   (0)| 00:00:01 |       |       |
|* 24 |        VIEW                                   |                              |  1903 |   104K|    13   (8)| 00:00:01 |       |       |
|* 25 |         WINDOW SORT PUSHED RANK               |                              |  1903 | 98956 |    13   (8)| 00:00:01 |       |       |
|  26 |          TABLE ACCESS FULL                    | WORK_ARRANGE_GEA_FAULTS      |  1903 | 98956 |    12   (0)| 00:00:01 |       |       |
|  27 |       TABLE ACCESS BY GLOBAL INDEX ROWID      | PORTL_MBORC_DTLS             |     2 |    34 |     2   (0)| 00:00:01 | ROWID | ROWID |
|* 28 |        INDEX RANGE SCAN                       | PORTL_MBORC_DTLS_PK          |     1 |       |     1   (0)| 00:00:01 |       |       |
|* 29 |      TABLE ACCESS FULL                        | NGA_PROD_CODE                |     3 |    57 |     4   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------


Now as per the previous plan it should have been a hash join throughout, a parallelized query instead of a serialized one above and there were no indexes being scanned anywhere at all.

So many changes to a sql id on the same day seems odd.

I have checked the stats and refreshed wherever found them as stale, checked if there were changes in the sql code -- no changes at all.
I know there may be more details required for this query but would request you to accept this case and I can then provide all the relevant details as required.

Many thanks
Rohit

and Connor said...

Take a look here

https://asktom.oracle.com/Misc/oramag/on-seeing-double-in-vsql.html

and see if there are multiple entries in v$sql_shared_cursor

If not, then a most likely cause is cardinality feedback, where the optimizer tries to improve upon an initial sub-optimal plan.

https://blogs.oracle.com/optimizer/post/statistics-feedback-formerly-cardinality-feedback
https://blogs.oracle.com/optimizer/post/cardinality-and-dynamic-statistics

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9534360500346333173


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Some links don't work

Migs, July 20, 2023 - 12:43 pm UTC

Team, can you kindly update some of the links? it's not working anymore. thanks!
Connor McDonald
August 03, 2023 - 10:39 am UTC

Links corrected.

Thanks for reporting

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.