Recommended DBQL Logging Scenarios
User Type 1: Short Subsecond Known Work (Tactical Work)
The numbers 1,3,5 are clock seconds not CPU seconds
For this workload, high-speed performance and minimal response time are the primary objectives. Typically, this workload tends to be very predictable in nature with queries typically designed to be single AMP retrievals.
Logging of this workload at the request level may be unnecessary for two reasons:
The objective is to capture only summarized information about these SQL requests. Since the expectation for this workload type is that the work is predictable, repeatable and does not vary much, summary logging is sufficient. If however, there could be unauthorized use of this ID, or on occasion, a longer running query is run, threshold logging could be used.
User Type 2: Mostly Long Running Work
If there are 10s of thousands of subsecond work, additional overhead will be incurred
Teradata recommends that a high degree of detailed data be captured for analysis of this workload category. The data generated from this DBQL logging option generates the critical detailed information needed for effective performance management and tuning.
The logging captures the entire SQL text for each request along with the objects used in processing the request. The SQL text and Object data is critical for performing query access path analysis. The DBQL detail data provides not only CPU and IO data, but also the data to calculate whether a query is skewed, does a large scan, or has the characteristics of a large product join, the keys to high impact performance tuning.
User Type 3: Mostly Short Subsecond Requests with Occasional Long Running or Unknown Work
With threshold logging, DBQL cannot log to separate Explain and XML tables, even for those queries taking longer than the specified criteria. SQL, STEPINFO, and OBJECTS can be logged during threshold logging, even for those queries taking longer than the specified clock seconds.
This logging scenario captures data at a more detailed level for unknown or long running requests, while still logging the bulk of the work, the subsecond requests, at a summary level.