Databases
StackOverflow2013
Postsdbo
POTeradata Performance issue and example
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POTeradata Performance issue and example
    primarykey
    data
    text
    <p>I am facing an issue in our Teradata QA environment where a simple query that ran in under 1 minute is now taking 12 minutes to complete. This select is pulling 5 fields based on a simple inner join</p> <pre><code>select a.material , b.season , b.theme , b.collection from SalesOrders_view.Allocation_Deliveries_cur a inner join SalesOrders_view.Material_Attributes_cur b on a.material = b.material; </code></pre> <p>I can run this same query in our Prod environment and it returns in less than a minute while running on approx 200k more records than QA.</p> <p>Total volume is under 1.1 M records in SalesOrders.Allocation_Deliveries and 129 k records in SalesOrders.Material_Attributes. These are small datasets.</p> <p>I compared the Explain plans on both environments and there is a stark difference in the estimated spool volume in the first Join step. The estimate in Production is on the money while the Estimate in QA is an order of magnitude off. However the data and table/views are identical in both systems and we have collected stats in every conceivable manner and we can see the particular table demographics in both systems as identical.</p> <p>Lastly, this query has always returned in under a minute in all environments including QA as it is still doing in Production. This latent behavior is recent in the last week or so. I discussed this with our DBA and we have had no changes to software or configuration. He is new, but seems to know what he's doing but still getting caught up with a new environment.</p> <p>I am looking for some pointers on what to check next. I have compared the relavant table / view definitions across QA and Prod and they are identical. The Table demographics in each system are also the same (I went through these with our DBA to make sure)</p> <p>Any help is appreciated. Thanks in advance. Pat</p> <p>This is the Explain plan from QA. Note the very Low estimate in Step 5 (144 Rows). In Prod, the same Explain shows > 1 M rows which would be close to what I know.</p> <pre><code>Explain select a.material , b.season , b.theme , b.collection from SalesOrders_view.Allocation_Deliveries a inner join SalesOrders_view.Material_Attributes_cur b on a.material = b.material; 1) First, we lock SalesOrders.Allocation_Deliveries in view SalesOrders_view.Allocation_Deliveries for access, and we lock SalesOrders.Material_Attributes in view SalesOrders_view.Material_Attributes_cur for access. 2) Next, we do an all-AMPs SUM step to aggregate from SalesOrders.Material_Attributes in view SalesOrders_view.Material_Attributes_cur by way of an all-rows scan with no residual conditions , grouping by field1 ( SalesOrders.Material_Attributes.material ,SalesOrders.Material_Attributes.season ,SalesOrders.Material_Attributes.theme ,SalesOrders.Material_Attributes.theme ,SalesOrders.Material_Attributes.af_grdval ,SalesOrders.Material_Attributes.af_stcat ,SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM). Aggregate Intermediate Results are computed locally, then placed in Spool 4. The size of Spool 4 is estimated with high confidence to be 129,144 rows (41,713,512 bytes). The estimated time for this step is 0.06 seconds. 3) We execute the following steps in parallel. 1) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of an all-rows scan into Spool 2 (all_amps), which is redistributed by the hash code of ( SalesOrders.Material_Attributes.Field_9, SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM, SalesOrders.Material_Attributes.Field_7, SalesOrders.Material_Attributes.Field_6, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.season, SalesOrders.Material_Attributes.material) to all AMPs. Then we do a SORT to order Spool 2 by row hash and the sort key in spool field1 eliminating duplicate rows. The size of Spool 2 is estimated with low confidence to be 129,144 rows (23,504,208 bytes). The estimated time for this step is 0.11 seconds. 2) We do an all-AMPs RETRIEVE step from SalesOrders.Material_Attributes in view SalesOrders_view.Material_Attributes_cur by way of an all-rows scan with no residual conditions locking for access into Spool 6 (all_amps), which is redistributed by the hash code of ( SalesOrders.Material_Attributes.material, SalesOrders.Material_Attributes.season, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM, SalesOrders.Material_Attributes.Material_Attributes_UPD_TS, (CASE WHEN (NOT (SalesOrders.Material_Attributes.af_stcat IS NULL )) THEN (SalesOrders.Material_Attributes.af_stcat) ELSE ('') END )(VARCHAR(16), CHARACTER SET UNICODE, NOT CASESPECIFIC), (CASE WHEN (NOT (SalesOrders.Material_Attributes.af_grdval IS NULL )) THEN (SalesOrders.Material_Attributes.af_grdval) ELSE ('') END )(VARCHAR(8), CHARACTER SET UNICODE, NOT CASESPECIFIC)) to all AMPs. Then we do a SORT to order Spool 6 by row hash. The size of Spool 6 is estimated with high confidence to be 129,144 rows ( 13,430,976 bytes). The estimated time for this step is 0.08 seconds. 4) We do an all-AMPs RETRIEVE step from Spool 2 (Last Use) by way of an all-rows scan into Spool 7 (all_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 7 by the hash code of (SalesOrders.Material_Attributes.material, SalesOrders.Material_Attributes.season, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.theme, SalesOrders.Material_Attributes.Field_6, SalesOrders.Material_Attributes.Field_7, SalesOrders.Material_Attributes.Material_Attributes_SRC_SYS_NM, SalesOrders.Material_Attributes.Field_9). The size of Spool 7 is estimated with low confidence to be 129,144 rows (13,301,832 bytes). The estimated time for this step is 0.05 seconds. 5) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an all-rows scan, which is joined to Spool 7 (Last Use) by way of an all-rows scan. Spool 6 and Spool 7 are joined using an inclusion merge join, with a join condition of ("(material = material) AND ((season = season) AND ((theme = theme) AND ((theme = theme) AND (((( CASE WHEN (NOT (af_grdval IS NULL )) THEN (af_grdval) ELSE ('') END ))= Field_6) AND (((( CASE WHEN (NOT (AF_STCAT IS NULL )) THEN (AF_STCAT) ELSE ('') END ))= Field_7) AND ((Material_Attributes_SRC_SYS_NM = Material_Attributes_SRC_SYS_NM) AND (Material_Attributes_UPD_TS = Field_9 )))))))"). The result goes into Spool 8 (all_amps), which is duplicated on all AMPs. The size of Spool 8 is estimated with low confidence to be 144 rows (5,616 bytes). The estimated time for this step is 0.04 seconds. 6) We do an all-AMPs JOIN step from Spool 8 (Last Use) by way of an all-rows scan, which is joined to SalesOrders.Allocation_Deliveries in view SalesOrders_view.Allocation_Deliveries by way of an all-rows scan with no residual conditions. Spool 8 and SalesOrders.Allocation_Deliveries are joined using a single partition hash_ join, with a join condition of ("SalesOrders.Allocation_Deliveries.material = material"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 3,858 rows (146,604 bytes). The estimated time for this step is 0.44 seconds. 7) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -&gt; The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.70 seconds. </code></pre> <p>Here is what the record distribution looks like and the SQL I used to generate the result set</p> <pre><code>SELECT HASHAMP(HASHBUCKET(HASHROW( MATERIAL ))) AS "AMP#",COUNT(*) FROM EDW_LND_SAP_VIEW.EMDMMU01_CUR GROUP BY 1 ORDER BY 2 DESC; </code></pre> <p>Output Highest: AMP 137 with 1093 rows Lowest: AMP 72 with 768 rows Total AMPs: 144</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USapesa
    1. USapesa
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POTeradata Performance issue and example
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POTeradata Performance issue and example
      1. USBellevueBob
      1. VTFavorite
    1. COIf the EXPLAIN plan is different then *something* between the QA and Production environment is different. Can share the EXPLAIN plans, view definitions and table definitions (I don't care if you rename the columns)? What does the data distribution look like for the columns participating in the `INNER JOIN`? Keep in mind these columns have to be on the same AMP to join. If there is a redistribution and a skew in the data you have found your problem.
      singulars
      1. POTeradata Performance issue and example
      1. USRob Paller
    2. CORob, I added the Explain Plan from QA. Had to change the names but they match
      singulars
      1. POTeradata Performance issue and example
      1. USapesa
    3. COAlso, I used WinMerge to Diff the 2 Explain Plans and the only differences are in the row counts and size estimates. They are all accurate when comparing them to actual table recs or what is expected in the Join. The only way off value is in Step 5 (In QA only) where it is showing 144 rows. That should be upwards of 1 M rows as it is in Prod.
      singulars
      1. POTeradata Performance issue and example
      1. USapesa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload