First-level information is captured in DBQLogTbl and DBQLSummaryTbl. For short-running, high-volume queries, you can request the THRESHOLD option in combination with the SQL, STEPINFO, or OBJECTS logging option to reduce collection overhead. You can further specify the units of elapsed time, CPU usage, or number of I/Os.
Use the data from DBQLogTbl and DBQLSummaryTbl to identify issues such as workloads that do not meet response time service-level agreements. If space is a major concern, you can use threshold logging, but having the detail can provide much more insight.
Best practices for DBQL logging dictate that you log at the Teradata Database system level with the SQL and OBJECTS logging options. If there are specific account strings, or user IDs that only do millions of tactical, subsecond requests, Teradata Database recommends that you use the THRESHOLD logging option with the STEPINFO logging option to log CPU usage.
When used selectively, detailed data can be invaluable. Analyze it to:
- Optimize queries or your query management or priority strategy; for example, compare:
- Results of different queries that target the same join index
- Elapsed times of the same query run on different days or hours
- Determine reasons for high consumption or poor performance by correlating DBQL data to other data collections with query characterization, including QCF, ResUsage, and DBC.AMPUsage
- Make efficient use of existing capacity and plan for future expansion by factoring in exponential growth of query demand
You can also input detailed data for baseline profiling, Target-Level Emulation, and Teradata client tools and utilities such as Teradata Visual Explain. Client tools aid in analysis and present the information in a graphic form that is easily manipulated and understood. For more information, see Teradata Visual Explain User Guide.