This website offers numerous articles on SQL Tuning, comprehensively analyzing the tools for optimizing queries. The Teradata architecture offers many possibilities for SQL tuning, making Teradata Vantage and, in general, all Teradata databases highly tunable. It is worth noting that Teradata University provides a wealth of great tuning information but is, unfortunately, exclusively available for customers of the Teradata corporation. This article aims to demonstrate the optimal approach for query optimization, although other methods exist. Tuning is a combination of practical experience and chance.
The tuning process typically commences when business users or DBAs notify us of sluggish queries. As we have previously stated, query runtime is a valid criticism. However, as performance tuners, we aim to decrease the resources utilized by slow queries and reduce skew. Resource consumption mainly pertains to disk IOs; while the number of required CPU seconds is also essential, it is of greater importance when the system is CPU bound. As we know, data can only be processed by a computer when available in primary memory. The transfer of data blocks into primary memory is the bottleneck. Therefore, we should primarily focus on IOs and skew.
We must first determine whether we are dealing with a tactical or strategic workload to optimize in the right direction. The nature of the workload is crucial. Strategic workloads handle vast amounts of data, allowing longer processing times and tolerating fluctuations in runtime caused by competing workloads. The primary objective of strategic workloads is to lower the overall average runtime. In contrast, tactical workloads have different expectations from business users, who require short and stable runtimes.
Optimizing the tactical workload presents a greater challenge as we must minimize runtime while ensuring stability. Thus, this article will delve deeper into this topic.
Tactical queries are sensitive to concurrent workloads. To gain insight into their behavior under various circumstances, running them on a schedule for an extended period, including all exceptional cases, is advisable. This scheduled run will provide an initial overview of query performance under load.
In my experience, a query that executes quickly on an unburdened system can take up to an hour on a heavily taxed system. In this scenario, it is important to examine workload management closely. TASM should not hold up tactical queries. Additionally, it is crucial to avoid queries being obstructed by other processes utilizing necessary database objects. The implementation of LOCKING FOR ACCESS can be highly beneficial in this regard.
Once disruptions such as the above have been addressed, optimization can commence. To attain stable runtimes, we must consider eliminating full table scans in the execution plan. Direct data access at every step is crucial. Optimal accesses should occur via the primary index and single- or group-AMP joins. Secondary and join indexes should prioritize direct data access.
However, achieving this task is not as simple as it may seem. The challenge lies in the fact that alterations to data demographics or the removal of statistics can potentially annul our optimizations by altering the execution plan. Frequently, the statistics landscape remains beyond our influence.
Attaining Primary Index or USI access is crucial due to its optimal efficiency. The Optimizer favors this approach, even without available statistics.
Regardless of the workload, we consistently begin collecting and updating statistics when optimizing certain queries. We aim to prevent optimizing an execution plan that could potentially alter when statistics are not present.
After completing the initial stage, we shall track the query in real-time using the Viewpoint tool. We will focus on identifying any steps that are stagnant or have a significant skew factor. Nevertheless, it is worth noting that skew optimization is frequently no longer a concern once statistics have been collected.
To better understand the various causes of skew and potential remedies in the context of the architecture of Teradata, I suggest reviewing the following article:
It’s time to analyze the Execution Plan and identify any All-AMP full table scans, or All-AMP joins. We must address these issues to ensure consistent run times.
Methodical testing of the Execution Plan is applicable regardless of the workload. It is imperative to examine potential issues meticulously. There are no effortless solutions or abbreviated procedures.
Special attention should be paid to joins following a step with a higher estimated number of result rows than actual rows, as selecting the wrong join method can negatively impact runtime. Misestimation can frequently occur with product joins, and hash joins may be problematic if the AMPs cannot retain the hash table in memory. However, suppose the actual number of rows is lower than the estimated amount. In that case, this is generally not a concern as the Optimizer would have selected a more cautious join method that can handle fewer rows.
Search for Teradata Tuning Opportunities for Tactical Workload
After discussing the overall factors, let’s examine the tuning choices for the tactical workload. This checklist will serve as a valuable tool for your daily tuning tasks.
Have both tables joined the same PI and Partitioning, and is it matching the join columns?
Aligning PI and Partitioning to the join columns ensures that AMP-local joining via merge join or rowkey-based merge join (partitioned tables) is possible. Such a setup is beneficial not only for tactical but also for strategic workloads.
Did we place a USI on the foreign table of relation to support the usage of nested joins?
Suppose the AMP can query the main table using UPI or USI, and we provide a USI, UPI, or NUPI on the other table, which matches the join columns. In that case, the system can read the row in the main table using UPI or USI (single or dual AMP access). Then the hash value is calculated from the join column(s), and the AMP containing the corresponding row is instructed to return the searched row(s). So we have a join using only 3 or 4 AMPs, depending on which indexes we created.
Should we create single join indexes with a PI different from the base table?
A single table join index with different PI can be helpful in single-AMP queries if the Optimizer can use the new PI in retrieving steps or joins. The join index can also be created as a global join index by not including all required columns in the index but only the ROWIDs of the base table. If necessary, the join index and the ROWIDs can be used to access the base table to retrieve the required columns.
The advantage of a single table join index over a NUSI is that duplicates are allowed, and the rows can still be accessed using ROWHASH. A single table join index has further advantages: ROWHASH locking and Group-AMP operations are possible. The NUSI, on the other hand, always requires table locks and almost always all-AMP access (single-AMP access when the NUSI matches the primary index columns of its base table).
Are there statistics on all tables on the WHERE conditions and the join columns?
Statistics are always vital but especially so here. So the Optimizer will be able to use a join index even if these statistics are missing but may not perform group-AMP access but all-AMP access.
Do all row-partitioned tables contain all partition columns in the primary index?
If the PI can contain all partition columns, no partition probing is necessary to determine which partitions the PI value is available. The design then ensures that the value can only be in one partition. So make sure that all partition columns are always part of the PI. If possible, define a unique primary index.
If putting all partition columns into the PI is not possible, then you should at least define a WHERE condition on the partition columns to prevent probing.
Other strategies to avoid probing:
If the Primary Index of the partitioned table is a NUPI with unique values, we can put a USI matching the PI columns.
If the Primary Index of the partitioned table’s values is not unique, we can put a global join index matching the PI columns.
With both options, we can directly access the rows from the index as both types of indexes point straight to the physical rows, and the AMP doesn’t need to probe partitions to look up the row from the base table.
If we can’t use any of these strategies, there is still the option to keep the number of partitions small. The fewer partitions, the fewer have to be checked by probing.
Can a sparse join index be created that restricts to a specific partition?
It is often the case that we have partitioned base tables by date. Therefore, we could restrict our sparse join index to include only the current date partition (WHERE RefDate = DATE’2022-06-30′).
Another trick with the sparse join index is choosing the PI to include only a few values (fewer than AMPs). This way, the Optimizer can limit the query to a few AMPs (group AMP).
Did we optimize the All-AMP Steps?
We cannot consistently achieve an execution plan that is complete without All-AMP. We should optimize all remaining All-AMP steps. Here are some ideas:
– Remove all unnecessary steps (e.g., joins that are not needed).
– NUSI accesses and row partitioning can prevent full table scans.
– Use join indexes with few columns as it reduces the IOs.
– Split the query into more minor queries.
– LOCKING FOR ACCESS ensures that others do not block us.
Should we apply multivalue compression?
If our system is not CPU bound, it may be helpful to apply multivalue compression. Especially if the AMP can keep small tables in the main memory, this can give a performance advantage because fewer IOs are needed. Multivalue compression can be an accompanying measure but is not one of our primary tools.
Multivalue compression makes sense for wide columns and columns with few distinct values.
Can small tables be swapped out to sparse maps?
Sparse maps are a relatively new feature to keep small tables entirely on an AMP. This can have tremendous advantages for tactical workloads. First, single AMP accesses are possible even if the entire table is read. Second, it can make query times more stable compared to all-AMP access since individual AMPs that are overloaded will no longer become a bottleneck if our small table is not on those AMPs. Finally, our workload can be prioritized as a tactical workload by TASM.
Read further specifics regarding sparse maps for tactical workload optimization here:
SQL Tuning Summary
Here’s an example of a tactical workload with several ideas for optimizing your query.
To optimize a tactical query, follow these steps:
Analyze the EXPLAIN statement output in detail, identifying the type of workload executed, such as join or retrieve steps, and the number of AMPs involved. If an All-AMP step is identified, review the checklist and carefully consider which tips can transform it into a Group-AMP or Single-AMP step. Enjoy the process.
Find additional information here: