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.