To optimize Teradata SQL performance, it is crucial to identify the root cause of any issues. The SQL statement itself is typically not the culprit but rather one or more stages of the execution plan.
This article does not cover genuine optimization techniques. Instead, it presents a pragmatic method for query optimization. I have noticed that people often rely on a trial and error approach, which may result in a successful outcome through sheer luck, but without a clear understanding of the solution.
We must adopt a detective-like approach to the issue, identifying the incorrect steps in the execution plan and determining their root causes. Isolated inspection is the appropriate method in this case.
SQL performance issues can be attributed to two primary factors: uneven distribution of rows, also known as skew or an inefficient workload. Typically, these problems stem from missing or outdated statistics.
The checklist:
- Take a snapshot of your SQL statement’s EXPLAIN plan, showing any changes in the execution plan caused by your optimization activities.
- Check the completeness and up-to-date of your statistics. Do this even before taking a closer look at the execution plan. Add missing statistics and update the old ones. Watch out for steps with low or no confidence, but don’t assume you can consistently achieve high confidence levels. Certain conditions prevent high confidence (OR conditions, for example).
- Suppose statistics must be refreshed or completed, run another explanation, and examine if the execution plan changed. If the optimizer has a new execution plan, rerun your query. The new execution plan could have solved your performance issue.
- If your performance problem did not dissolve, it is time to look at each step of the execution plan. While you could start with static analysis on the EXPLAIN output, I prefer to start with real-time monitoring as it is usually the less time-consuming approach. Any real-time monitoring tool (like Viewpoint, PMON, DBMON) gives you the essential information needed.
- Execute your SQL statement and watch each step in real time.
- Wasteful workload: Look for discrepancies between estimated rows and rows returned in each step. This will mislead the optimizer, causing it to choose the wrong data preparation (copy, rehash, etc.) and join strategies (product join instead of merge join). If this is the case and all your statistics are fine, you need to consider possible solutions like temporary tables, advanced indexing, partitioning, query rewriting, etc.
- Skewed data: If you detect a step in the execution plan, which is skewed, your base tables have a well-distributing primary index, and the statistics are excellent. A skewed intermediate spool probably causes the problem. Skewed spool happens for several reasons, as two joined tables moved to a common ROWHASH with only a few distinct values. There is no secret to solving such a problem. Suppose the goal is to avoid the skewed spool. In that case, you should consider all optimization techniques that can help you, like breaking up queries into smaller parts and splitting the query into two parts, one handling the skewed values and the other handling the un-skewed values.
- Repeat the above approach until no wrong steps are left. Don’t forget to set statistics after each impacting change on the SQL statement.
To summarize, optimization consists of the following steps:
Detect the wrong steps in the execution plan, and figure out what is done wrong by the optimizer (bad join, etc.) and why it is wrong.
When you know the reason, you can browse your optimization toolkit for the appropriate tool and use your creativity: Many roads lead to Rome.
