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.
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 poorly performing queries using the following Query Monitor parameters:
You can click on and sort the display by a parameter instead of by session number.
- 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.
- High CPU Use% combined with low REQ I/O accesses.
- 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 SQL for 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.