Archives of the TeradataForum

Message Posted: Thu, 12 Jul 2012 @ 10:15:44 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

Can you help me on the possible ways to tune the query. The? query is consuming High Spool. (The data is very huge around 500GB)

The metrics in production for the below query is Final Spool Usage: 14.8TB Final CPU Usage: 386K CPU Seconds Peak CPU Skew (during heaviest load period): 59% Peak I/O Skew (during heaviest load period): 55%

The below query is generated using the Push Down optimization in informatica.

     INSERT INTO REFERENCE_W.INVL_PRTY_T_INVL_PRTY_CIS_WRK1(CHLD_OWN_PC,
     ETL_SRC_FILE_NB, ETL_FILE_TYPE_CD, CRE_RUN_ID, EFF_TS, END_TS, ETL_ROW_NB,
     LAST_UPDT_RUN_ID, SBJ_INVL_PRTY_ID, OBJ_INVL_PRTY_ID, SRC_SYS_CD,
     ETL_ACTN_CD, REL_END_DT, OBJ_SRC_SYS_UNQ_KEY_TX, SBJ_SRC_SYS_UNQ_KEY_TX,
     SBJ_SRC_SYS_CD, INVL_PRTY_TO_INVL_PRTY_TYPE_CD, ETL_LD_DT, REL_EFF_DT,
     GEMINI_SBLG_TYPE_CD, OBJ_SRC_SYS_CD)

     SELECT sc_INVL_PRTY_TO_INVL_PRTY_SA2.CHLD_OWN_PC,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.ETL_SRC_FILE_NB,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.ETL_FILE_TYPE_CD,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.CRE_RUN_ID,
     CAST(CAST('2012-07-05 00:00:00' AS TIMESTAMP FORMAT 'YYYY-MM-DDBHH:MI:SS')
     AS timestamp), CAST(CAST('9999-12-31 00:00:00' AS TIMESTAMP FORMAT
     'YYYY-MM-DDBHH:MI:SS') AS timestamp),
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.ETL_ROW_NB, 53228,
     PM_Alkp_INVL_PRTY_KEY_2.INVL_PRTY_ID, PM_Alkp_INVL_PRTY_KEY_1.INVL_PRTY_ID,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.SRC_SYS_CD,
     (CASE WHEN (PM_Alkp_INVL_PRTY_KEY_1.INVL_PRTY_ID IS NULL OR
     PM_Alkp_INVL_PRTY_KEY_2.INVL_PRTY_ID IS NULL) THEN 'I' ELSE
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.ETL_ACTN_CD END),
     CAST(cast(sc_INVL_PRTY_TO_INVL_PRTY_SA2.REL_END_DT as timestamp(6)) AS
     date), sc_INVL_PRTY_TO_INVL_PRTY_SA2.OBJ_SRC_SYS_UNQ_KEY_TX,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.SBJ_SRC_SYS_UNQ_KEY_TX,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.SBJ_SRC_SYS_CD,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.INVL_PRTY_TO_INVL_PRTY_TYPE_CD,
     CAST(cast(sc_INVL_PRTY_TO_INVL_PRTY_SA2.ETL_LD_DT as timestamp(6)) AS date),
     CAST(cast(sc_INVL_PRTY_TO_INVL_PRTY_SA2.REL_EFF_DT as timestamp(6)) AS
     date),
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.GEMINI_SBLG_TYPE_CD,
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.OBJ_SRC_SYS_CD

     FROM ((REFERENCE_W.INVL_PRTY_TO_INVL_PRTY_CIS_SA2
     sc_INVL_PRTY_TO_INVL_PRTY_SA2
     LEFT OUTER JOIN ICDW_SPROC_V.INVL_PRTY_KEY PM_Alkp_INVL_PRTY_KEY_1 ON
     ((PM_Alkp_INVL_PRTY_KEY_1.SRC_SYS_UNQ_KEY_TX =
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.OBJ_SRC_SYS_UNQ_KEY_TX)
     AND (PM_Alkp_INVL_PRTY_KEY_1.SRC_SYS_CD = (CASE WHEN (CHARACTER_LENGTH('') >
     0) THEN '' ELSE sc_INVL_PRTY_TO_INVL_PRTY_SA2.OBJ_SRC_SYS_CD END))))
     LEFT OUTER JOIN ICDW_SPROC_V.INVL_PRTY_KEY PM_Alkp_INVL_PRTY_KEY_2 ON
     ((PM_Alkp_INVL_PRTY_KEY_2.SRC_SYS_UNQ_KEY_TX =
     sc_INVL_PRTY_TO_INVL_PRTY_SA2.SBJ_SRC_SYS_UNQ_KEY_TX)
     AND (PM_Alkp_INVL_PRTY_KEY_2.SRC_SYS_CD = (CASE WHEN (CHARACTER_LENGTH('') >
     0) THEN '' ELSE sc_INVL_PRTY_TO_INVL_PRTY_SA2.SBJ_SRC_SYS_CD END))))

     WHERE (NOT (PM_Alkp_INVL_PRTY_KEY_1.INVL_PRTY_ID IS NULL) AND NOT
     (PM_Alkp_INVL_PRTY_KEY_2.INVL_PRTY_ID IS NULL))

Thanks,

Vijay Mani

	<Prev	Next>		<<First	<Prev	Next>	Last>>