Spool and performance

Database
Teradata Employee

Spool and performance

Hi

 

I have a query based on a table partitioned monthly and i need to perform aggregation on 3 months data with billion of rows. At the moment i am joining my table with only one lookup table. But spool is new to 1 terabyte and populating it to another table is taking 3 hours atleast.

Any suggestions please to improve spool and time both.

 

regards

18 REPLIES
Senior Apprentice

Re: Spool and performance

Hi,

 

We will typically need more info before we can realistically help you. Please post the table definitions, the sql and the explain plan.

 

A couple of simple pointers:

- do you have selection against the PPI column on your partitioned table

- does the plan show that your lookup table is being copied into spool (a typical choice for this type of query)

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Spool and performance

populating it to another table is taking 3 hours atleast

This sounds like you insert into a SET table with a bad Primary Index.

 

Besides the things Dave requested, do you have StepInfo from QueryLog?

Teradata Employee

Re: Spool and performance

Yes I am selecting against partitioned column with other criteria as well. Explain attached. Although stats has been collected but still getting no confidence in the explain. And it is not for just this query.

Target table is Multiset table and PI is based on next joining criteria. But query is taking more then two hours before merge step.

 

regards

EXPLAIN
INSERT INTO A_O_M
																		SELECT 201811 AS MONTH_ID
																						,USG.BASIC_CALL_TYPE_ID                                                                                          
																						,USG.Service_Type_Id
																						,USG.TOTAL_TYPE_ID                                                                                          
																						,USG.MSISDN                                                                                                                                                                                    
                                                                                        ,USG.CELL_ID                                                                                          
                                                                                        ,USG.LOCATION_AREA_ID                                                                                          
                                                                                          ,USG.Callindicator    
																						  ,TT.BASIC_CALL_SUBTYPE
																						  ,Sum(USG.TOTAL_DURATION) AS TOTAL_DURATION
                                                                                          ,Sum(USG.TOTAL_CALLS) AS TOTAL_CALLS
																						  ,Sum(USG.TOTAL_GROSS) AS TOTAL_GROSS																						  
																						  ,Count(MSISDN) AS NO_OF_TRANSACTIONS
                                                                       FROM 
																	     AGG_OUT_TR_MONTHLY_N USG
																		INNER JOIN TOTAL_TYPE TT 
																		ON USG.TOTAL_TYPE_ID = TT.TOTAL_TYPE																		
                                                                           WHERE USG.MONTH_ID BETWEEN To_Number(To_Char(Add_Months(To_Date('201811', 'yyyymm'), - 2), 'yyyymm'))  AND To_Number('201811')
																					    AND USG.Service_Type_Id NOT IN (51,61,21)   																		
                                                                        				AND CALLINDICATOR NOT IN ('SHD','SHP','SLF')
																						AND USG.BASIC_CALL_TYPE_ID IN (1,3,4,5,6,7)  																						
																						GROUP BY 1,2,3,4,5,6,7,8,9;

  1) First, we lock A_O_M for write on a reserved
     RowHash to prevent global deadlock.
  2) Next, we lock TOTAL_TYPE in view
     TOTAL_TYPE for access, we lock
      USG for access, and we lock A_O_M for
     write.
  3) We do an all-AMPs RETRIEVE step from
     TOTAL_TYPE in view
     TOTAL_TYPE by way of an all-rows scan
     with no residual conditions into Spool 4 (all_amps) (compressed
     columns allowed), which is duplicated on all AMPs.  The size of
     Spool 4 is estimated with high confidence to be 135,880 rows (
     3,940,520 bytes).  The estimated time for this step is 0.02
     seconds.
  4) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
     all-rows scan, which is joined to 3 partitions of  USG with
     a condition of ("( USG.Month_Id >= 201809) AND
     (( USG.BASIC_CALL_TYPE_ID IN (1 ,
3 TO 7 )) AND
     (( USG.Callindicator <> 'SHP') AND
     (( USG.Callindicator <> 'SHD') AND
     (( USG.Callindicator <> 'SLF') AND
     ((( USG.Service_Type_Id (FLOAT, FORMAT
     '-9.99999999999999E-999'))<> 2.10000000000000E 001) AND
     ((( USG.Service_Type_Id (FLOAT, FORMAT
     '-9.99999999999999E-999'))<> 5.10000000000000E 001) AND
     ((( USG.Service_Type_Id (FLOAT, FORMAT
     '-9.99999999999999E-999'))<> 6.10000000000000E 001) AND
     ( USG.Month_Id <= 201811 ))))))))").  Spool 4 and
      USG are joined using a dynamic hash join, with a join
     condition of ("( USG.Total_Type_Id )= (TOTAL_TYPE (FLOAT,
     FORMAT '-9.99999999999999E-999'))").  The input table  USG
     will not be cached in memory.  The result goes into Spool 3
     (all_amps) (compressed columns allowed), which is built locally on
     the AMPs.  The size of Spool 3 is estimated with no confidence to
     be 36,245,239 rows (4,349,428,680 bytes).  The estimated time for
     this step is 3 minutes and 35 seconds.
  5) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
     way of an all-rows scan , grouping by field1 ( 201811
     , USG.BASIC_CALL_TYPE_ID , USG.Service_Type_Id
     , USG.Total_Type_Id , USG.Msisdn
     , USG.Cell_Id , USG.Location_Area_Id
     , USG.Callindicator
     ,TOTAL_TYPE.BASIC_CALL_SUBTYPE).
     Aggregate Intermediate Results are computed locally, then placed
     in Spool 5.  The aggregate spool file will not be cached in memory.
     The size of Spool 5 is estimated with no confidence to be
     36,245,238 rows (12,105,909,492 bytes).  The estimated time for
     this step is 26.84 seconds.
  6) We do an all-AMPs RETRIEVE step from Spool 5 (Last Use) by way of
     an all-rows scan into Spool 1 (all_amps) (compressed columns
     allowed), which is redistributed by the hash code of (
      USG.Msisdn,  USG.Cell_Id,
      USG.Location_Area_Id) to all AMPs.  Then we do a SORT to
     order Spool 1 by row hash.  The size of Spool 1 is estimated with
     no confidence to be 36,245,238 rows (4,711,880,940 bytes).  The
     estimated time for this step is 11.09 seconds.
  7) We do an all-AMPs MERGE into A_O_M from Spool 1
     (Last Use).  The size is estimated with no confidence to be
     36,245,238 rows.  The estimated time for this step is 5 minutes
     and 40 seconds.
  8) We spoil the parser's dictionary cache for the table.
  9) 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.

 

Tags (1)
Teradata Employee

Re: Spool and performance

the query gets aborted after two hours, so it is not reaching the merge step actually.

Teradata Employee

Re: Spool and performance

Hi heena,

 

 

Don't just look at the elapsed time and Spool but also at I/O and AMPCPUTime metrics in DBQLogTbl.

Maybe you have a low priority on this job.

 

Are your statistics accurate ?

The optimizer thinks the amount of data pre and post aggregation is about 36 millions rows.

How did you collect the stats ?

 

Did you try to do the aggregation first and the join later (the joining column being in the result it's doable) ?

 

Also, what is the type of the column Service_Type_Id : there is a casting to float which may be useless.

 

Senior Apprentice

Re: Spool and performance

Hi,

 

A couple of things.

- Can you show the DDL for the tables?

- You say that it didn't reach the MERGE step. Do you know which step it was on when it was killed?

- You've got a couple of columns (as @Waldar mentioned) where there is a CAST to FLOAT. This often indicates a mis-match of data types.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Teradata Employee

Re: Spool and performance

Service type id is char in table.

In one run it gets aborted at aggregate step and in 2nd in the start of merge step ( guess based on previous performance)


CREATE MULTISET TABLE A_O_M ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( Month_Id INTEGER, BASIC_CALL_TYPE_ID INTEGER , Service_Type_Id CHAR(10) CHARACTER SET Latin NOT CaseSpecific, Total_Type_Id SMALLINT, Msisdn VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific, Cell_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific, Location_Area_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific , Callindicator VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific , BASIC_CALL_SUBTYPE VARCHAR(25) CHARACTER SET Latin NOT CaseSpecific, Total_Duration DECIMAL(18,4), Total_Gross DECIMAL(18,4) , Total_Calls DECIMAL(18,4) , NO_OF_TRANSACTIONS BIGINT ) --PRIMARY INDEX ( Month_Id ,Msisdn ) PRIMARY INDEX(Cell_Id,Location_Area_Id,MSISDN); --======================================================= CREATE MULTISET TABLE AGG_OUT_TR_MONTHLY_N ,NO FALLBACK , NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO ( Month_Id INTEGER, BASIC_CALL_TYPE_ID INTEGER Compress (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ), Onnet_Crossnet_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('0','A','C','F','O','Z'), Tap_In_Out_Ind CHAR(2) CHARACTER SET Latin NOT CaseSpecific Compress ('MO','MT','ST','TI'), Pricing_Plan_Id SMALLINT Compress (639 ,640 ,641 ), Service_Type_Id CHAR(10) CHARACTER SET Latin NOT CaseSpecific, Nat_Intl_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'), Total_Type_Id SMALLINT Compress (1 ,7 ,8 ,10 ,11 ,524 ,47 ,100 ,365 ,895 ,910 ,911 ,913 ,170 ,181 ,182 ,188 ,191 ,447 ,193 ,468 ,473 ,481 ,482 ,483 ,230 ,240 ,244 ,245 ), SERVICE_IND VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress '0', Fav_No_Ind CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('I','N'), DES_COUNTRY_ID VARCHAR(30) CHARACTER SET Latin NOT CaseSpecific Compress ('20','62','63','90','91','92','94','880','961','962','963','965','967','971','974','977','212','234','249'), Msisdn VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific, Traffic_Type_Id INTEGER Compress (1300 ,1070 ,1330 ,2104 ,1090 ,1111 ,100 ,1172 ,1700 ,1190 ,1191 ,1706 ,1200 ,1201 ,1721 ,1215 ,2243 ,1230 ,2255 ,1234 ,1238 ,1751 ,2010 ,2011 ,2269 ,1250 ,1252 ,1253 ,1254 ,1255 ,2279 ,2280 ,1005 ,500 ,2302 ), Package_Id INTEGER Compress (1325 ,1341 ,1346 ,1347 ,1604 ,1352 ,1353 ,1393 ,1144 ,1665 ,1186 ,1448 ,1197 ,1453 ,1218 ,1221 ,1485 ,1490 ,1262 ,1271 ), ACCOUNT_TYPE VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress '0', Customer_Category_Id CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('B','C'), Cug_Flag CHAR(1) CHARACTER SET Latin NOT CaseSpecific Compress ('N','Y'), Cell_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific, Location_Area_Id VARCHAR(20) CHARACTER SET Latin NOT CaseSpecific Compress ('00000','01330','01325','01326','01338','01324','02171','01331','01332','02175','03329','02176','02148','03328','02173','04026','01337','02155','02016','07027','03319','02311','07030','04061','02302','02170','04025','02174','01072','03318'), Call_Cost DECIMAL(18,4) Compress 0.0000 , FLAG_TOTAL_TYPE BYTEINT Compress (0 ,1 ), Actual_Call_Cost DECIMAL(18,4) Compress 0.0000 , Total_Duration DECIMAL(18,4) Compress 0.0000 , Total_Net DECIMAL(18,4) Compress 0.0000 , Total_Gross DECIMAL(18,4) Compress 0.0000 , Total_Free DECIMAL(18,4) Compress 0.0000 , Total_Calls DECIMAL(18,4) Compress (16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ), Total_discounted_Duration DECIMAL(18,4), Total_discounted_Gross DECIMAL(18,4), Total_Discounted_Free DECIMAL(18,4), Total_Discounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ), Total_Nodiscounted_Duration DECIMAL(18,4) Compress 0.0000 , Total_Nodiscounted_Net DECIMAL(18,4) Compress 0.0000 , Total_Nodiscounted_Gross DECIMAL(18,4) Compress 0.0000 , Total_Nodiscounted_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ), Total_Overlap_Duration DECIMAL(18,4) Compress 0.0000 , Total_Overlap_Net DECIMAL(18,4) Compress 0.0000 , Total_Overlap_Gross DECIMAL(18,4) Compress 0.0000 , Total_Overlap_Free DECIMAL(18,4) Compress 0.0000 , Total_Overlap_Calls DECIMAL(18,4) Compress (0.0000 ,16.0000 ,1.0000 ,17.0000 ,2.0000 ,18.0000 ,3.0000 ,19.0000 ,20.0000 ,4.0000 ,5.0000 ,6.0000 ,7.0000 ,8.0000 ,9.0000 ,10.0000 ,11.0000 ,12.0000 ,13.0000 ,14.0000 ,15.0000 ), Created_Date DATE FORMAT 'YYYY-MM-DD', DA300_COST DECIMAL(18,2) Compress 0.00 , Callindicator VARCHAR(6) CHARACTER SET Latin NOT CaseSpecific Compress ('3GC','PPS','2GC','POS','SLF','4GC','3GF'), CHARGED_VOLUME BIGINT, ZERO_RATED_VOLUME BIGINT, FREE_VOLUME BIGINT, CHARGED_NUMBER_OF_CALLS INTEGER, ZERO_RATED_NUMBER_OF_CALLS INTEGER, FREE_NUMBER_OF_CALLS INTEGER, HYBRID_NUMBER_OF_CALLS INTEGER, ACCT_NUM VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific DEFAULT NULL Compress ('UGW','','SDP','5444','5666','5600','EXT','5400','140','5699','5411','5655','5466','5422','5688','5677','5433','5455','5477','5633','5611','5644','AIR','5622','5499','5555','5404','5656','5440','5405','5414','5445','5488','5665','5660','5454','5448','2008','5410','5447','5446','5441','5443','5415','5406','5442','5696','5667','5434','5668','5606','5669','5662','5664','5407','5650','5661','5409','5678','5480','5408','5402','5690','5464','5663','5412','5695','5680','5430','5651','5449','5421','5403','2069','5420','5670','5450','5605','5490','5416','5413','5698','5691','5686','5616','5640','5694','5456','5630','5697','5692','5423','5646','5693','5432','5636','5620','5602','5610','5676','5424','5609','5607','5417','5470','150','5612','5608','5000','5056','5451','5626','5419','5453','5641','5435','5460','5418','5058','5425','5484','5654','5469','5685','5553','5615','5659','5431','5055','5671','5658','5657','5053','5426','5437','5452','5645','5556','5494','5481','5436','5681','5059','5457','5679','5631','5684','5632','5689','5613','5672','5675','5474','5428','5635','5639','5653','5687','5623','5604','5438','5427','5614','5652','5617','5634','5458','5618','5673','5483','5459','5629','5468','5619','5429','5683','5682','5621','5559','5642','5482','5465','5648','5674','5649','5492','5485','5462','5439','5647','5461','5637','5487','5558','5551','5628','5625','5049','5467','5552','5999','5624','5638','5478','5643','5603','5498','5051','5486','5493','5627','119','5463','5557','5033','5471','5052','5043','5479','5489','5046','5048','5544','5475','5472','5496','5495','5044','5054','5497','5554','5550','5566','5045','5057','5039','5533','5473','5038','5476','5900','5066','5036','5001','5005','5333','5042','5577','5041'), PPC_ACCT_NO VARCHAR(100) CHARACTER SET Latin NOT CaseSpecific) PRIMARY INDEX ( Msisdn ) PARTITION BY Range_N(Month_Id BETWEEN 200001 AND 210012 EACH 1 , NO RANGE OR UNKNOWN) INDEX ( Package_Id );
Junior Contributor

Re: Spool and performance

Use the correct datatypes, this one is easy to fix

USG.Service_Type_Id NOT IN ('51','61','21')  -- string, not numeric

but the join based on

USG.TOTAL_TYPE_ID = TT.TOTAL_TYPE

is quite bad. You should fix the data model, it's either a string or numeric, but not both.

 

Those type cast lead to no confidence because stats are lost.

Teradata Employee

Re: Spool and performance

Sorry i missed it this time. performed cast for join and added quotes for condition. Explain still shows no confidence and Merge time more then 4 hours.