2 Replies Latest reply on Jan 5, 2016 9:06 PM by rp0428

    How to tune this query?

    2847712

      When I check the query plan, the cost is around 7 million. Does it mean 7 million milliseconds? Below is the query. Can someone help me tune it? Any help is highly appreciated. Thanks.

       

      Query:

       

       

       

      SELECT EMPLID as EmployeeID, G_PLAN_CTR as PlanningCenter, JOB_FUNCTION as JobFunction, MANAGER_LEVEL as ManagerLevel, FULL_PART_TIME as FullPartTime, ROUND(NVL(FTE,0), 2) AS FTE FROM

      (

       

       

      SELECT X.EMPLID, X.G_PLAN_CTR, X.JOB_FUNCTION, X.MANAGER_LEVEL, X.FULL_PART_TIME, Y.FTE FROM

      (

       

       

      SELECT A.EMPLID, A.G_PLAN_CTR, D.JOB_FUNCTION, C.MANAGER_LEVEL, A.FULL_PART_TIME

       

       

        FROM PS_JOB A, PS_LOCATION_TBL B, PS_JOBCODE_TBL C, PS_JOBFUNCTION_TBL D

        WHERE ( A.EFFDT =

              (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED

              WHERE A.EMPLID = A_ED.EMPLID

                AND A.EMPL_RCD = A_ED.EMPL_RCD

                AND A_ED.EFFDT <= SYSDATE-10)

          AND A.ACTION_DT <= SYSDATE-10

          AND A.EFFSEQ =

              (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES

              WHERE A.EMPLID = A_ES.EMPLID

                AND A.EMPL_RCD = A_ES.EMPL_RCD

                AND A.EFFDT = A_ES.EFFDT)

           AND A.COMPANY NOT IN ('PEN','GFR','CON','BUS')

           AND A.EMPL_STATUS IN ('A')

           AND A.PER_ORG = 'EMP'

           AND B.LOCATION = A.LOCATION

           AND B.EFFDT =

              (SELECT MAX(B_ED.EFFDT) FROM PS_LOCATION_TBL B_ED

              WHERE B.SETID = B_ED.SETID

                AND B.LOCATION = B_ED.LOCATION

                AND B_ED.EFFDT <= A.EFFDT) )

               

               

           AND A.JOBCODE = C.JOBCODE

           AND C.EFFDT =

              (SELECT MAX(C_ED.EFFDT) FROM PS_JOBCODE_TBL C_ED

              WHERE C.SETID = C_ED.SETID

                AND C.JOBCODE = C_ED.JOBCODE

                AND C_ED.EFFDT <= A.EFFDT)

                  

           AND C.JOB_FUNCTION = D.JOB_FUNCTION

           AND D.JOB_FUNCTION IN

      (

      'ADX',

      'CEN',

      'COM',

      'CSR',

      'ITS',

      'LIA',

      'MOT',

      'PIP',

      'SAL',

      'SER',

      'STC',

      'SUP',

      'UND')

       

       

      ) X

       

       

      LEFT OUTER JOIN

       

       

      (

       

       

      SELECT EMPLID, NVL(SUM(OTH_HRS)/77.5, 0)  AS FTE FROM

      (

       

       

       

       

      SELECT DISTINCT

      PS_PAY_EARNINGS.EMPLID AS EMPLID, PS_PAY_OTH_EARNS.ERNCD,  PS_PAY_OTH_EARNS.OTH_HRS AS OTH_HRS

       

       

      FROM HRPRDADM.PS_PAY_EARNINGS

       

       

      INNER JOIN HRPRDADM.PS_PAY_OTH_EARNS ON

      PS_PAY_EARNINGS.COMPANY = PS_PAY_OTH_EARNS.COMPANY

      AND PS_PAY_EARNINGS.PAYGROUP = PS_PAY_OTH_EARNS.PAYGROUP

      AND PS_PAY_EARNINGS.PAY_END_DT = PS_PAY_OTH_EARNS.PAY_END_DT

      AND PS_PAY_EARNINGS.OFF_CYCLE = PS_PAY_OTH_EARNS.OFF_CYCLE

      AND PS_PAY_EARNINGS.LINE_NUM = PS_PAY_OTH_EARNS.LINE_NUM

      AND PS_PAY_EARNINGS.SEPCHK = PS_PAY_OTH_EARNS.SEPCHK

      AND PS_PAY_EARNINGS.PAGE_NUM = PS_PAY_OTH_EARNS.PAGE_NUM

      AND PS_PAY_EARNINGS.ADDL_NBR = PS_PAY_OTH_EARNS.ADDL_NBR

       

       

      INNER JOIN HRPRDADM.PS_JOB ON PS_JOB.EMPLID = PS_PAY_EARNINGS.EMPLID AND PS_JOB.EMPL_RCD = PS_PAY_EARNINGS.EMPL_RCD

       

       

      WHERE

       

       

      PS_JOB.EFFDT =

      (SELECT MAX(PS_JOB2.EFFDT)

      FROM HRPRDADM.PS_JOB PS_JOB2

      WHERE PS_JOB.EMPLID = PS_JOB2.EMPLID

      AND PS_JOB.EMPL_RCD = PS_JOB2.EMPL_RCD

      AND PS_JOB2.EFFDT <= Current_Date)

       

       

      AND PS_JOB.EFFSEQ =

      (SELECT MAX(PS_JOB3.EFFSEQ)

      FROM HRPRDADM.PS_JOB PS_JOB3

      WHERE PS_JOB.EMPLID = PS_JOB3.EMPLID

      AND PS_JOB.EMPL_RCD = PS_JOB3.EMPL_RCD

      AND PS_JOB.EFFDT = PS_JOB3.EFFDT)

       

       

      AND PS_JOB.EMPL_RCD = '0'

       

       

      AND PS_JOB.COMPANY NOT IN ('PEN', 'GFR', 'CON')

       

       

      AND PS_JOB.EMPLID IN

       

       

      (

      SELECT A.EMPLID

        FROM PS_JOB A, PS_LOCATION_TBL B, PS_JOBCODE_TBL C, PS_JOBFUNCTION_TBL D

        WHERE ( A.EFFDT =

              (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED

              WHERE A.EMPLID = A_ED.EMPLID

                AND A.EMPL_RCD = A_ED.EMPL_RCD

                AND A_ED.EFFDT <= SYSDATE-10)

          AND A.ACTION_DT <= SYSDATE-10

          AND A.EFFSEQ =

              (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES

              WHERE A.EMPLID = A_ES.EMPLID

                AND A.EMPL_RCD = A_ES.EMPL_RCD

                AND A.EFFDT = A_ES.EFFDT)

           AND A.COMPANY NOT IN ('PEN','GFR','CON','BUS')

           AND A.EMPL_STATUS IN ('A')

           AND A.PER_ORG = 'EMP'

           AND B.LOCATION = A.LOCATION

           AND B.EFFDT =

              (SELECT MAX(B_ED.EFFDT) FROM PS_LOCATION_TBL B_ED

              WHERE B.SETID = B_ED.SETID

                AND B.LOCATION = B_ED.LOCATION

                AND B_ED.EFFDT <= A.EFFDT) )

               

           AND A.JOBCODE = C.JOBCODE

           AND C.EFFDT =

              (SELECT MAX(C_ED.EFFDT) FROM PS_JOBCODE_TBL C_ED

              WHERE C.SETID = C_ED.SETID

                AND C.JOBCODE = C_ED.JOBCODE

                AND C_ED.EFFDT <= A.EFFDT)

                  

           AND C.JOB_FUNCTION = D.JOB_FUNCTION

           AND D.JOB_FUNCTION IN

      (

      'ADX',

      'CEN',

      'COM',

      'CSR',

      'ITS',

      'LIA',

      'MOT',

      'PIP',

      'SAL',

      'SER',

      'STC',

      'SUP',

      'UND'

      )

       

       

      )

       

       

      AND PS_PAY_OTH_EARNS.PAY_END_DT = TO_DATE(SYSDATE-10, 'DD-MON-YY')

      AND PS_PAY_OTH_EARNS.ERNCD IN

      ('FFH', 'FLP', 'FLS', 'FLT', 'FLV', 'FPV', 'HOL', 'JRY', 'MFH', 'MIL', 'MVL', 'OTM', 'OTO', 'OTR', 'PMV', 'PSV',

      'PTJ', 'PTV', 'PUV', 'REG', 'SCK', 'SMF', 'SMP', 'SMS', 'SMV', 'UFH', 'UPL', 'UVL', 'VAC', 'VAJ', 'WWP', 'JPS', 'BRV', 'PER', 'SLM' )

       

       

      ) X1

       

       

      GROUP BY X1.EMPLID

      ) Y

       

       

      ON X.EMPLID = Y.EMPLID

       

       

       

       

      )Z;