Query is giving more spool space

Database

Query is giving more spool space

Sum(PeakSpool) Maximum((PeakSpool*VAmps))
126,620,605,440 259,258,613,760

The spool space is around 260GB.it should be decreased to below 100GB.The query is to be tuned.took the stats on the indexes mentioned in the explain plan.

Query:

SELECT

CASE WHEN DGCRMV03.VGCRM_CTCT_INDV.CGRY_TYPE_CODE = 'CTCT' THEN DGCRMV03.VGCRM_CTCT_INDV.CTSY_TITL_ABBR WHEN DGCRMV03.VGCRM_CTCT_INDV.CGRY_TYPE_CODE = 'EMPL' THEN DGCRMV03.VGCRM_INDV_NAME.CTSY_TITL_TEXT END,

DGCRMV03.VGCRM_INDV_NAME.FMLY_NAME,

DGCRMV03.VGCRM_INDV_NAME.GVN_NAME,

EMAIL_ADDR.ELC_ADDR_TEXT,

CTCT_TRT_ASGN_JOB_GR.TRT_VALU_TEXT,

CTCT_TRT_ASGN_LANG.TRT_VALU_TEXT,

PHON_ADDR_WRK_PH.PHON_NBR,

max(case when DGCRMV03.VGCRM_INDV_PRIV_PREF_HIST.PRIV_TYPE_CODE = 'EMAIL' and DGCRMV03.VGCRM_INDV_PRIV_PREF_HIST.INDV_PRIV_STRT_DT <= date and DGCRMV03.VGCRM_INDV_PRIV_PREF_HIST.INDV_PRIV_END_DT = '9999-12-31' then 'Y'

else 'N' end ),

ORG_NAME_HIST_ACCOUNT.ORG_NAME,

Account_Ext_Org.LOC_NAME,

MAILING_ADDR_PRIM.LN1_ADDR,

MAILING_ADDR_PRIM.LN2_ADDR,

MAILING_ADDR_PRIM.PSTL_CODE,

MAILING_ADDR_PRIM.CITY_NAME,

VGCRM_ORG_EXT_KER_ACT.NBR_OF_EMPL,

DW_CODE_ACCT_STUS.CODE_DESC_L,

DGCRMV03.VGCRM_CTCT_INDV.INDV_SRCE_ID,

VGCRM_ORG_EXT_KER_ACT.ORG_SRCE_ID,

VGCRM_ORG_EXT_KER_ACT.LANG_NAME,

Max(Case When (DGCRMV03.VGCRM_ORG_GRP_ASGN_HIST.ORG_GRP_CODE='CMPR' and DGCRMV03.VGCRM_ORG_GRP_ASGN_HIST.ASGN_END_DATE = '9999-12-31') Then 'Y' Else 'N' END),

cast(ORG_TRT_ASGN_BCL.TRT_TEXT as integer),

cast(ORG_TRT_ASGN_WCL.TRT_TEXT as integer),

DGCRMV03.VGCRM_INDUSTRY.INDUST_NAME,

DGCRMV03.VGCRM_INDUSTRY.INDUST_CODE,

count(distinct DGCRMV03.VGCRM_ACTV.GCRM_ACTV_KEY)

FROM

DGCRMV03.VGCRM_CTCT_TRT_ASGN CTCT_TRT_ASGN_LANG RIGHT JOIN DGCRMV03.VGCRM_CTCT_INDV ON CTCT_TRT_ASGN_LANG.GCRM_INDV_KEY=DGCRMV03.VGCRM_CTCT_INDV.GCRM_CTCT_KEY and CTCT_TRT_ASGN_LANG.TRT_CODE='LANG'

RIGHT JOIN DGCRMV03.VGCRM_ACCT_CTCT ON DGCRMV03.VGCRM_ACCT_CTCT.GCRM_CTCT_KEY=DGCRMV03.VGCRM_CTCT_INDV.GCRM_CTCT_KEY

RIGHT JOIN DGCRMV03.VGCRM_ORG_EXT VGCRM_ORG_EXT_KER_ACT ON DGCRMV03.VGCRM_ACCT_CTCT.GCRM_ORG_KEY=VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY

LEFT JOIN DGCRMV03.VGCRM_ORG_ACTV ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY = DGCRMV03.VGCRM_ORG_ACTV.GCRM_ORG_KEY And DGCRMV03.VGCRM_ORG_ACTV.ACTIVITY_ROLE_CODE='ACCTS'

LEFT JOIN DGCRMV03.VGCRM_ACTV ON DGCRMV03.VGCRM_ORG_ACTV.GCRM_ACTV_KEY=DGCRMV03.VGCRM_ACTV.GCRM_ACTV_KEY LEFT JOIN DGCRMV03.VGCRM_EXTERNAL_ORG Account_Ext_Org ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY=Account_Ext_Org.GCRM_ORG_KEY LEFT JOIN DGCRMV03.VGCRM_ORG_NAME_HIST ORG_NAME_HIST_ACCOUNT ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY=ORG_NAME_HIST_ACCOUNT.GCRM_ORG_KEY AND ORG_NAME_HIST_ACCOUNT.ALT_ORG_TYPE_CODE = 'ORGNM' AND ORG_NAME_HIST_ACCOUNT.ORG_NAME_END_DATE = '9999-12-31' LEFT JOIN DGCRMV03.VGCRM_ORG_RLTN_HIST ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY=DGCRMV03.VGCRM_ORG_RLTN_HIST.RLTS_ORG_ID AND DGCRMV03.VGCRM_ORG_RLTN_HIST.ROLE_CODE IN ('BU','PRBU') and DGCRMV03.VGCRM_ORG_RLTN_HIST.ORG_RLTN_END_DATE='9999-12-31'

LEFT JOIN DGCRMV03.VGCRM_ORG_NAME_HIST ORG_NAME_HIST_RTLN_BU_PRBU ON DGCRMV03.VGCRM_ORG_RLTN_HIST.RLTD_ORG_ID = ORG_NAME_HIST_RTLN_BU_PRBU.GCRM_ORG_KEY AND ORG_NAME_HIST_RTLN_BU_PRBU.ALT_ORG_TYPE_CODE = 'ORGNM'

And ORG_NAME_HIST_RTLN_BU_PRBU.ORG_NAME_END_DATE = '9999-12-31' LEFT JOIN DGCRMV03.VGCRM_ORG_GRP_ASGN_HIST ON DGCRMV03.VGCRM_ORG_GRP_ASGN_HIST.GCRM_ORG_KEY=VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY LEFT JOIN DGCRMV03.VGCRM_ORG_STATUS_ASGN_HIST_SSC ACC_ORG_STATUS_ASGN_HIST_SSC ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY=ACC_ORG_STATUS_ASGN_HIST_SSC.GCRM_ORG_KEY AND ACC_ORG_STATUS_ASGN_HIST_SSC.ORG_STUS_END_DATE='9999-12-31'

LEFT JOIN DGCRMV03.VGCRM_DW_CODE DW_CODE_ACCT_STUS ON DW_CODE_ACCT_STUS.SYS_SRCE_CODE=ACC_ORG_STATUS_ASGN_HIST_SSC.SYS_SRCE_CODE and DW_CODE_ACCT_STUS.CODE_VALU=ACC_ORG_STATUS_ASGN_HIST_SSC.STUS_CODE and DW_CODE_ACCT_STUS.TBL_NAME='TGCRM_ORG_STATUS_ASGN_HIST' and DW_CODE_ACCT_STUS.CODE_NAME='STUS_CODE' LEFT JOIN DGCRMV03.VGCRM_BUS_INDUSTRY ON VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY=DGCRMV03.VGCRM_BUS_INDUSTRY.GCRM_ORG_KEY

LEFT JOIN DGCRMV03.VGCRM_INDUSTRY ON DGCRMV03.VGCRM_INDUSTRY.GCRM_INDUST_KEY=DGCRMV03.VGCRM_BUS_INDUSTRY.GCRM_INDUST_KEY LEFT JOIN DGCRMV03.VGCRM_ORG_ADDR_HIST ORG_ADDR_HIST_PRIM ON ORG_ADDR_HIST_PRIM.GCRM_ORG_KEY=VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY AND ORG_ADDR_HIST_PRIM.ADDR_TYPE_CODE='PRADR' AND ORG_ADDR_HIST_PRIM.ADDR_END_DATE= '9999-12-31'

LEFT JOIN DGCRMV03.VGCRM_MAILING_ADDR MAILING_ADDR_PRIM ON MAILING_ADDR_PRIM.GCRM_MAIL_ADDR_KEY=ORG_ADDR_HIST_PRIM.GCRM_ADDR_KEY LEFT JOIN (

select GCRM_ORG_KEY, TRT_TEXT from dgcrmv03.VGCRM_ORG_TRT_ASGN

where TRT_CODE = 'BCL'

Group by 1,2

) ORG_TRT_ASGN_BCL ON ORG_TRT_ASGN_BCL.GCRM_ORG_KEY=VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY LEFT JOIN (

select GCRM_ORG_KEY, TRT_TEXT from dgcrmv03.VGCRM_ORG_TRT_ASGN

where TRT_CODE = 'WCL'

Group by 1,2

) ORG_TRT_ASGN_WCL ON ORG_TRT_ASGN_WCL.GCRM_ORG_KEY=VGCRM_ORG_EXT_KER_ACT.GCRM_ORG_KEY LEFT JOIN DGCRMV03.VGCRM_INDV_NAME ON DGCRMV03.VGCRM_CTCT_INDV.GCRM_INDV_KEY=DGCRMV03.VGCRM_INDV_NAME.GCRM_INDV_KEY

LEFT JOIN DGCRMV03.VGCRM_INDV_PRIV_PREF_HIST ON DGCRMV03.VGCRM_INDV_PRIV_PREF_HIST.GCRM_INDV_KEY=DGCRMV03.VGCRM_INDV_NAME.GCRM_INDV_KEY LEFT JOIN DGCRMV03.VGCRM_CTCT_TRT_ASGN CTCT_TRT_ASGN_JOB_GR ON DGCRMV03.VGCRM_CTCT_INDV.GCRM_CTCT_KEY=CTCT_TRT_ASGN_JOB_GR.GCRM_INDV_KEY and CTCT_TRT_ASGN_JOB_GR.TRT_CODE='JOBGR' LEFT JOIN DGCRMV03.VGCRM_INDV_ADDR INDV_ADDR_HIST_WRKPH ON DGCRMV03.VGCRM_CTCT_INDV.GCRM_INDV_KEY=INDV_ADDR_HIST_WRKPH.GCRM_INDV_KEY and INDV_ADDR_HIST_WRKPH.ADDR_TYPE_CODE='WRKPH' and INDV_ADDR_HIST_WRKPH.INDV_ADDR_END_DATE='9999-12-31'

LEFT JOIN DGCRMV03.VGCRM_PHON_ADDR PHON_ADDR_WRK_PH ON INDV_ADDR_HIST_WRKPH.GCRM_ADDR_KEY=PHON_ADDR_WRK_PH.GCRM_PHON_ADDR_KEY LEFT JOIN DGCRMV03.VGCRM_INDV_ADDR INDV_ADDR_HIST_EML ON DGCRMV03.VGCRM_CTCT_INDV.GCRM_INDV_KEY=INDV_ADDR_HIST_EML.GCRM_INDV_KEY And INDV_ADDR_HIST_EML.ADDR_TYPE_CODE='EML' And INDV_ADDR_HIST_EML.INDV_ADDR_END_DATE='9999-12-31'

LEFT JOIN DGCRMV03.VGCRM_ELEC_ADDR EMAIL_ADDR ON INDV_ADDR_HIST_EML.GCRM_ADDR_KEY=EMAIL_ADDR.GCRM_ELC_ADDR_KEY

WHERE

ORG_NAME_HIST_RTLN_BU_PRBU.ORG_NAME In ( 'BE PMD','BE C&O','BE Dental','BE Medical','BE Alternate Care' )

GROUP BY

1,

2,

3,

4,

5,

6,

7,

9,

10,

11,

12,

13,

14,

15,

16,

17,

18,

19,

21,

22,

23,

24;

Thanks in advance
Krishna
1 REPLY

Re: Query is giving more spool space

Performance tuning/Query Tuning