Learning from each other

Tips and insights gleaned from Teradata user questions.

by Carrie Ballinger

Once again, the 2004 annual Teradata PARTNERS User Group Conference and Expo created a great opportunity to network, listen, learn and exchange tips. While walking through the hallways of the Seattle Convention Center during last October's event, I was stopped often and drawn into some fascinating technical discussions. In this column, I'd like to pass on to you a few of the more interesting questions that were thrown my way, along with the answers.

Stored procedures and Database Query Log

Q: In Teradata Database V2R5.1, the Database Query Log (DBQL) logs CPU usage. Does that include stored procedure SQL resource consumption?
A: Both the stored procedure call and each SQL statement within the stored procedure will be logged as separate entries in DBQL, each with a distinct Query ID. If the QueryText column contains the stored procedure call, for example, then it will contain something like "call pksscan (10, avgep)" and you will see zero in the TotalCPUTime column of that row. However, each SQL statement within the stored procedure will have its own row in DBQLogTbl and its own Query ID. Additionally, each SQL statement's CPU usage will be recorded in its TotalCPUTime column.

Let me pass on one additional piece of information concerning stored procedures and DBQL: When one stored procedure calls another stored procedure, both call statements are logged in DBQLogTbl. However, the nested stored procedure always gets a zero QueryID and nulls in the QueryText column. In the current release, DBQL does not have access to the request and session context when there is a call within a call.

Object Use Count and partitions

Q: I noticed there is a new capability in Teradata Database V2R5.1 called Object Use Count. Are there any tradeoffs to using this new feature? Will it tell me when a partition of a primary partitioned index (PPI) table has last been accessed?
A: Object Use Count is designed to improve performance analysis capabilities by tracking how many times objects like indexes, columns and tables have been accessed in user SQL. If turned on, it collects and records access counts and last-accessed times in Teradata Data Dictionary fields for all queries.

The feature uses logging techniques similar to DBQL's. A special cache is created for the use count entries in the parsing engine, which is merged into the appropriate dictionary tables based on a flush interval—usually every 10 minutes. As with DBQL, you will lose entries that are being held in the cache during a database restart.

Object Use Count is not intended to be on at all times, and it does not guarantee that all accesses have been captured. One recommendation is to keep it on for a week or two to get a list of database objects that are not showing any usage. Then you can investigate those particular objects.Object Use Count does not break down table accesses by partition, so it will not be helpful for understanding and evaluating partition usage.

Overlapping workload limit rules

Q: I have defined a Teradata Dynamic Query Manager (DQM) workload limit rule on one Teradata Priority Scheduler resource partition. I did this by creating a workload limit of 50 queries and associating each of the three active performance groups within that resource partition to the rule. I also have separate, more restrictive workload limit rules on each of the same three performance groups. My goal is to limit each performance group at different, much lower concurrency thresholds and, at the same time, to control total concurrency within the resource partition to 50. Will that work?
A: Unfortunately, the answer is no. If you have several different context objects associated with a workload limit rule and they are all context objects of the same type (three performance groups, for example), each will have an independent counter. Each performance group will have its own limit of 50, which will be enforced separately. Activity levels across all three performance groups combined will not be considered.

In addition, any time you have a query affected by more than one workload limit rule and, assuming the rules are associated with the same context object (the user that issued the query, for instance), Teradata DQM will apply the most restrictive limit for that object (the user) to the query and ignore the other limits. In your case, a given query in the delay queue is only being checked against one workload limit rule concurrency count—the most restrictive one—for the performance group.

The documentation does not clearly express this, but it is possible for you to create a situation where a given query would have to satisfy three different workload limits before it could escape from the delay queue. Let's suppose you create a workload limit rule associated with a specific user, a different workload rule associated with a specific account and a third rule associated with a specific performance group. In other words, you create three rules, each associated with a different type of context object. A query issued by that user within that account under the control of that performance group will not be removed from the delay queue until it has satisfied all three limits. Only one workload limit per context object type will be enforced, but because there are three different allowable context object types, a given query could be required to satisfy up to three limits (figure 1).

Database Query Log vs. access logging

Q: Does Teradata Dynamic Query Manager require the use of access logging, or can it get everything it needs from DBQL?
A: There is no requirement; in fact, there is actually no interplay between access logging and Teradata DQM—but they are not incompatible. You also are not required to use DBQL if you have Teradata DQM rules enabled, although most users do.

The added advantage of using DBQL when you are using Teradata DQM workload limit rules is that, at the end of the day, you can identify which queries were placed in the delay queue and calculate how long they were there. DBQL also allows you to use Teradata DQM warning mode with some, or all, of your query resource or object access rules. If you have begun logging for those users, DBQL will show you the queries that would have been rejected if those rules had been enforced.

Prior to DBQL, access logging was relied upon to do things that were beyond its original intent, such as general purpose logging of the SQL of each query. However, with the growing acceptance of DBQL as a query logging facility, it will be easier for people to use access logging for its intended purpose, which is security.

DBQL is an appropriate tool to take over the query analysis function, including capturing the SQL and, in Teradata Database V2R5.1, the resource usage. DBQL is streamlined for collection efficiency and provides a lot more detail about the query. However, because DBQL caches data in memory before writing it periodically to disk, there is some delay in seeing the logged data, and it is possible to lose data that is still in the cache during a restart.

With access logging, logged rows are written immediately to disk before the SQL is executed. While this approach may have an impact on very short query work, it does offer higher reliability and can provide greater detail on negative accesses (attempts to view data that did not succeed).

I would continue to use access logging for security and auditing purposes and use DBQL to capture the details needed for performance tuning and resource usage analysis. T

Need tech support? E-mail your questions to teradata.query@teradata-ncr.com.

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