Masterful solutions

Got a technical question? A Teradata Certified Master is here to help.

by Carrie Ballinger

I see a lot of helping hands at work within the Teradata world. Every time you, the reader, post a response on one of the Teradata e-mail groups, stand up at a Teradata User Group (TUG) meeting to answer a question or participate in one of the PARTNERS committees, you are volunteering a bit of your time and knowledge to a larger community of users—enriching the Teradata knowledge pool.

I like to think that this column is a small part of that widespread chain of Teradata volunteerism. Many of the subjects I write about here come from ideas someone else shared with me first, often at meetings such as TUGs. So for me this is an opportunity to pass along the favor by sharing them with you.

Lock contention updating NUSIs

Dear Carrie: I have a table with several non-unique secondary indexes (NUSIs). When TPump loads the table, does each row's insert cause a table-level lock on each of the index sub-tables? Wouldn't this create blocking? —Loading in London
Dear Loading: It's natural to assume that because access via a NUSI always causes a table-level read lock to be set, then updating a NUSI would have the same locking scope. But that is not the case. When loading with TPump you can really cross NUSI update contention off your checklist, and here's why.

First, NUSIs are structurally less contentious. NUSIs are AMP-local, with each AMP having a dedicated NUSI sub-table for its rows. You can insert a row on your AMP with a zip code NUSI value of '90230' and I can insert a different row on my AMP with the same NUSI value, and we won't step on each other's toes.

Second, the NUSI doesn't undergo traditional locking. When the TPump base-table row insert takes place, the file system places an internal, temporary lock on the data block within the index sub-table where the NUSI maintenance will take place. These internal locks are not transaction-type locks that can be explicitly specified in a LOCKING ROW modifier, and they don't show up in the explain text. Rather, they are one level lower and are held very briefly, just for the moment in time when the NUSI sub-table is actually updated—not for the entire step and most definitely not until the end of transaction (see figure 1).

If more than one TPump insert needs to update the same NUSI data block on the same AMP at the same point in time, these updates are handled serially, with only the briefest of delays to any of them.

The other really great thing about NUSI updates is that there is no transient journal overhead, as there is with unique secondary index updates. The inserts to the NUSI sub-table will be automatically reversed as part of the rollback of the base-table insert, since they are conveniently located on the same AMP.

Cancel rollback

Dear Carrie: I noticed that starting in Teradata Database V2R5.1, I can cancel a rollback that is in progress. I'd like to know if the rollback is canceled at the transaction level or the table level. If there are three different transactions accessing the table when the cancel is issued, is the table still usable, or does it have to be dropped? —Rolling Back in Houston
Dear Houston: You should be very cautious of this command. It doesn't come with an undo button, and you can't change your mind afterward. CANCEL ROLLBACK is a command with the Recovery Manager utility that is always applied at the table level. You need to stop and think before using it because it will make that table immediately unavailable for any future transactions. Once you issue that command, it marks the table header for that table as invalid.

CANCEL ROLLBACK is designed for cases where a long-running rollback has gotten under way, and it would be more efficient and less disruptive to simply restore or reload the table. It could also be useful if the table rolling back is not widely used or just not important. You'll want to issue a DELETE ALL on the table and follow that up with a RESTORE after canceling. In fact, if I were you, I'd put plans in place to do that before I pulled the plug on the rollback.

Query milestones and coexistence systems

Dear Carrie: My Teradata system has a combination of NCR 5250 and 5300 Server nodes. I saw your article about query milestones in Teradata Magazine, Vol. 5, No. 2, and have followed your guidelines. However, because we have different node types, I'm wondering if the milestone thresholds will be enforced in the same way on all nodes. —Coexisting in California
Dear Coexisting: Good question! First, I understand exactly what you are worried about: If you have various types of nodes, work is done more quickly on the newer, more powerful nodes, and you wonder whether this will cause the milestone limit to be hit at different times, since those limits are enforced at the node level.

Let's walk through a simple example that I put together to help me to visualize this scenario. Assume you have two types of nodes. Node A is an older node type, and Node B is newer and twice as powerful. Because Node B is twice as powerful, it has been configured with twice as many AMPs as Node A, as shown in figure 2.

Now, let's further assume that you select a query milestone limit of 10 seconds. See what happens when an evenly balanced query begins to execute:

• Each AMP is doing the same amount of work in the same amount of time. In the chart above, the power of each AMP is equivalent on each node: (Baseline / 10) = (Baseline * 2 / 20). This is a result of assigning the more powerful node type more AMPs.
• The milestone limit is met on one node when that particular query's CPU usage from all AMPs on that node totals 10 seconds.
• Node A's AMPs each use twice the CPU to accomplish a unit of work, compared to Node B's AMPs. But since there are only 10 AMPs per node, each will get twice as much CPU in the same amount of wall-clock time as Node B's AMPs.
• Conversely, Node B's AMPs will require half the CPU to accomplish their work, but because there are 20 AMPs per node, it will take them the same amount of wall-clock time to accomplish the work as the AMPs on Node A.

If the number of AMPs selected for each node type is appropriate and you have minimal amount of unusable CPU on any one node type, then query milestones should work just the same as they work on a uniform configuration (see figure 3).

For more on query milestones, see "Making the most of it."

AMP worker task use

Dear Carrie: Are AMP worker tasks (AWTs) held by queries when they are returning answer sets? During a quiet time on my system, I noticed 10 sessions, all in RESPONSE mode.When I issued a puma-c command there were no AWTs in use. What's happening here? —Curious in Canada
Dear Curious: There's always a lot of interest in AWTs, and I think this is because these are the worker bees inside the database—processes that are created at start-up that are available to do any work that needs to be done on an AMP.

Teradata was designed to have a limited number of AWTs on each AMP, so you want to know when and how they'll be used.

AWTs doing work on behalf of a query are released at the end of the final query step before the BYNET merge process begins, even when there is a large answer set to return. The last AMP to complete its work in building a query's final spool collects row counts from all AMPs, responds to the dispatcher that the step is complete and reports the final row count. The dispatcher then tells the BYNET to start a merge process.

The BYNET driver has special data buffers where the rows that will be used to feed into the merge must be placed. AWTs are acquired for the brief period of time it takes to move data from the final spool file into the BYNET buffers. If the final spool is large, there may be many such episodes, but it's likely under the radar for you because each such use of an AWT is so short.

Workload limit rules and deadlocks

Dear Carrie: I remember hearing somewhere that setting a Teradata Dynamic Query Manager workload limit rule concurrency limit as low as "1" can cause deadlocks, and that Teradata recommends that the limit be at least "3." I'd like to limit some of my users to one query at a time. Is there a risk to doing that? —Getting Control in Georgia
Dear Getting Control: The recommendation is not to set a workload limit rule limit below 3 if the workload limit rule is delaying queries (as opposed to rejecting them).

This is because of the possibility of having multiple active transactions under the control of the same rule, each transaction containing multiple queries bounded by a pair of BT/ETs. In such a case, one query could be delayed and the transaction to which it belongs could be holding a lock that is needed by a query that is part of a second transaction. The query within the second transaction is active, but it cannot start doing database work until it gets that lock; meanwhile, the first transaction cannot complete and release that lock until its query gets off the delay queue.

In Teradata Database V2R6, workload limits are renamed object throttles. Prior to V2R6 there was no deadlock detection within TDQM that can recognize a deadlock caused by the delay queue. Because of that, it was felt that 3 was a safe number, where a deadlock situation that required intervention would be unlikely. In V2R6 you can select deadlock detection that will handle this situation, so lower concurrency levels for your object throttles are acceptable.

In your case, if the workload limit is associated with users only, then in my view a concurrency limit of 1 or 2 is low-risk, since users would only deadlock against themselves. This would not be advisable, however, if you have applications with multiple sessions all using the same user ID.

Remember, a workload limit rule at the user level is enforced against each user who is associated with that rule.

For a discussion on overlapping workload limit rules, see "Learning from each other: Tips and insights gleaned from Teradata user questions." T

Carrie Ballinger, a Teradata Certified Master V2R5, is a senior technical consultant in Teradata's Active Data Warehouse Center of Expertise, based in El Segundo, Calif.