No More Spool Space Issue

General
Fan

No More Spool Space Issue

Hi I have error: No more spool space in CRM_user. {HY000,NativeErr = -2646} when I qurey one of my views.

User have enough spool space (250GB).

 

Explain plan for query is:

 

1) First, we lock DP99_Category.T9987_Application_Names in view
     vp30_mis.mta_communication for access, we lock
     DP99_Category.T9902_SERV_CDR_HIER in view
     vp30_mis.mta_communication for access, and we lock
    DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication for access.
  2) Next, we do an all-AMPs RETRIEVE step from 4551 partitions of
     DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication with a condition of (
     "(DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication.CALL_START_DT >= DATE '2017-09-01') AND
     ((DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication.CALL_DIRECTION_IND = 'O') AND
     (((DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication.A_COUNTRY_CD (FLOAT, FORMAT
     '-9.99999999999999E-999'))= 3.82000000000000E 002) AND
     ((DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication.A_AREA_CD (FLOAT, FORMAT
     '-9.99999999999999E-999'))= 6.90000000000000E 001 )))") into Spool
     5 (all_amps) (compressed columns allowed), which is redistributed
     by hash code to all AMPs with hash fields (
     "DP11_Call_History.T1100_CALL_HIST.SERV_CDR_ID").  Then we do a
     SORT to order Spool 5 by row hash.  The size of Spool 5 is
     estimated with low confidence to be 1 row (51 bytes).  Spool
     AsgnList:
     "A_ACCS_METH_NUM" = "A_ACCS_METH_NUM",
     "B_ACCS_METH_NUM" = "B_ACCS_METH_NUM",
     "CALL_DURATION_QTY" = "CALL_DURATION_QTY",
     "SERV_CDR_ID" = "DP11_Call_History.T1100_CALL_HIST.SERV_CDR_ID".
     BEGIN RECOMMENDED STATS FOR THIS Relation->
     <-END RECOMMENDED STATS
     The estimated time for this step is 6 minutes and 29 seconds.
  3) We do an all-AMPs JOIN step (Global sum) from Spool 5 (Last Use)
     by way of a RowHash match scan, which is joined to
     DP99_Category.T9902_SERV_CDR_HIER in view
     vp30_mis.mta_communication by way of a RowHash match scan with a
     condition of ("(DP99_Category.T9902_SERV_CDR_HIER in view
     vp30_mis.mta_communication.serv_cdr_level_4_id = 6) OR
     (DP99_Category.T9902_SERV_CDR_HIER in view
     vp30_mis.mta_communication.serv_cdr_level_4_id = 5)").  Spool 5
     and DP99_Category.T9902_SERV_CDR_HIER are joined using a merge
     join, with a join condition of ("Spool_5.SERV_CDR_ID =
     DP99_Category.T9902_SERV_CDR_HIER.serv_cdr_id").  The result goes
     into Spool 4 (all_amps) (compressed columns allowed), which is
     built locally on the AMPs with Field1 ("-20422").  The size of
     Spool 4 is estimated with low confidence to be 1 row (138 bytes).
     Spool AsgnList:
     "Field_1" = "-20422",
     "Spool_4.A_ACCS_METH_NUM" = "{ Copy }{LeftTable}.A_ACCS_METH_NUM",
     "Spool_4.serv_cdr_level_4_desc" = "{ Copy
     }{RightTable}.serv_cdr_level_4_desc",
     "Spool_4.B_ACCS_METH_NUM" = "{ Copy }{LeftTable}.B_ACCS_METH_NUM",
     "Spool_4.CALL_DURATION_QTY" = "{ Copy
     }{LeftTable}.CALL_DURATION_QTY",
     "Spool_4.serv_cdr_level_4_id" = "{ Copy
     }{RightTable}.serv_cdr_level_4_id".
     BEGIN RECOMMENDED STATS FOR THIS Relation->
     <-END RECOMMENDED STATS
     The estimated time for this step is 0.00 seconds.
  4) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by
     way of an all-rows scan, and the grouping identifier in field 1.
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 6.  The size of Spool 6 is estimated with low confidence
     to be 1 row (457 bytes).  The estimated time for this step is 0.02
     seconds.
  5) We do an all-AMPs STAT FUNCTION step from Spool 6 (Last Use) by
     way of an all-rows scan into Spool 10 (Last Use), which is
     redistributed by hash code to all AMPs.  The result rows are put
     into Spool 1 (group_amps), which is built locally on the AMPs with
     Field1 ("Field_1").  The size is estimated with low confidence to
     be 1 row (827 bytes).  The estimated time for this step is 0.01
     seconds.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 6 minutes and 29 seconds.

 

 

All columns of underlying tables have fresh statistics. No recommendations. I think that, for some reasons, Optimizer create too small Spool for this query.

Does anyone have idea what is wrong with this execution plan?

Tags (1)
3 REPLIES
Senior Supporter

Re: No More Spool Space Issue

Could it be that the condition on the

 

vp30_mis.mta_communication.A_COUNTRY_CD (FLOAT, FORMAT
     '-9.99999999999999E-999'))= 3.82000000000000E 002) AND
     ((DP11_Call_History.T1100_CALL_HIST in view
     vp30_mis.mta_communication.A_AREA_CD (FLOAT, FORMAT
     '-9.99999999999999E-999'))= 6.90000000000000E 001 )))") i

is not matching the data type of the two columns?

This would result in conversions and you loose the stats info...

 

 

Fan

Re: No More Spool Space Issue

Hi, thank you for replay.

 

Both columns are the same data type:

 

A_AREA_CD CHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('63   ','67   ','69   '),
A_COUNTRY_CD CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS '382',

 

Regards.

Senior Supporter

Re: No More Spool Space Issue

The sql is using in this case the wrong condition on these columns

I think it is:

vp30_mis.mta_communication.A_COUNTRY_CD = 382

vp30_mis.mta_communication.A_AREA_CD = 69

 

but should be

vp30_mis.mta_communication.A_COUNTRY_CD = '382'

vp30_mis.mta_communication.A_AREA_CD = '69'

 

can you change it and rerun the explain and query