16.10 - Recommendations for Common Query Problems - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

You can monitor queries using the Teradata Viewpoint Query Monitor portlet and other tools and utilities to identify poorly performing queries. Poor query performance is usually due to:

Query Problem Result
Stale statistics The optimizer uses statistics to plan query execution. If the statistics are out of date, the optimizer may create a poor plan.

Recommendation: Review tables to see whether statistics are out of date, and recollect statistics if required, as shown in Improving Query Performance Using COLLECT STATISTICS: Application DBAs.

Badly skewed data Causes table rows to be distributed unevenly among AMPs and queries to execute unevenly, which slows processing time.

Recommendation: Monitor table skew. Redefine the primary index if required. See Finding and Fixing Skewed Tables.

Poor query construction Poor SQL syntax or missed punctuation can result in bad queries.

Recommendations:

Concurrency issues Some queries are delayed or prevented from executing because of lock contentions, or the relative priorities of concurrent queries.

Recommendations:

  • Identify the cause of a delayed query. See Investigating Query Blocks and Delays.
  • Release a query from the throttle delay cue. See Controlling Poorly Performing Queries.
  • Order the release of requests from the throttle delay queue based on workload priority, instead of first in, first out. See the General tab in the Teradata Viewpoint Workload Designer portlet.
  • Change the priority for a database operation or user.
  • Establish more detailed concurrency controls using TASM.