Spool error

General
Enthusiast

Spool error

I have a query which is spooling out, it is a simple query and I have tried to write this in many ways and also collect stats on all the join columns but still it is spooling out. Please suggest me on how to handle this.

    CUST_D_STG.GAS_DLY_USG_SI  - 4.5 million rows

   CUST_D_STG.CI_BF_VAL  -- 200000 rows

explain

UPDATE    TGT

FROM    

    CUST_D_STG.GAS_DLY_USG_SI TGT ,

(      SELECT    BF_CD,CHAR_TYPE_CD,val.CHAR_VAL,EFFDT,VAL ,stg.sp_id,stg.mtr_id,stg.usg_Dt

FROM    

CUST_D_STG.CI_BF_VAL val  inner join CUST_D_STG.GAS_DLY_USG_SI STG

on   stg.CHAR_VAL = val.CHAR_VAL

WHERE    

 val.BF_CD = 'THFACTOR'

    AND     val.CHAR_TYPE_CD =  'THRMAREA' and

     val.EFFDT <= STG.USG_DT

    and stg.char_val is not null

QUALIFY    ROW_NUMBER() OVER(PARTITION BY  BF_CD,CHAR_TYPE_CD,val.char_val

ORDER    BY EFFDT DESC) = 1

                                      )SRC

                                            SET  thrm_fctr  = SRC.VAL

                                            WHERE SRC.SP_ID =TGT.SP_ID

                                            AND SRC.MTR_ID = TGT.MTR_ID   

                                     AND SRC.USG_DT =TGT.USG_DT

  1) First, we lock a distinct CUST_D_STG."pseudo table" for read on a

     RowHash to prevent global deadlock for CUST_D_STG.val.

  2) Next, we lock a distinct CUST_D_STG."pseudo table" for write on a

     RowHash to prevent global deadlock for CUST_D_STG.GAS_DLY_USG_SI.

  3) We lock CUST_D_STG.val for read, and we lock

     CUST_D_STG.GAS_DLY_USG_SI for write.

  4) We do an all-AMPs RETRIEVE step from CUST_D_STG.STG by way of an

     all-rows scan with no residual conditions into Spool 3 (all_amps),

     which is duplicated on all AMPs.  The size of Spool 3 is estimated

     with high confidence to be 216 rows (9,504 bytes).  The estimated

     time for this step is 1.54 seconds.

  5) We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of an

     all-rows scan, which is joined to CUST_D_STG.val by way of an

     all-rows scan with a condition of ("(CUST_D_STG.val.CHAR_TYPE_CD =

     'THRMAREA') AND (CUST_D_STG.val.BF_CD = 'THFACTOR')").  Spool 3

     and CUST_D_STG.val are joined using a dynamic hash join, with a

     join condition of ("(char_val = CUST_D_STG.val.CHAR_VAL) AND

     (CUST_D_STG.val.EFFDT <= usg_dt)").  The result goes into Spool 2

     (all_amps), which is built locally on the AMPs.  The size of Spool

     2 is estimated with low confidence to be 403 rows (26,598 bytes).

     The estimated time for this step is 0.04 seconds.

  6) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by

     way of an all-rows scan into Spool 6 (Last Use), which is

     redistributed by hash code to all AMPs.  The result rows are put

     into Spool 4 (all_amps), which is built locally on the AMPs.  The

     size is estimated with low confidence to be 403 rows (38,285

     bytes).

  7) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of

     an all-rows scan with a condition of ("Field_6 = 1") into Spool 1

     (used to materialize view, derived table or table function SRC)

     (all_amps), which is built locally on the AMPs.  The size of Spool

     1 is estimated with low confidence to be 237 rows (12,561 bytes).

     The estimated time for this step is 0.03 seconds.

  8) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of

     an all-rows scan into Spool 10 (all_amps), which is redistributed

     by the rowkey of (CUST_D_STG.STG.sp_id, CUST_D_STG.STG.usg_dt) to

     all AMPs.  Then we do a SORT to partition Spool 10 by rowkey.  The

     size of Spool 10 is estimated with low confidence to be 237 rows (

     10,665 bytes).  The estimated time for this step is 0.06 seconds.

  9) We do an all-AMPs JOIN step from CUST_D_STG.GAS_DLY_USG_SI by way

     of a RowHash match scan with no residual conditions, which is

     joined to Spool 10 (Last Use) by way of a RowHash match scan.

     CUST_D_STG.GAS_DLY_USG_SI and Spool 10 are joined using a

     rowkey-based merge join, with a join condition of ("(SP_ID =

     CUST_D_STG.GAS_DLY_USG_SI.sp_id) AND ((MTR_ID =

     CUST_D_STG.GAS_DLY_USG_SI.mtr_id) AND (USG_DT =

     CUST_D_STG.GAS_DLY_USG_SI.usg_dt ))").  The result goes into Spool

     9 (all_amps), which is built locally on the AMPs.  Then we do a

     SORT to order Spool 9 by the sort key in spool field1 (

     CUST_D_STG.GAS_DLY_USG_SI.ROWID).  The size of Spool 9 is

     estimated with low confidence to be 238 rows (6,188 bytes).  The

     estimated time for this step is 0.02 seconds.

 10) We do a MERGE Update to CUST_D_STG.GAS_DLY_USG_SI from Spool 9

     (Last Use) via ROWID.  The size is estimated with low confidence

     to be 238 rows (32,606 bytes).  The estimated time for this step

     is 0.37 seconds.

 11) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> No rows are returned to the user as the result of statement 1.

     BEGIN RECOMMENDED STATS ->

 12) "COLLECT STATISTICS COLUMN (SP_ID ,MTR_ID ,USG_DT) ON

     CUST_D_STG.GAS_DLY_USG_SI".  (LowConf)

     <- END RECOMMENDED STATS

4 REPLIES
Junior Contributor

Re: Spool error

Did you check dbc.QryLogStepsV which step failed?

Could you show the DDLs?

I would suggest to switch to a MERGE instead, this might be more efficient.

Enthusiast

Re: Spool error

DDL

1) CREATE SET TABLE CUST_D_STG.GAS_DLY_USG_SI ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      sp_id CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Service Point Id' NOT NULL,

      gas_dly_end_rdg_dttm TIMESTAMP(0) TITLE 'gas_dly_end_rdg_dttm' NOT NULL,

      mtr_id CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Meter Id' NOT NULL,

      usg_dt DATE FORMAT 'YYYY-MM-DD' TITLE 'usage date' NOT NULL,

      vee_sta_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'VEE Status Code' NOT NULL,

      est_rule_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Estimation Rule Code',

      gas_dly_end_rdg DECIMAL(13,3) TITLE 'gas_dly_end_rdg',

      gas_dly_uccf_usg_amt DECIMAL(18,7) TITLE 'gas_dly_uccf_usg_amt',

      rgstr_cnst DECIMAL(12,6) TITLE 'Register Constant',

      thrm_fctr DECIMAL(18,7) TITLE ' thrm_fctr',

      mdms_proc_dttm TIMESTAMP(0) TITLE 'MDMS Process Datetime',

      mtr_rdg_sys_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Meter Reading System Cd',

      dir_srce_sys_cd VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Dir Source System Code',

      intvl_lgth SMALLINT TITLE 'Interval Length' NOT NULL,

      uom VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'UOM' NOT NULL,

      prem_id CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Premise Location Id',

      char_val VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Premise Char Value',

      ins_btch_id INTEGER TITLE 'Insert_Batch_Id')

PRIMARY INDEX ( sp_id )

PARTITION BY RANGE_N(usg_dt  BETWEEN DATE '2008-03-01' AND DATE '2018-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE);

2) CREATE MULTISET TABLE CUST_D_STG.CI_BF_VAL ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      BF_CD VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'BF_CD' NOT NULL,

      CHAR_TYPE_CD VARCHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Char Type cd' NOT NULL,

      CHAR_VAL VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'Char Value',

      TOU_GRP_CD VARCHAR(12) CHARACTER SET LATIN NOT CASESPECIFIC TITLE 'TOU_GRP_CD',

      EFFDT DATE FORMAT 'YYYY-MM-DD' TITLE 'Effective dt' NOT NULL,

      VAL DECIMAL(18,7) NOT NULL,

      ins_btch_id INTEGER TITLE 'Insert_Batch_Id' NOT NULL DEFAULT 0 ,

      last_updt_btch_id INTEGER TITLE 'Last_Update_Batch_Id' NOT NULL DEFAULT 0 )

NO PRIMARY INDEX ;

Enthusiast

Re: Spool error

HI Dieter ,

above is the DDL .CUST_D_STG.CI_BF_VAL is created with no primary index because there are no columns which can give good distribution. currently this table is 13% skewed.

We tried creating PI on few columns then the skew % is going to 90 or 98 % .

I tried the same query with MERGE statement but still it is spooling .

Is there any other way to rewrite the query?

Junior Contributor

Re: Spool error

Can you do a HELP/SHOW STATS, too?

Number of rows and estimates don't match, is this the actual Explain?