Teradata SQL Tuning Evergreens

 

  1. Ensure Completeness and Correctness of Teradata Statistics

This is what I always teach in my performance Teradata SQL tuning workshops without ever disappointing my clients:

The most important optimization task is to give complete and correct statistics about tables and indexes (secondary indexes, join indexes, hash indexes, etc.).

Detecting missing statistics is easy. In the SQL Assistant executes the following statement:

DIAGNOSTIC HELPSTATS ON FOR SESSION;

Above statement turns on the optimizer’s statistic hints, and adds information about required statistics to the explain output.

A list of missing statistics is displayed at the end of the output. These are the statistics that the optimizer could use when creating the execution plan. Add the statistics one after the other and check the execution plan again after each change.

There are several ways to find outdated statistics. The easiest way is to split the SQL statement and to test each part separately, by comparing the estimated row count (as shown in the explain output) with the real number of records being returned by the query.

This approach is particularly suitable if the SQL statement cannot be executed in a reasonable time. Here is one example:

SELECT t01.* FROM table_1 t01 INNER JOIN  table_2 t02 ON t01.PK = t02.PK
WHERE  t01.column_1 = 1 AND t02.column_a = 2;

Above query can be split into two parts for testing:

SELECT * FROM table_1 WHERE column_1 = 1;

and

SELECT * FROM table_2 WHERE column_a = 2;

Execute and EXPLAIN both queries, and compare the number of rows returned with the number of rows returned by the EXPLAIN output.

If there is a big difference, it could be obsolete statistics or similar data distribution problems.

  1. The Primary Index (PI) Choice

Whenever possible, design your queries in a way, that the Primary Index can be used in the joins. Being able to join on the complete set of Primary Index columns is the cheapest way of joining (apart from the nested join). The join condition can include other columns, which are not part of the Primary Index (they are applied as residual conditions), but missing Primary Index columns forbid the usage of a row hash match scan.

If you cannot change the primary indexes for any reason, or if you only need another primary index for certain SQL statements, use a volatile table or a real temporary table with the same structure and content as the original table, but with a different primary index.

  1. Teradata Indexing Techniques

Using indexes is another way to improve query performance. Secondary indices are useful in highly selective WHERE conditions.

Although usually not considered indexing, you could try join indexes or work with partitioning. The difference between an index and partitioning is that indexes are sub-tables, partitioning is just another way of structuring the base table.

Whenever you work with indexing techniques, you need to keep an eye on the entire data warehouse architecture and decide whether your solution fits into it.

If potentially useful indexes are not used by the Optimizer and they are not helpful in the entire PDM design, drop them immediately. You’re wasting space and resources.

More details about indexing you can find here

  1. Query Rewriting

The performance of a query can often be improved by rewriting the query.

Examples of using DISTINCT instead of GROUP BY on columns with many values come to mind.
Union statements could be used to split a large SQL statement into several smaller parts that are executed in parallel.

Rewriting queries often solve performance problems, even in cases where all other techniques have failed.

While query rewriting is a very powerful way of improving performance, it often requires understanding the business logic (“can this left join be replaced by an inner join?”). Usually, understanding the business logic of a query reveals other tuning options.

  1. Real-Time Monitoring

Watch the query in real time. Observing a query in Viewpoint or PM during execution helps to recognize the critical steps.

Most performance problems are caused by query steps that cause skew, or by a poor execution plan caused by obsolete and missing statistics.

Stale and missing statistics typically lead to wrong decisions in join preparation (copying a table to all AMP’s instead of rehashing) and join types used (product join instead of merge join).

  1. Comparison of Resource Usage

Another important task in performance optimization is the measurement of resource consumption before and after optimization. Query runtimes are misleading because they can be affected by simultaneous system load, blocking, and so on.

Here is an example of a query that gives you a detailed insight into how good each step of your query is. To execute this query, you need a select access to table DBC. DBQLOGTBL.

Set a different QUERYBAND for each version of the query you execute:

SET QUERY_BAND = ‘Version=1;’ FOR SESSION;

SELECT

   AMPCPUTIME,

   (FIRSTRESPTIME-STARTTIME DAY(2) TO SECOND(6)) RUNTIME,

   SPOOLUSAGE/1024**3 AS SPOOL_IN_GB,

   CAST(100-((AMPCPUTIME/(HASHAMP()+1))*100/NULLIFZERO(MAXAMPCPUTIME)) AS INTEGER) AS CPU_SKEW,

   MAXAMPCPUTIME*(HASHAMP()+1) AS CPU_IMPACT,

   AMPCPUTIME*1000/NULLIFZERO(TOTALIOCOUNT) AS LHR

FROM

   DBC.DBQLOGTBL

WHERE

     QUERYBAND = ‘Version=1;’

The query will return:

  • The total CPU Usage
  • The Spool Space needed
  • The LHR (ratio between CPU and IO usage)
  • The CPU Skew
  • The Skew Impact on the CPU

Our goal is to cut total CPU usage, consumed spool space and skewing on the CPU. Furthermore, the LHR is optimally around 1.00

Teradata Quiz
Teradata Quiz
Price: Free
Our Reader Score
[Total: 47    Average: 4/5]
6 Golden Rules for Teradata SQL Tuning written by Roland Wenzlofsky on October 29, 2014 average rating 4/5 - 47 user ratings

9 COMMENTS

  1. Hi Roland,
    Is it fine to collect stats on all columns recommended by DIAGNOSTIC command ?
    Is there any specific way to know as to which stats recommendations should be implemented and which ones to skip ?
    I generally collect stats on single column stats recommendations and skip the multi-column stats. But there have been instances where multi-column stats recommendations helped after implementation.

    Regards,
    Dhruba

    • Not all recommended statistics are needed. I follow a similar approach. Especially when it comes to multi-column statistics we have to consider that their application is limited compared to single column stats. While the optimizer can use the first part of a statistic on columns (a,b) i.e. the “a”, it will not be able to use a query which, for example, has a WHERE condition only on column “b”. Of course, the ideal scenario for the statistic on columns(a,b) is a WHERE condition ob both columns.

      It’s similar to statistics on functions, such as on CASE statements. They are only useful for a very specific kind of query which matches exactly this function.

  2. INSERT INTO eitopsdb.GTT_srv_ft_data_grp_nbr_rk

    SELECT BASE.srv_accs_id
    ,BSS.data_grp_nbr
    FROM Eitopsdb.srvft_blstmt_usgvol_fltavg_rk BASE INNER JOIN
    EDWVIEWS.bl_stmnt_sub BSS
    ON
    BASE.srv_accs_id = BSS.srv_accs_id
    — WHERE bl_cyc_dt BETWEEN DATE -31 — insure one month ago
    — AND DATE -1 — Yesterday
    QUALIFY 1 = ROW_NUMBER() OVER
    (PARTITION BY BSS.srv_accs_id
    ORDER BY BSS.bl_cyc_dt DESC, BSS.data_grp_nbr DESC)

    Could anybody help in tuning this query??

    It is failing with below error-

    *** Failure 3156 Request aborted by TDWM. Exception criteria exceeded: Spool Usage.
    Statement# 1, Info =0

    • Hi – The duplicate filtering should be taken care before participating in to join operation . Share the explain plan of this Query for more detail.

  3. This is a question which can’t be answered easily. New releases of Teradata has some helpful features like statistic extrapolation and comparision of random AMP sampling against collected statistic. This may help in reducing recollection intervals.

    There are different approaches which could be taken. The refresh interval definitely depends on how fast data changes. Usually I sneak some code into central components like the historization process. You could for example use the ACTIVITYCODE variable in BTEQ to get the number of new rows added to a table. This may be helpful in estimating the table growth and this insight can be used for a timely recollection of statistics without any overhead.

    Statistics collection is always a tradeoff between keeping the system performing and the overhead caused by the collection process.

  4. Roland,

    Should we collect stats every time a table gets refreshed ( either inserts or updates or both) ? Lets says we have some 500 tables in DWH which gets refreshed every day, in this case, do we have to take stats for all 500 tables every day ?

  5. Yes, partitioning definitely should be part of the golden rules.

    Regarding statistics, I am always struggling against showing the DIAGNOSTIC HELPSTATS statement. On the one hand it is a fast and comfortable way to analyze the statistics landscape, on the other hand I have seen many people starting to blindly add all recommendations after I showed them this possibility.

  6. From a pedagogical point of view, I would actually rank “statistics” at the end of this list.

    I consistently realize that statistics tend to be over-collected due to staff’s “low confidence” in pretty much all the other points you mention here. The magic of the DIAGNOSTIC often results in shifting the attention away from more fundamental design problems, which statistics are just intended to complete.

    Great post!

LEAVE A REPLY

Please enter your comment!
Please enter your name here