Identifying Poorly Performing Queries - Teradata Database

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.