Why exact same query is fast on prod env but slow in Dev env?

Question

I have one query which is taking 10-11 seconds in prod env, but the same query is taking too long to return data in Dev env. Table and indexes are same in both environments. (Both Prod and Dev are on Same VM) Both query returning different execution plan showing different indexes.

Prod Env in picking VMRCTTA1.VMRIACCNT_MC_EXTR_IDX Index but Dev Env is picking VMRCTTA1.VMRRACCNT_MC_EXTR_P(autogenerated from primary key) Index

Please help me with this.

Here is the query:

--INSERT INTO VMRCTTA1."VMRRMC_CD_SUMM"
SELECT
        ACCNT_CNTRY_CD,
        YTD,
        YEAR,
        MONTH,
        ACCNT_GSSN_CD,
        ACCNT_CD,
        ACCNT_MC_CD,
        COALESCE(REVNU,0)AS REVNU_P,
        COALESCE(QTY,0) AS QTY_PC,
        ACCNT_MC_DIV AS MC_DIV
    FROM
    (
        SELECT   ACCNT_CNTRY_CD,
            'Y' AS YTD,
            2019 AS YEAR,
            2 AS MONTH,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            SUM(COALESCE(ACCNT_NET_REVNU,0)) AS REVNU,
            SUM (COALESCE(ACCNT_QTY,0)) AS QTY,
            CASE WHEN ACCNT_MC_DIV = 'P' THEN 'P' WHEN  ACCNT_MC_DIV = 'T' THEN 'V' END AS ACCNT_MC_DIV
        FROM    VMRCTTA1.VMRRACCNT_MC_EXTR ME--,                VMRCTTA1.VMRRMC_CD_SPS_MAPPNG CM
        WHERE
--        ME.ACCNT_MC_CD = CM.MC_CD
            ACCNT_CNTRY_CD = 531
        AND ( ACCNT_YEAR = 2019 AND ACCNT_PERIOD <= 2 )
        AND  ACCNT_MC_DIV IN ('P','T')
        GROUP BY
            ACCNT_CNTRY_CD,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            ACCNT_MC_DIV
    )AS A

UNION

SELECT
    B.ACCNT_CNTRY_CD,
    B.YTD,
    B.YEAR,
    B.MONTH,
    B.ACCNT_GSSN_CD,
    B.ACCNT_CD,
    B.ACCNT_MC_CD,
    B.REVNU,
    B.QTY,
    B.DIV
FROM
(
    SELECT
        ACCNT_CNTRY_CD,
        YTD,
        YEAR,
        MONTH,
        ACCNT_GSSN_CD,
        ACCNT_CD,
        ACCNT_MC_CD,
        MAX(CASE WHEN ACCNT_MC_DIV ='P' THEN  COALESCE(REVNU,0) END) AS REVNU_P,
        MAX(CASE WHEN ACCNT_MC_DIV ='V' THEN  COALESCE(REVNU,0) END) AS REVNU_V,
        MAX(CASE WHEN  ACCNT_MC_DIV ='P' THEN  COALESCE(QTY,0) END) AS QTY_PC,
        MAX(CASE WHEN  ACCNT_MC_DIV ='V' THEN  COALESCE(QTY,0) END) AS QTY_VAN,
        'T' AS MC_DIV
    FROM
    (
        SELECT   ACCNT_CNTRY_CD,
            'Y' AS YTD,
            2019 AS YEAR,
            2 AS MONTH,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            SUM(COALESCE(ACCNT_NET_REVNU,0)) AS REVNU,
            SUM (COALESCE(ACCNT_QTY,0)) AS QTY,CASE WHEN ACCNT_MC_DIV = 'P' THEN 'P' WHEN  ACCNT_MC_DIV = 'T' THEN 'V' END AS ACCNT_MC_DIV
        FROM    VMRCTTA1.VMRRACCNT_MC_EXTR ME--,                VMRCTTA1.VMRRMC_CD_SPS_MAPPNG CM
        WHERE
--        ME.ACCNT_MC_CD = CM.MC_CD
            ACCNT_CNTRY_CD = 531
        AND ( ACCNT_YEAR = 2019 AND ACCNT_PERIOD <= 2 )
        AND  ACCNT_MC_DIV IN ('P','T')
        GROUP BY
            ACCNT_CNTRY_CD,
            ACCNT_GSSN_CD,
            ACCNT_CD,
            ACCNT_MC_CD,
            ACCNT_MC_DIV
    )AS A
    GROUP BY
    ACCNT_CNTRY_CD,
    YTD,
    YEAR,
    MONTH,
    ACCNT_GSSN_CD,
    ACCNT_CD,
    ACCNT_MC_CD
) AS A,
TABLE
(
VALUES
   (A.ACCNT_CNTRY_CD,A.YTD,A.YEAR,A.MONTH,A.ACCNT_GSSN_CD,A.ACCNT_CD,A.ACCNT_MC_CD,COALESCE(A.REVNU_V,0)+COALESCE(A.REVNU_P,0),COALESCE(A.QTY_PC,0)+COALESCE(A.QTY_VAN,0),'T')
)
AS      B(ACCNT_CNTRY_CD,YTD,YEAR,MONTH,ACCNT_GSSN_CD,ACCNT_CD,ACCNT_MC_CD,REVNU,QTY,DIV)

Answer 1

Since version 11.1 you can use embedded optimization guidelines to easily test if the index difference is the only issues here.

In your specific case you can append the following guideline to your query to force Db2 to pick VMRIACCNT_MC_EXTR_IDX index:

/* <OPTGUIDELINES><IXSCAN TABLE='VMRCTTA1.VMRRACCNT_MC_EXTR' INDEX='VMRIACCNT_MC_EXTR_IDX'/></OPTGUIDELINES> */

Please try that and:

• check if query indeed runs faster
• verify if you see expected index picked in the explain

If answer for both question is yes, then it means there must be something not quite right with the statistics or health of the indexes, e.g. on production index is more fragmented and that affects the costing during compilation. Assuming that problem would persist after REORG and RUNSTATS (I suggest WITH DISTRIBUTION AND DETAILED INDEXES ALL), feel free to upload full explains (db2exfmt) from PROD for the query with and without the guideline.

Answer 2

The Db2 optimizer is cost based. Besides the data model the number of rows, statistics (collected by runstats), configuration and resources are considered for the calcualtion of the costs. As an example the Db2 configuration has information about the CPUSpeed and this matters as well - so these values may be different between your development, test and production environments.

These differences can lead to different cost estimates can lead to different access plans.