Finding and Fixing Problem Queries - Teradata Database

You can use the Teradata Viewpoint Query Monitor portlet for regular monitoring of database queries and to investigate whether a reported delay is the result of a bad 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 bad 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 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.

8 If the query appears to be constructed correctly, other problems may be indicated.

System conditions may cause uneven processing. See “Skewed Query Processing Across Nodes” on page 493 and “Skewed Query Processing Across AMPs” on page 494.

The data table may have skewed distribution. See “Identifying and Fixing Skewed Tables” on page 495.

The query may be blocked from accessing the data. See the sections beginning with “Identifying and Managing Blocked Queries” on page 499.

If a bad query causes problems in the database, you can:

Use Query Monitor to abort the query. See Teradata Viewpoint User Guide.

Use the SET SESSION ACCOUNT request to reassign a poorly-performing query to a lower priority account. For information on:

Using accounts to set job priorities, see “Account Strings and Performance Groups” on page 511.

SET SESSION ACCOUNT, see SQL Data Definition Language Syntax and Examples.