Key details for this exam, checked against the published exam outline
Each question shows the correct answer and an explanation of why it is right
Users are complaining that queries in their workload are taking too long. The Administrator suspects that workload management may be throttling the queries.
Which table should be analyzed to confirm this suspicion?
The ResUsageSawt table captures information related to AMP Worker Task (AWT) usage and workload throttling. If queries are being throttled due to workload management, this table will provide relevant data, including details about tasks that may be waiting due to AWT limits or other resource constraints.
Option A (ResUsageSps) focuses on stored procedure statistics and may not provide direct insight into query throttling.
Option C (DBQLogTbl) logs query-level statistics but does not directly capture information about throttling due to workload management.
Option D (DBQLStepTbl) contains detailed information about query execution steps but, like DBQLogTbl, it does not provide information about throttling.
An analytics team uses a multibillion row table that is relevant to a great number of queries with different filters and joins. The Administrator needs to identify an effective strategy to collect statistics on this table.
Which statistics should be collected?
Dynamic AMP Sample is an efficient method for collecting statistics on large tables. It collects sample statistics from a subset of AMPs (Access Module Processors) in the system, making it much faster and less resource-intensive than collecting full-table statistics, while still providing sufficiently accurate information for the optimizer.
Full-table statistics collection would be too resource-intensive for a multibillion-row table, potentially causing performance issues due to the size of the data.
Sampled statistics might be an option, but Dynamic AMP Sample is generally preferred because it provides a more efficient and balanced approach in large distributed systems like Teradata.
Summary statistics typically apply to aggregate data rather than large, detailed tables, and would not be sufficient for query optimization across different filters and joins.
Hence, Dynamic AMP Sample is the most effective strategy in this scenario.
A user runs a number of large, complex queries that regularly generate insufficient memory errors. The Administrator increases the value of the MaxParseTreeSegs DBS control field in order to allocate more parse tree segments to the parser for parsing requests.
Which action must be taken before the queries will run without memory errors?
When the MaxParseTreeSegs value is increased, the new settings will not take effect for existing sessions. The user must log out and log back in to start a new session where the updated configuration will be applied.
Other options are not relevant in this case.
An Administrator has been tasked with analyzing previous growth and usage patterns by utilities such as Multiload and FastLoad. The aim is to project the likely resource requirements of the existing loads in the next three to six months.
In the last month, the Administrator team have started using AccountString expansion on all user accounts and have started maintenance jobs to housekeep system data older than seven years. Two years ago, the following command was issued on the system:
BEGIN QUERY LOGGING WITH ALL ON ALL;
No other logging commands have been issued.
Which view contains the utility usage data for the prior months?
QryLogUtilityV is a specific DBQL (Database Query Logging) view that provides information about utility usage (such as Multiload and FastLoad) on the system. Since the BEGIN QUERY LOGGING WITH ALL ON ALL command was issued two years ago, the DBQL (Database Query Logging) has been tracking various events, including utility usage, which is stored in QryLogUtilityV.
Option A (AMPUsageV) contains AMP (Access Module Processor) level statistics and usage data but not detailed information about utility jobs.
Option B (LogOnOffV) tracks user logon and logoff activities but does not provide information about utility usage.
Option D (QryLogV) logs general query execution data but is not specifically focused on utility usage.
A Vantage instance includes the following four databases:
Tables Database contains base tables and associated index subtables
Upsert_Database: contains views and other objects to support base table modifications
Query Database: contains views and other objects supporting queries and reporting
Development_Database: contains objects supporting development of new data applications
The Administrator needs to add a new user that will perform batch-level functions.
Which action should the Administrator take to achieve this goal?
Batch-level functions often involve creating and modifying tables as part of large data loads or updates. To allow the new user to perform these functions, the Administrator should assign them a profile that grants appropriate permissions, such as the CREATE TABLE privilege on the Tables_Database, where base tables are stored.
Profiles are a common way to manage and control resource and security settings for users in Teradata, and assigning a profile with the CREATE TABLE privilege ensures that the user can create tables in the specified database without granting excessive privileges.
Option A (Grant the CHECKPOINT privilege on the Tables_Database): The CHECKPOINT privilege is related to checkpointing transactions, which is not directly relevant to batch-level functions involving table creation or modification.
Option B (Set the user's default role to one that has the CREATE TABLE privilege): While setting a default role can grant privileges, using a profile is a more scalable way to manage user resource and privilege settings, especially if the profile already exists with the necessary permissions.
Option D (Grant the CHECKPOINT privilege on the Tables_Database to the new user's profile): Again, the CHECKPOINT privilege is not directly related to the batch-level activities such as creating tables.
A Vantage customer is using the Hive Connector in QueryGrid to access a foreign server and push the processing onto the Hadoop Cluster. The Vantage Administrator is concerned about the performance implication of the temporary space that is consumed by these queries during execution.
What must the Vantage Administrator investigate?
When using the Hive Connector in QueryGrid to push processing onto a Hadoop cluster, queries generate temporary data that consumes spool space on the Vantage system. The temporary database name defined on the link is where this temporary data is stored during execution.
The administrator should focus on investigating the spool space allocations and configurations related to the QueryGrid link because this directly impacts performance, especially when managing large datasets or complex queries.
The other options are less relevant:
Perm space assigned to the user is not directly related to the temporary space used during query execution.
The default temporary NOS location pertains to object storage and is unrelated to Hadoop or QueryGrid processing.
Temp space assigned to the Teradata user is also less relevant, as the spool space and the QueryGrid link are the primary concerns for temporary space usage in this scenario.
A table that contains about two billion records is showing a bad response time for range-based queries on the order date column and frequently projects 10 out of 100 columns. The Administrator decides to convert the table to a column and row partitioned table.
Which resource is constrained?
The table contains about two billion records and is exhibiting poor response time for range-based queries on the order date column, along with frequently projecting only 10 out of 100 columns. These factors indicate that the I/O (Input/Output) subsystem is under strain, as large amounts of data are being scanned and retrieved unnecessarily for each query.
Row and column partitioning can significantly reduce the amount of data that needs to be read from disk by limiting I/O to only the necessary partitions (rows) and columns, thus improving performance.
CPU might also be involved, but in this case, the primary concern appears to be I/O due to the large volume of data being retrieved and processed inefficiently.
AWT (AMP Worker Tasks) and Space are less likely to be the primary issues based on the problem description, as there is no mention of concurrency issues (AWT) or running out of space.
Exam domains verified against: Official TeraData TDVAN5 exam guide, last checked October 2026.
Use Viewpoint to investigate system conditions and monitor external queries. Learn to identify constrained system resources and track data movement jobs with Viewpoint.
Master DBQL logging and use performance data to troubleshoot issues. Optimize queries, assign tables to hash maps, apply indexing strategies, and evaluate design impacts on performance.
Understand access logging and advanced security configurations. Use system log tables for auditing, manage database and object privileges, and configure external data access security.
Meet user security requirements through roles and proxy access. Learn profile functionality and key attributes needed when setting up new users.
Sample question from this domain above: Q5
Manage access rights and advanced analytics functions. Handle backup and restore strategies, capacity planning, and manage perm space, temp space, and spool space.
Sample question from this domain above: Q3
Use TASM features to classify and manage workloads. Apply throttles, exceptions, and utility management, and create dynamic rules using the state matrix.
Sample question from this domain above: Q1
Work with QueryGrid and NOS for data integration. Understand Data Mover for job scheduling and error handling, and assess performance implications of accessing external sources.
Sample question from this domain above: Q6
Common questions about the exam itself
