DBQL and Utility Resource Usage Reporting

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

If you are someone who uses Database Query Log (DBQL) to keep on top of how much resource your users and applications are consuming, you are probably aware that there are different DBQL tables that collect information related to usage. Starting in Teradata Database 15.0, if you are tracking usage for utilities, that detail is carried in two places:  The DBQLogTbl and the DBQLUtilityTbl.

 

Which should you use?   Should you combine usage from both tables?  Or only consider the usage in one of the two tables?

 

What DBQLogTbl Tables Reports

 

Both DBQLogTbl and DBQLUtilityTbl report CPU and I/O usage for utility jobs that run. DBQLogTbl is one of the original DBQL tables and has been around for a long time.  It contains one row per SQL request that is contained within a utility job. Each of those requests reports its resource usage independently. For example, for a single MultiLoad (similar to TPT Update) job, you could see rows in DBQLogfTbl  such as:

 

SET QUERY_BAND='UTILITYNAME=MULTLOAD;'…

EXEC MLOAD User1.ml_table_1;

USING Ckpt(VARBYTE(1024)) INS . . .;

 

As is true with all DBQLogTbl rows, there is CPU usage and I/O usage detail provided in each DBQLogTbl row. So one way to calculate total resource usage for load utility jobs is to sum up the metrics for all of these statements that make up a single utility job.  

 

What DBQUtilityTbl Reports

Each row in the DBQLUtilityTbl reports on one utility job. In that single row it reports CPU and I/O metrics for each of five phases of the utility job are reported. The table below describes applicable phases and request types in each phase for different utility protocols.  

 

Utility Protocols

Phase 0 Name / Request Type

Phase 1 Name / Request Type

Phase 2 Name/ Request Type

Phase 3 Name/ Request Type

Phase 4 Name/ Request Type

FastLoad (TPT Load) / Traditional MultiLoad (TPT Update)

Setup /

Normal SQL

Acquisition /

Special protocol

Application /

Normal SQL

Cleanup /

Normal SQL

N/A

MLOADX

Setup /

Normal SQL

Acquisition /

Normal SQL*

Application /

Normal SQL

Cleanup /

Normal SQL

N/A

FastExport Spool Mode

Setup /

Normal SQL

Select /

Normal SQL

Export /

Special protocol

Cleanup /

Normal SQL

N/A

FastExport No Spool Mode

Setup /

Normal SQL

Select /

Special protocol

Export /

Special protocol

Cleanup /

Normal SQL

N/A

DSA

N/A

Dictionary /

Both

Data /

Special protocol

Build /

Special protocol

Postscript /

Normal SQL*

 

*Although MLOADX’s Acquisition phase and DSA’s Postscript phase use normal SQL requests, these requests are not executed by the control SQL sessions so special handling is needed to forward resource usages of these requests to the control SQL session.

 

If you sum up the CPU usage from all the rows in the DBQLogtbl for a single utility job, however, it may be less than the CPU usage reported in all four phases reported in the DBQLUtilityTbl for that same utility. So the two tables are not accumulating the same level of resource.

 

Accumulation difference between DBQLogTbl and DBQLUtilityTbl

DBQLUtilityTbl is the more accurate of the two tables when it comes to reporting CPU and I/O usage for a utility job. In the DBQLogTbl,  Phase 1 requests within the DQLogTbl do not account for all the resource usage involved in special protocol when loading the data from the client into the database.

 

Phase 1 usage stats are undercounted in the DBQLogTbl because of how the utilities are architected. Data loading for both TPT Update (MultiLoad) and TPT Load (FastLoad) are designed to happen very quickly, and consequently these activities bypass the parser and the  dispatcher. In addition, the AMP worker tasks within Phase 1 using CPU and I/O to load data do not report all of their usage back to the dispatcher, as is conventionally done.

 

Consider what happens inside of Phase 1 during a MultiLoad job, as an example. The INSERT statement spawns two AMP worker (AWTs)  to support Phase 1 processing.  However, these AWTs become orphans as soon as they start doing work. MultiLoad starts the step which issues them  then ends the step leaving the AWTs active on their own.  With no active step to return to where resource usage can be reported, that usage slips through the cracks.

 

Consequently, most of the CPU and I/O involved in reading data from the client and dumping it into the database will not be available to DQLogTbl.

 

The DBQLUtilityTbl, on the other hand, was designed to capture all resource usage, including CPU and I/O that is missing from DBQLogTbl entries. For that reason, it is recommended that DBQLUtilityTbl be used when accumulating resource usage for utility jobs, rather than DBQLogTbl.

 

Results of a Comparison Test

Here a comparison of CPU usage from a test execution of a short MultiLoad utility job. In the case of DBQLogTbl, the phase totals were calculated by summing up each DBQLogTbl row that belonged in each phase of the specific MultiLoad job.

 

 DBQLogTblDBQLUtilityTbl
Phase 0 Total    0.16       0.16
Phase 1 Total    0.36       2.04
Phase 2 Total    0.27       0.27
Phase 3 Total    0.42       0.42

 

 All of the resource usage within the four phases match between the DBQLogTbl and the DBQLUtilityTbl, except for Phase 1. 

 

The DBQLogTbl carries more extensive detail that does the DBQLUtility table. Even though you plan to get resource usage based on the utility log table you might require some of the data that is in the DBQLogTbl as well.  In that situation SQL requests for a particular job instance can be obtained by  joining the DBC.DBQLUtilityTbl and DBC.DBQLogTbl table on the LSN, host ID, and session ID column.

 

The example below retrieves rows from DBC.DBQLogTbl for the MultiLoad job submitted by USER1 ending at 14:12:51.55 on 4/10/2013.

 

SELECT L.*

FROM DBC.DBQLogTbl L,

     DBC.DBQLUtilityTbl U

WHERE U.LSN = L.LSN

  AND U.LogicalHostID = L.LogicalHostID

  AND U.SessionID = L.SessionID

  AND U.UtilityName = 'MULTLOAD'

  AND U.UserName = 'USER1'

  AND U.JobEndTime = TIMESTAMP '2013-04-10 14:12:51.55';

 

Conclusion

It is recommended that resource usage statistics concerning load utility be taken from the DBQLUtilityTbl rather than from DBQLogTbl. A more accurate reporting of this usage will be available in the utility logging table.

 

It is even more important when using DSA to rely on the DBQLUtilityTbl metrics. Because of the numerous special protocols used by DSA, the DBQLogTbl is highly inaccurate in is reporting of metrics such as CPU and I/O.  However, the DBQLUtilityTbl captures everything that is happening within a DSA job.

1 Comment
Senior Scout

Thank you so much for giving the valuable information