This is an update to an earlier posting in which data dictionary statistics to collect in Teradata Database 15.10 were recommended. This posting makes recommendations for dictionary statistics from the Teradata Database 16.10 perspective. You may not require all these statistics, but they cover the tables and columns most likely to be accessed in the data dictionary views.
On the Enterprise platforms, Teradata Active System Management (TASM) workload management offers a priority level called the SLG Tier, composed of five levels. Workloads that have a service level goal (SLG) or that are special for some reason can be placed in one of the SLG Tiers. Workloads in the SLG Tier get access to resources ahead of workload placed in Timeshare, which is lower in the priority hierarchy.
Workloads assigned to SLG Tiers have a richer set of workload options and require different setup techniques than workloads placed on other levels of the priority hierarchy, such as Timeshare. This posting describes a not-well-understood characteristic of SLG Tiers that limit the maximum and minimum resource allocations that workloads on such a tier will receive.
When a “Workload” is defined in Teradata Workload Management’s TASM feature it may be given a supplemental capability called a “workload exception”. When a workload exception is defined, it will cause characteristics of each running request that classifies to the workload to be monitored. The action taken when a workload exception reaches a specified threshold will depend on the type of exception being defined. There is one peculiarity in what exception actions can be associated with a workload exception, and that will be discussed in this posting.
I came across an interesting behavior concerning statistics histograms that has to do with NULL values. This posting explains and illustrates this behavior. If you observe this phenomena at your site, know that things are working as designed.
Workload Management Classification with IPE Queries
Incremental Planning and Execution (IPE) is a framework within the database that provides a method to improve plans for complex queries. It does this by breaking a plan into “fragments” and interleaving optimization and AMP execution.
Workload management uses classification criteria that relies on knowledge of the query plan before to associate a query to a given workload. This posting explains how the classification process functions when only a fragment of the plan is available before the query begins to execute.
If a table has fallback defined, the fallback AMPs for a primary AMP are included in the range of AMPs that are defined for the contiguous map. In other words, both primary and fallback copies share the same contiguous map. In the initial software release for the MAPS feature (Teradata Database 16.10), fallback rows are not allowed to be placed on AMPs that are not included in the contiguous map where the primary rows are located.
However, fallback placement works a little differently when a table is moved to a single-AMP sparse map.
The MultiLoad utility, whether used as part of the legacy stand-alone utility or as Teradata Parallel Transporter Update Operator, is designed to transfer large amounts of data from a client to the Teradata Database. In order to maximize throughput, MultiLoad allows the administrator to load data through more than one session.
The question this blog posting asks and then answers is this: If a MultiLoad job uses a low number of sessions, will that reduce the number of AMP worker tasks (AWTs) the job will require? In other words, is there a correlation between number of sessions started up by the load utility job and the number of AWTs it requires?
Most of the resources of a system are consumed by requests running in workloads that were defined by the administrator, workloads that are managing user-initiated work. However, some small percentage of resources, mostly CPU, is utilized by what we call internal workloads. This posting will help you understand what kinds of activities are running in those internal workloads, and some of the reasons why utilization there can and will fluctuate.
Throttles are a workload management technique for controlling concurrency in a data warehouse. When a throttle is an active part of a TASM or TIWM ruleset, a counter is kept of how many requests are currently running that are under its control. When a request wants to begin to execute that would cause this counter to exceed the throttle’s defined limit, that request is placed in delay queue.
It is not unusual for a request to be under the control of more than one throttle, so when you are analyzing throttle impact after the fact, it can be difficult to know which throttle was responsible for the delay action. There is a field in DBQLogTbl named TDWMRuleID that can aid you in making that determination.
With Teradata MAPS Feature, you can expand the hardware configuration and choose to postpone the redistribution of tables from the old AMPs to the new AMPs. This delay in moving rows to the new AMPs can provide a significate reduction in down time during the expansion window. Postponing table redistributions is enabled by allowing multiple hash maps (the old hash map that covered the previous configuration and the new hash map for all of the AMPs in the new configuration) to co-exist at the same time. It will be up to the administrator to decide when to move tables into the new, larger map.
The move of a table from the old map to the new map is accomplished by a new type of ALTER TABLE command. When an ALTER TABLE statement that includes a “MAPS = map_name” clause is issued, that ALTER TABLE will be processed similar to an INSERT-SELECT.
The INSERT-SELECT moves tables from one map to a different map is essentially doing the same thing as the standard INSERT-SELECT. However, there are a few differences, which will be highlighted in this posting.