Tools to Troubleshoot and Administrate | Teradata Vantage - Tools for Troubleshooting and Administrating - Advanced SQL Engine - Teradata Database

The AWT Monitor (awtmon) utility quickly views how many AMP worker tasks are active (in use) and determines “hot AMPs” so you can troubleshoot performance problems.

awtmon is a front-end tool to the “puma -c” command and prints AWT in-use count information in a summary format.

By default, awtmon displays the AWT in-use count of the local node. With the -s option, print system-wide information and locate hot AMPs on the nodes of the entire system. When hot AMPs are identified, run awtmon on those hot nodes using pcl or psh.

You can also use the ResUsageSawt table to see AMP worker tasks in-use counts by work type and determine when AMPs enter the state of flow control.

To determine which workload is responsible for I/O skew, use the ResUsageSps table and then use DBQL to identify specific queries.

Because calculating checksums requires system resources and may affect system performance, system-wide checksumming is disabled by default on most platforms. Contact Teradata Services if you suspect disk corruption.

Use the CHECKSUM option of CREATE TABLE, ALTER TABLE, CREATE JOIN INDEX, and CREATE HASH INDEX statements to enable checksums for a table, hash index, or join index.

Previously halted SCANDISK operations can be restarted using a Perl script.

The commands of this utility allow the system to combine free sectors on a cylinder, reconfigure contents on a disk leaving a specified percentage of space free for future use, report which tables qualify for packing and display disk cylinder utilization and available free cylinders.

Use Update DBC only to correct inconsistency in the DBase or DataBaseSpace tables, which might occur as a result of rare types of system failures.

• Use the DELETE ALL/DROP table statement.
• Rebuild the table using the Table Rebuild utility.
• Restore the table from backup.
• Drop and recreate the index.
• Drop and recreate the fallback subtable.

After repairing a down table, use the ALTER TABLE ... RESET DOWN statement to reset a down table.

In the case of hardware errors, a fallback table can be rebuilt to copy the rows from the fallback subtable, to repair the rows in the down regions of the primary subtable, and vice versa. If the down table is rebuilt or restored from an earlier back up, the down status flag for the various subtables will be reset, and the rows in them will also be moved to unmarked data blocks and cylinders from the fallback copies.

To change the number of regions per AMP that must fail before a table is marked as down, use the MaxDownRegions field in DBS Control.

See “Table” in this table for more information.

You can use options in the Gateway Control utility to select things like connection time out length, external authentication settings, or whether to use the system default or customer-settable defaults after a configuration or addition of new host groups and gateway vprocs, and more.

You can also use the DBQL utility job log table, DBC.DBQLUtilityTbl, to troubleshoot utility job performance.

Careful planning of what sessions run in which order or at what time of day can help prevent lock contention. Use the EXPLAIN. Consider running a request that needs an EXCLUSIVE lock and will take a while to process during off hours.

You can access the DBQL lock log through the Viewpoint lock viewer portlet or query the system table DBC.DBQLXMLLOCKTbl or the view DBC.QrylockLogXMLV.

HUT locks may interfere with application processing and are normally released after the utility process is complete.

If locks interfere with application processing, you can remove them by invoking the RELEASE LOCK statement. It is available through the DSA utility or as an SQL statement.

Showlocks can be started from the DB Window Supervisor screen with the command start showlocks.

Use the ResUsageSps table to understand how CPU is being consumed by different workloads. You can also use this table to understand the pattern of database activity across the processing day.

Alternately, use the STEPINFO option of BEGIN/REPLACE QUERY LOGGING to determine which step uses the most resources and if there are any large differences between estimated and actual CPU, I/O, and number of rows.

Then after that query returns the session number n, use that number in the following query:

sel logonsource
from sessioninfovx
where sessionno =  n;

*** Query completed. One row found. One column
returned.
*** Total elapsed time was 1 second.

LogonSource
-------------------------------------------------------
(TCP/IP) 09AB 127.0.0.1 DBC    8804  USER  BTEQ  01 LSS

This helps you determine which Client process/Thread ID belongs to which session. This is especially useful if you want to find out which Thread ID belongs to which session because you are logged on to multiple sessions.

• Use Teradata Viewpoint to determine which user is using the most CPU. In general, a bad query can be detected by finding users with high CPU relative to I/O access.
• Look at the EXPLAIN text to determine the number of steps used for the query and the type of operation. You may find something like a product join in a poorly written query. You can abort the query and investigate further or fix the query.
• To identify node-level skew, use the ResUsageSpma table, comparing maximum CPU usage to the average CPU consumed across all nodes. If parallel efficiency as shown in the ResUsage Spma table is not approaching 100%, look for the reasons for the imbalance.
• To find AMP-level skew, there are several options. Use the ResCPUByAMP macro to find AMP-level CPU skew. You can look at the ResUsageSpvr table and compare resource usage across different vprocs. You can also look at the number of requests field in the ResUsageSps table to see if workloads on one node are actively supporting more requests than on other nodes, then drill down to AMPs. Also using the ResUsageSps table, you can examine queue wait and service time numbers to find backed up queries by workload or allocation group.
• To find skew at the request level, you can compare DBQLogTbl fields MaxAMPCPUTime with MinAMPCPU. Because user logging is often incomplete with DBQL, ResUsage tables and macros may provide more accurate information.
• Another option is to use PM/API to develop your own application to monitor for high CPU consumers.

The settings apply to all operations and all instances of space accounting, such as table inserts, so Teradata recommends setting these fields to a non-zero value only temporarily.

• Not using global space accounting, which allows extra space to AMPs when needed.
• Poorly written queries can sometimes consume excessive spool space. Use Teradata Viewpoint to check queries before allowing them to run. You can check the EXPLAIN to see how much spool is used and prevent the system from getting overloaded with bad queries.
• Outdated statistics can cause bad queries. The Optimizer may calculate query plans differently than it ought if statistics it uses does not accurately reflect the system. The Optimizer also determines how much spool it needs or does not need based on collected statistics. Refresh collected statistics regularly.
• If you are using OLAP statistical functions and you run out of spool, check the syntax of your queries. For example, when using the PARTITION BY clause, if a large number of identical values in the partitioning column hash to the same AMP, this can result in out-of-spool errors. Choose a column that results in rows being distributed over a broader set of AMPs.
• Poorly skewed data. Poor data distribution means one AMP is responsible for a larger proportion of the data. Because the spool space is divided by the number of AMPs, reaching the maximum spool space limit on one AMP means your query will fail even despite the other unused spool space on the other AMPs.
• Poorly chosen PI that leads to AMP skew. Because the spool space is divided by the number of AMPs, reaching the maximum spool space limit on one AMP means your query will fail even despite the other unused spool space on the other AMPs.
• Adding nodes or AMPs without reassigning spool space means spreading out the spool amongst more AMPs.
• The limit for spool space as defined in the profile overrides the user settings. Check the limit for the profile to see if it is lower than the limit defined for the user. You can compare the values of MaxProfileSpool and MaxSpool in DBC.DiskSpace.V

Limiting spool space for new users helps reduce the impact of possibly bad queries (such as product joins). With a reasonable limit, the user will get out-of-spool messages before a bad query ties up the system. Furthermore, adding spool to a bad query will allow the query to run longer but a bad query may still not complete.

Allows you to display mapping or force migration of cylinders on the system.

The Event_Tag field of the view reports the error message number.

• The primary or fallback portion of a table
• An entire table (both primary and fallback portions)
• A particular range of rows of a subtable
• The corrupted regions of a table
• A corrupted or missing table header
• All tables
• All fallback tables
• All tables in a database
• Single tables sequentially or tables of each database in parallel.

• Table and dictionary inconsistencies, such as differing table versions, ParentCount and ChildCount data, and partitioning definitions.
• Table corruption, such as duplicate rows or unique values and data inconsistency, of primary and fallback data and stored procedure tables.
• Inconsistencies in internal data structures such as table headers, row identifiers, secondary indexes, and reference indexes.
• Invalid row hash values and partition numbers.

CHECKTABLEB is a non-interactive batch mode version of CheckTable that can be run through the cnsrun utility. CHECKTABLEB is identical to CheckTable except that it is meant to run from a script.

You cannot rebuild a table with parts of its fallback down. While you can rebuild stored procedures, UDFs, and UDMS, you cannot rebuild a join index or hash index defined on a table being rebuilt.

• View or change vproc states
• Initialize and boot a specific vproc
• Initialize the storage associated with a specific vproc
• Force a manual restart

To administrate vprocs and disks, use the Vproc Manager utility.