QUESTION: 17

An Administrator has been presented with these performance metrics from the DBQLOGTBL table for four queries:

The Administrator needs to identify which query is suspected to be causing a full table scan or large redistribution.

Which query is producing this result?

QRY_D because of CPU SKEW
QRY_C because of UII
QRY_B because of IO SKEW
QRY_A because of PJI

Answer(s): C

Explanation:

IO SKEW refers to an uneven distribution of I/O operations across AMPs, which can occur during large redistributions or full table scans, as data needs to be read from or written to the entire table. High IO skew indicates that some AMPs are handling significantly more I/O than others, which is often a sign of inefficient data distribution or full table scanning. QRY_B has an IO SKEW value of 2.76, which is much higher than the other queries, suggesting that it is likely performing a full table scan or large redistribution, as this would result in uneven I/O across AMPs.

CPU SKEW (as seen in QRY_D) could indicate an issue with CPU load balancing, but it doesn't necessarily suggest a full table scan or redistribution. UII (Utility Impact Index) and PJI (Product Join Index) are also metrics that might indicate query inefficiencies, but the high IO SKEW in QRY_B is the most direct indicator of a full table scan or large redistribution.
QRY_B is the query suspected of causing the full table scan or large redistribution due to its high IO SKEW value.

Reveal Solution Next Question

QUESTION: 18

BATCH_USER submits stored procedures, utility jobs, and other requests. All BATCH_USER work classifies into workload WD-Batch based on username criterion. System performance has slowed due to AWT exhaustion during the batch window. The Administrator determined the AWT exhaustion is due to high concurrency of stored procedure calls from BATCH_USER.
How can the Administrator effectively throttle these stored procedures?

Create a utility limit, classify on username = BATCH_USER and utility = Stored Procedure, and set the limit.
Define an AWT resource limit, classify on username = BATCH_USER and utility = Stored Procedure, and set the limit.
Create a new workload, classify on username = BATCH_USER and statement type = CALL, and set a throttle.
Define a system throttle, classify on username = BATCH_USER and stored procedure = *.*, and set the limit.

Answer(s): C

Explanation:

In this scenario, the issue arises due to high concurrency of stored procedure calls, which are exhausting AWTs (AMP Worker Tasks). To address this, the administrator needs to throttle the specific stored procedure calls made by BATCH_USER. The most effective way to do this is to create a new workload that specifically targets those calls (statement type = CALL) from BATCH_USER and then applies a throttle to limit concurrency. This helps manage the system's AWTs by controlling how many stored procedures can be run concurrently.
Other options are less suitable as they either do not target stored procedures specifically or may not provide the necessary granularity in controlling AWT usage for stored procedure calls.

Reveal Solution Next Question

QUESTION: 19

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?

MAXAMPIO and PJI
TOTALIOCOUNT and PJI
I/O SKEW % and UII
REQPHYSIO and UII

Answer(s): 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.

Reveal Solution Next Question

QUESTION: 20

DRAG DROP (Drag and Drop is not supported)
An Administrator is configuring an alert service on a Vantage system and must define threshold levels and triggers for the alerts.
Match the Administrator's portlet to the correct use definition.
(Each answer is used once.)

See Explanation section for answer.

Answer(s): A

Explanation:

Reveal Solution Next Question

Free TDVAN5 Exam Braindumps (page: 6)

QUESTION: 17

Explanation:

QUESTION: 18

Explanation:

QUESTION: 19

Explanation:

QUESTION: 20

Explanation:

TDVAN5 Discussions & Posts