Formulas and Default Settings
Queries are flagged as suspect according to the suspect query formulas listed here. 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 are 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 far from the threshold in proportion to the resources they consume. The larger the Teradata system, the larger the tolerance values need to be to make sure 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 before the suspect query line are flagged as suspect. Adjust the tolerance in the suspect query formula to shape the line so the visual outliers are before the line, but the majority of queries are after the line.
With the second approach, you can run a SQL query that counts the number of rows that exceed the value of the suspect queries formulas for a days worth of DBQL data and adjust the tolerance values so that an actionable number of queries are returned.