Being Smart About Using Workload Classification

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

Have you ever found yourself confused when setting up classification criteria for a new workload in Teradata?  You’re not alone.  This posting discusses the main principles at work when it comes to combining different classification criteria.  It also provides some general tips to help you do effective, clean, predictable classification, whether on your workloads, your throttles or your filters.

What is Classification

In both TASM (Teradata Active System Management) and TIWM (Teradata Integrated Workload Management) there are a common set of measures that are used to link a request to a workload.  The same pool of measures are used to determine which requests match to a system filter, or a system throttle.  These measures are called “classification criteria”.

For example, if a request comes into Teradata and carries certain characteristics that match to a given workload’s classification criteria, that request will then run under the control of that workload.  

Individual classification measures are called “types”.  Some of these types (such as utility and query band) are addressed independently in the general descriptions below.  The other types are grouped into different “categories” referred to as “Who”, “Where”, and “What” categories.

Here are the distinct categories and independent types of classification criteria:

Who category (called “Request Source Criteria” in Workload Designer):  This includes information known at session logon time, such as these types of criteria:

  • User
  • Profile
  • Account
  • Client IP Address

Where category (called “Target Criteria” in Workload Designer):   Where criteria include objects within the database that a request might use or operate on, such as these types of criteria:

  • Table
  • View
  • Server object
  • UDF name

What category (called “Query Characteristics” in Workload Designer):  What criteria has to do with the type of processing the request is expected to do, usually based on details included in the query plan, such as these types of criteria:

  • Join type
  • Estimated processing time
  • Statement type
  • Single/few/all AMPs

Utility type:  Utility criteria is used to create workloads that can only be classified to by utility jobs, and may include some or all utility job types, such as:

  • TPT Update Operator
  • TPT Load Operator
  • JDBC FastLoad

Query band type:  Query banding provides a mechanism for wrapping metadata around a query so that applications are able collaborate with the underlying database to influence workload, throttle and filter assignments.

 Basic Rule When Using  Multiple Classification Criteria

Often, a workload requires more than one classification criterion.   For example, you might want to classify all requests from a single application to a series of workloads, and then divide them across those workloads based on expected execution time.  There would be one workload for longer-running requests from this application, one for medium requests, and one for short requests.  You would need a Who classification to single out requests from this particular application (such as user or account).  Then you would need a secondary criteria to specify expected execution time (such as estimated processing time).

There are few simple rules used by the database when it comes to the outcome of combining multiple classification criteria.  The main rule to remember is the following:

Criteria of the same type are OR-ed.  Criteria of different types are AND-ed.

This means that if a workload includes multiple criteria of the same type (for example User A, User B and User C), then a request that is associated with only one of those criteria (User C, for example) will successfully match to that workload.  The request will not have to match to all three criteria if the criteria are of the same type (which would not be possible anyway, since a session has only one user).  Those three criteria of the same type are OR-ed together implicitly.

However, if the workload contains multiple Who criteria but they are of different types (User A,  Account  XYZ,  Application BTEQ)  then a request will only classify to that workload if it matches to all three of those different criterion types. Those three criteria, because they are of different types, are AND-ed together implicitly.

Places Where this Rule Does Not Apply

There are a few exceptions to this general rule, so let’s break these cases down by classification category:

Deviation in the Who category:  Starting in 15.10 you can OR Profiles and Users, even though they are different types.   That means that that if your workload contains classification on both (such as User B and Profile LMN) and you specify “OR” between them, then you request will match to that workload even if only satisfies one of those criterion.  This is an option.  You can still AND Profiles and Users if you wish.

In Viewpoint Workload Designer 15.10 when you add a User classification criteria, and then follow that by adding a Profile criteria, you will see a small drop down window between the two Request Source specifications, as shown below.  If you click on the drop down you will be given the option of using AND or OR between the two criteria.

Deviation in the Where category:  This across the board deviation from the basic rule of classification OR-ing and AND-ing when it comes to data base objects is not something new.  It’s always worked like this from the time classification first came into being with the introduction of TASM. 

Contrary to how Who and What criteria work, Where criteria representing different types of target objects (database, table, view, etc.) are always OR-ed together by the database.   For example, if a workload has classification for a specific database and in addition has classification for a specific table, a request would only have to satisfy one or the other of those objects to classify to that workload.  The request would not need to match both criteria.

Be aware that Workload Designer will currently AND What criteria of different types.  If you add target criteria of Database = Marketing and also add target criteria of Table = Inventory, Workload Designer will place an “AND” between them.  However, the workload management code in the database will interpret that relationship between the two different target classification types with an OR condition.  This discrepancy will be fixed in a future release of Viewpoint Workload Designer, at which time the AND will be replaced with an OR.

The What category:  The What category (Query Characteristics in Workload Designer) follows the general rule in all cases.  Multiple criteria of the same type are always OR-ed and multiple criteria of different types are always AND-ed.   For example, if you specify a workload with classification of Join Type = Product Join and classification of Statement Type = Select a request would have to match both criteria to classify to that workload.  But if the workload specified Join Type = Product Join and Join Type = Merge join, then the request only needs to match to one of those join types to classify to the workload. 

Query bands are treated as their own criteria type in Workload Designer.  If multiple query bands are being used for classification on a workload, they follow the same conventions, as shown below:

  • Query bands with the same query band name are OR-ed
  • Query bands with different query band names are AND-ed

The following example of multiple query bands…

SET QUERY_BAND = ‘Group=Mktg;  Group=CRM;  Importance=High’  for session;

Will be interpreted as…

          ((Group = ‘Mktg’  OR  Group = ‘CRM’) AND Importance = ‘High’)

Utility Classification Specifics

If you select the Utility classification, you will be disallowed from selecting secondary criteria on What criteria (query characteristics).   You may, however, select secondary classification in the Who  (Request Source), the Where (Target) categories, or the Query Band type, as shown below.

As shown above, if you set up a workload using the Utility category for your FastLoad and MultiLoad jobs of all types, Workload Designer will not offer you the ability to also select query characteristics, such as “estimated row count”.

On the other hand, if you are defining a workload and select What criteria first, such as “estimated final row count”, you will not be allowed to also select Utility type.  What criteria and the Utility category are considered to be conflicting types of criteria.

Evaluation Order

Evaluation order can help to manage the logic of many criteria and/or workloads. As requests are issued to the system, they are compared with the list of workload classification criteria, in the evaluation order that the DBA has specified, to determine which workload it belongs. When a match is found, the workloads later in the list are not considered. This can be both an advantage and disadvantage; if a request could be classified into two or more workloads, the order of evaluation will dictate that the one first in the list is the one that is chosen. For example, listed in the following order of evaluation:

WD-A: User=Payroll.

WD-B: User=Payroll and estimated processing time < 5 seconds.

If a short running request from user Payroll comes in, it will be classified to WD-A rather than WD-B because of the evaluation order. This can be corrected by moving WD-B ahead of WD-A in the evaluation order.

A good recommendation is to place any workload that carries a utility classification higher in the workload evaluation order than workloads that do not contain utility classification. This will ensure that utilities, which are intended to be processed as a single unit of work, are not erroneously classified to a workload without utility classification, and treated as several distinct queries.  That could be the case if in addition to utility classification, you classified the utility workload on user name, and there was already a workload with that same user name for queries to execute in.

Conclusion

When setting up multiple criteria for workload (or throttle or filter) criteria, keep things simple.  Use only the number and type of classification that you require to effectively separate the different types of work running on your platform.   Avoid, if you can, long Include or Exclude lists associated with a type of criterion.  Consider using wild cards (=*) in place of long lists.

When you add new workloads, remember to review the workload evaluation order to make sure that some requests will not be classifying too early in the list, and be put into an unintended workload.  Use the order of workload evaluation to put more specific definitions ahead of less specific definitions.

12 Comments

Nice article Carie. Thank you.

Enthusiast

Hi Carrie

Thanks for the article.

I have question, what happens if we want a workload to define on a table AND a database, as currently they are OR'd as you mentioned.  Consider the scenario where we have the same table name in 2 different database, one in database SAMPLE and one in database PROD, with the table in database PROD containing billions of rows, and the table in SAMPLE containing a few thousand rows.  We want to create a workload for the PROD sized table to limit full table scans, but only on that table in the PROD database, as the other tables in the PROD database are of a smaller size and don't warrant this restriction.

Cheers

Steven

Teradata Employee

 Steven,

When you specify a table name in Viewpoint, you have to select the database name first.   The "table" as a classification criteria is actually a "database+table" combination.  So there should not be a problem classifying on Prod.Table_name.

If you select "Database=PROD" as its own target selection criteria, then all objects within that database are included.  In that case a table scan on any table within the PROD database would potentially classify to that workload (based on how you set up other workloads and the workload evaluation order).

Thanks, -Carrie

Community Manager

Thank you Carrie!

Community Manager

Very good article.  Thank you.

Teradata Employee

Tony,

 

Glad you enjoyed it!

 

Thanks, Carrie

Enthusiast

Hi Carrie

Do you know if you can use a Global Temporary Table in the Target classification criteria for a workload ?

I have a workload setup that has classification criteria: 1) Request source of an Account string, 2) Request source of an Application id, and 3) Target to exclude a table.  When I check DBQL for queries that run with the specified account string, application id and the table, they appear in the workload, however my workload has an exclusion for that table, so the query should not have been assigned to that workload.

Cheers

Steven

Teradata Employee

Steven,

 

Specifying global temp tables for workload or throttle/filter classification is not supported. I don’t understand all the reasons for this myself, but it is related to the fact that temp tables, triggers, volatile table objects don’t have access rights associated with them. So it is working as designed.

 

Thanks, -Carrie

Enthusiast

Thanks for the quick response Carrie

Cheers

Enthusiast

Hi Carrie,

 

I have not yet used a Utility classification in a Workload, just Utility sessions and i'm a little bit confused:

how multiload/fastload/TPT or even ARC sessions are classified in priority levels when defining Utility sessions and not any workload with a utility classification ?

Are they classified in a Timeshare WD-Default workload ?

 

Thanks,

 

Pierre

 

Teradata Employee

Pierre,

 

Utility session management only does one thing: It determines how many database sessions will be used for a utility job when it runs. It does not influence priority or determine which workload a utility will execute in.

 

The workload classification process involves TDWM software and is applied before a request or a utility job begins execution. What workload a utility job will run in depends on the classification criteria that has beeen provided to the existing workloads, and where the workloads are in the workload evaluation order.  

 

If your utility job does not match to any defined workloads, it will run in WD-Default which is in Timeshare Medium. DBQL will tell you the workload ID of where a utliity job executed. That way you can know for sure which workload was used and what it’s priority is.

 

We recommend that utility classification be used on workloads that support load utilities. That will prevent non-utility work from running there and will make sure the workload’s characteristics are optimal for utility jobs.

 

This blog posting on utility workloads might give you some ideas:

 

 

http://community.teradata.com/t5/Blog/Two-Levels-of-Concurrency-Control-for-Load-Utilities-in-Terada...

 

 

Thanks, -Carrie

 

Enthusiast

Hi Carrie,

That's so clear !

thanks,

 

Pierre