One of my favorite radio programs, a food talk show, is broadcast every Saturday morning from 8 to noon here in L.A. The program is composed of cuisine questions and answers, cooking tips and techniques. Although I'm an amateur in the kitchen, I inevitably learn something new to drive me toward better cooking experiences, while having a good time along the way. That's the same reason I enjoy writing this column, which is based on technical questions I've received and my responses back. I hope to help you make better use of the Teradata features and functions available to you, whether you're a novice or a certified master chef on your system.

Figure 1: Two types of delayed queries Queue Table Delay: If the Queue Table is empty when SQL to SELECT and CONSUME is issued, then the SQL will go into a delayed state until a row is placed in the queue table or until the SQL is aborted. The query will not be placed in the Teradata DWM delay queue. Object Throttle Delay: If a query under the control of an Object Throttle rule would cause the counter to exceed the throttle limit, the query is marked as delayed and placed in the delay queue.

Queue table and object throttle delays
Dear Carrie: I have some stored procedures with SQL that issue SELECT AND CONSUMEs against a queue table. Sometimes these SQL statements are reported as being in the delay queue when they are truly not. So in this regard, queries being delayed by object throttles and queries being blocked waiting for a queue table row look the same to me. Is there any way I can differentiate the two?
—Curious in Canada

Dear Curious: Queue tables are database objects similar to tables but with the properties of a first-in first-out queue. Queue tables enable queries to wait for the appearance of data or events without polling. Once a row appears and is read in consume mode, it is physically deleted from the queue table.

If a query is waiting to SELECT AND CONSUME from a queue table, but the queue table is empty, then that query will be given a status of "delayed," which is the same status a query is given when it is placed in the delay queue by a Teradata Dynamic Workload Manager (DWM) object throttle (see figure 1, bottom). Having two different features sharing the same status is confusing, I agree, but this is a temporary situation. In the upcoming V2R7.0 release, only a query that an object throttle places in the delay queue will have the status of "DELAYED." The query waiting for a queue table row will be given a different status of "QTDELAYED." (See figure 1, top.)

Here's how to tell the difference. If the query can be seen when viewing the Teradata DWM delay queue, its delay is due to an object throttle. The same is true if the query gets reported with a value in the database query log's (DBQL's) DelayTime column. Neither will happen when a query is waiting to access a queue table.

Blocking on exclusive locks
Dear Carrie: My tactical queries perform well most of the time. However, at certain times of day, usually when I am dropping and recreating join indexes, some tactical queries get blocked for long periods of time, even though I have access locking on the queries.
—Blocked in Britain

Dear Blocked: Access locking will allow your query to read through a write lock. But you may be experiencing contention from an exclusive lock, the only type of lock that an access lock cannot get around. Exclusive locks are set in these situations:

	ALTER TABLE
	MODIFY DATABASE
	DROP TABLE
	CREATE/DROP INDEX
	CREATE/DROP JOIN INDEX

TIP: Minimizing blocking on exclusive locks When dropping partitions on a table, if NUSIs or join indexes are present, it is recommended that the rows be deleted from the partition before issuing the ALTER TABLE to drop the partition. When modifying a database (call it Database X) to add space, build a dummy database and assign that database the space you wish to add to Database X. Then use the GIVE command to give the dummy database to Database X. When you drop the dummy database, Database X will get the space that was originally assigned to the dummy database with no exclusive lock.

enlarge When set up appropriately, both Object Throttles and Teradata Active System Management can allow queries with an estimated time that is below the limit that you set to run immediately, while queries with estimates above the limit will be subject to delay.

Once applied, an exclusive lock is designed to hold for only a short time. However, if the exclusive lock has to wait on other transactions to complete in order to be placed—because other transactions are either reading or updating the desired object—then queries with access locks that are queued for a lock behind the exclusive lock will have a longer wait. Transactions with access locks cannot move ahead of transactions queued up to place exclusive locks in the lock queue.

Here are a couple of places in an active data warehouse where you might experience unexpected blocking by an exclusive lock:

When a partitioned primary index (PPI) table is undergoing maintenance. The ALTER TABLE command that adds or removes partitions uses an exclusive lock on the base table. If that PPI table participates in join indexes or has non-unique secondary indexes (NUSIs) built on it, then the operation that drops the partition will have to update those other structures as well. That index maintenance overhead may extend the time it takes to drop a partition.

During DBA maintenance on a database. Changing the space requirements of a database will require a MODIFY DATA-BASE command, which locks up the entire database until the process is complete.

Teradata Active System Management concurrency limits
Dear Carrie: My site plans to use Teradata Active System Management. Here's my question: We currently have several workload limit rules in place to control concurrency, and we use the step time threshold option to let the shorter queries run without delay. I don't see a place in Teradata Active System Management that allows you to have a step time threshold on your concurrency limit rules. This has been the most effective way of using Query Manager for us. Did it disappear?
—Making Plans in Atlanta

Dear Making Plans: While I agree that there is no step time threshold with Teradata Active System Management's Workload Definition (WD) throttles, that should not be a disadvantage for you. If you are using Teradata Active System Management, the assumption is that you have set up classification criteria to send the really short work to a WD, which typically would not need a concurrency limit. And, further, that you have classified the longer-running work to a different WD that likely would have a concurrency limit. In other words, you can expect to get the same functionality provided by the step time threshold by relying on Teradata Active System Management query classification (see figure 2). In fact, query classification uses the same explain estimates as does the step time threshold. When properly classified, the short work should always run without delay under Teradata Active System Management.

Statement type in DBQL
Dear Carrie: Access logging provides a 20-character description of the statement that executed. This gives me an easy way to characterize my workload. I'd like to start using DBQL more, but I can't find anything similar that will let me easily evaluate the different types of statements that are being issued on my platform. Got any ideas?
—DBQL Fan

Dear Fan: Currently, your only choice is to parse DBQL's SQLText column to identify the different statement types that you may be interested in. In Teradata Database V2R6.2, a new column called StatementType will be available in the default logging table. This will have the same function as the statement type that you are already using in access logging.

ExtraField5 in the DBQLogTbl will be used to hold this StatementType information. Once you get on V2R6.2, you will be able to issue SQL such as the following. (I'm showing you only a sample of the 125 different statement types supported.)

SELECT 
  StatementType,
  COUNT(*) 
FROM DBC.Qrylog 
GROUP BY StatementType;


StatementType		Count(*)
--------------------      ----------

Alter Table		    2
Begin Loading		    5
Collect Statistics		   54
Create Table		  641
Drop Table		  768
Grant			  229
Select			13143
  
  

Dear Seeker: When the optimizer sees an ordered-analytic function in a query, it produces what is called a "stat function step" to execute the function. At the time the plan is built, the optimizer does not know exactly how rows will be positioned to support the ordered-analytic function, thus the vague text. Stat function steps can use either hash partitioning or value partitioning. The AMP makes that decision after receiving the step for execution by sampling the data on which the ordered analytic function will operate. Sampling gives the AMP a feel for the number of GROUP BY values, as well as the number of rows that match each such value

If the value partitioning approach is selected, a "value partition map" is built. This map is a matrix where each AMP is "mapped" to each GROUP BY value. Each AMP is assigned GROUP BY values according to the total number of rows. So groups may be split up across AMPs. The overriding aim in this mapping approach is an even distribution of rows across AMPs, to effectively use the database's parallelism in performing the complex function.

If the number of GROUP BY values is low compared to the number of AMPs in the system, and/or the number of rows that match each GROUP BY value is also low, it's not worth that extra mapping and repartitioning effort. This might be the case for a small table or small input spool file.

The alternative approach to value partitioning, hash partitioning, simply takes advantage of the fact that the rows to be processed are already in a spool with rows that have been positioned based on the hash of the GROUP BY column. This repositioning may have come from a redistribution done in a previous step in the plan, or the rows may already be hashed appropriately without the need for redistribution. The stat function processes rows locally without the overhead of value mapping.

EXPLAIN
SELECT 
	orderkey,
	(current_date -  shipdate) AS WaitTime,
	RANK (WaitTime) AS "Rank_Wait"
FROM 
	Items
WHERE  Partkey BETWEEN  151 AND 374
ORDER BY Rank_Wait;
  

1) First, we lock a distinct ADW."pseudo table" for read on a RowHash to prevent global deadlock for ADW.Items.
2) Next, we lock ADW.Items for read.
3) We do an all-AMPs RETRIEVE step from ADW.Items by way of an all-rows scan with a condition of ("(ADW.Items. PARTKEY <= 374) AND (ADW.Items. PARTKEY >= 151)") into Spool 2 (all amps), which is built locally on the AMPs. The size of Spool 2 is estimated with no confidence to be 771,692 rows. The estimated time for this step is 6.58 seconds.
4) We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by way of an all-rows scan into Spool 5 (Last Use), which is assumed to be redistributed by value to all AMPs. The result rows are put into Spool 1 (group_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1.
5) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.

Priority of returning rows
Dear Carrie: What impact, if any, does Priority Scheduler have on returning answer sets after the query has completed? In other words, does it take longer to return a large answer set from Teradata for users with a lower priority compared to users with a higher priority?
—Tuning in Texas

Dear Texas: The priorities involved in returning answer sets are different than the priorities used to execute the query. AMP worker tasks are used only infrequently during answer set return. (See Managing AMP worker tasks, Teradata Magazine Vol. 6 No. 2 for more details.) AMP worker tasks are only used for the brief periods of time needed to move rows into the BYNET driver buffers, and this CPU time is very minimal. These AMP worker tasks are requested by the interconnect merge code, which generates a message asking for more rows that go to the AMP. All such messages, no matter the user priority, are set to the maximum priority in the system.

But keep in mind that the great majority of the effort of returning rows is taking place within the BYNET driver, the network and the client. The BYNET driver is running at operating system priority, and the operating system always gets its CPU ahead of the AMP worker tasks inside of the Teradata Database.

Once a query begins to return an answer set, user priority plays no role. Whether the query was executing at a high or low priority has no impact on the rate at which those rows are returned. T

Carrie Ballinger is a Teradata Certified Master V2R5 working for Teradata in El Segundo, CA.

� Teradata Magazine-September 2006