Suspect Query Formulas

Teradata Viewpoint User Guide

brand
Analytical Ecosystem
prodname
Teradata Viewpoint
vrm_release
16.00
category
User Guide
featnum
B035-2206-106K

Formulas and Default Settings

Queries are flagged as suspect based on the suspect query formulas listed below. The tolerance value controls how many queries are flagged as suspect.

When the tolerance is zero, any queries that consume 1 AMP CPU second or greater and exceed the thresholds will be flagged as suspect. It is normal for queries that consume smaller amounts of resources to exceed these thresholds and, increasingly so, on larger systems. The tolerance value prevents these smaller consuming queries from being flagged as suspect if they are not too far above the threshold in proportion to the resources they consume. The larger the Teradata system, the larger the tolerance values need to be to ensure that queries that are performing normally are not identified as suspect.

Metric Formula Suspect Query Formula Default Threshold Default Tolerance
CPUSkew 100 * (1 - (AMPCpuTime/(MaxAMPCPUTime * NumOfActiveAMPs))) CPUSkew > 100 - ((100 - Threshold) * AMPCPUTime)/(Tolerance + AMPCPUTime) 40 10
IOSkew 100 * (1 - (TotalIOCount/(MaxAmpIO * NumOfActiveAMPs))) IOSkew > 100 - ((100 - Threshold) * TotalIOCount)/(Tolerance * 20,000 + TotalIOCount) 40 10
PJI (AMPCPUTime * 1000)/TotalIOCount PJI > (10 * Tolerance)/(AMPCPUTime - Tolerance + 1) + Threshold) AND AMPCpuTime >= Tolerance 3 10
UII TotalIOCount/(AMPCPUTime * 1000) UII > (200,000 * Tolerance)/(TotalIOCount - (Tolerance * 1000) + 1) + Threshold) AND TotalIOCount >= Tolerance * 1000 20 10

Approaches to Obtaining an Optimal Tolerance Value

There are two different approaches to obtaining the optimal tolerance value.

With the first approach, you can use a visualization tool to scatter plot queries by CPU Skew or PJI versus AMPCPUTime, or IO Skew or UII versus TotalIOCount for a single day's worth of DBQL data. Then graph the corresponding suspect query formula on top of the scatter plot. Queries above the suspect query line will be flagged as suspect. Adjust the tolerance in the suspect query formula to shape the line so the visual outliers are above the line, but the majority of queries are below the line.

With the second approach, you can execute a SQL query that counts the number of rows that exceed the value of the suspect queries formulas for a day's worth of DBQL data and adjust the tolerance values so that an actionable number of queries are returned.