Identifying Poorly Performing Queries - Teradata Database

Teradata Database Administration

Teradata Database
Release Number
English (United States)
Last Update
Product Category

When notified of delays by a user or an alert, you can use the Teradata Viewpoint Query Monitor portlet to find out if the delay is the result of a poorly performing query.

Note: The Query Monitor portlet has many functions beyond what is shown in this example procedure. See Teradata Viewpoint User Guide for detailed setup and options.

1 Monitor query activity using the By Session tab.

2 Look for poorly performing queries using the following Query Monitor parameters:

  • High CPU Use% combined with low REQ I/O accesses. If the CPU divided by the I/0 is > 100, the query is probably bad.
  • High CPU Skew% with significant Delta CPU.
  • High spool usage.
  • Note: You can click on and sort the display by a parameter instead of by session number.

    3 Select a session row that shows CPU characteristics indicating a bad query to display the Details View, which provides information about the query, including the user and account that submitted the query.

    4 Select the SQL tab to display the SQL for the query. Examine the SQL for errors and poor construction.

    5 Select the Explain tab to display an abbreviated version of the step statistics and explain text (generated from a full EXPLAIN request), along with a listing of the active steps, completed steps, and steps yet to run.

    6 Check for the following in the Explain:

  • The steps required to execute the query
  • The type of operation being performed, and whether it includes a product join
  • Often the query is stuck on the step where the product join occurs.

    7 You can copy the query from the SQL tab, run it from Teradata SQL Assistant or Teradata Studio, and request a full EXPLAIN to get more details and validate the source of the problem.