Which metrics should be examined for high values to identify the queries?

An application is consuming very high I/O on Teradata Vantage. The Administrator has been asked to identify the queries that are consuming more than 100 million IOs and are suspected of completing Full Table Scans or Large Redistribution.

Which metrics should be examined for high values to identify the queries?
A . MAXAMPIO and PJI
B . TOTALIOCOUNT and PJI
C . I/O SKEW % and UII
D . REQPHYSIO and UII

View Answer

Answer: B

Explanation:

TOTALIOCOUNT represents the total number of I/O operations a query performs, which is crucial when trying to identify queries consuming excessive I/O (in this case, more than 100 million IOs). This directly indicates the amount of I/O a query is generating.

PJI (Product Join Index) measures whether a product join is being performed in the query, which is often associated with inefficient joins that result in large redistributions of data. A high PJI value

indicates that the query might be performing a product join, leading to a significant performance hit due to large redistributions.

Option A (MAXAMPIO and PJI): MAXAMPIO refers to the maximum I/O performed by a single AMP, but TOTALIOCOUNT is a more direct indicator of the total I/O performed by a query across all AMPs. Option C (I/O SKEW % and UII): I/O SKEW % indicates uneven distribution of I/O, which can cause performance issues, but it does not directly measure the total I/O consumption like TOTALIOCOUNT does. UII (Utility Impact Index) is relevant but not as important for identifying large I/O queries. Option D (REQPHYSIO and UII): REQPHYSIO refers to the number of physical I/Os, but it is not as comprehensive as TOTALIOCOUNT, which includes both logical and physical I/O.

Thus, TOTALIOCOUNT (for total I/O) and PJI (to detect potential large redistributions due to product joins) are the key metrics to identify queries with high I/O and large redistributions.