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.
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.
- Monitor query activity using the By Session tab.
- 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.You can click on and sort the display by a parameter instead of by session number.
- 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.
- Select the SQL tab to display the query. Examine the SQL for errors and poor construction.
- 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.
- 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.
- You can copy the query from the SQL tab, run it from Teradata Studio, and request a full EXPLAIN to get more details and validate the source of the problem.
- 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 and Skewed Query Processing Across AMPs.
- The data table may have skewed distribution. See Finding and Fixing Skewed Tables.
- The query may be blocked from accessing the data. See the sections beginning with Identifying and Managing Blocked Queries.
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.
- SET SESSION ACCOUNT, see SQL Data Definition Language Syntax and Examples.