17.00 - Usage Notes - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

Database Query Logging (DBQL) tables track query behavior. Each row in QryLogV contains default DBQL information for a query.

The Default Row

The fields of the default row provide general query information that is usually adequate for investigating a query that is interfering with performance. When no options are specified, a default row includes:
  • User ID and user name under which the session being logged was initiated
  • Unique ID for the process, session, and host (client) connection
  • Account string, expanded as appropriate, that was current when the query completed
  • First 200 characters of the query SQL statement
  • CPU and I/O statistics
  • Default database name that was current when the query completed

FlexThrottle

Flex Throttles automatically release a throttle to allow full utilization of the system and release work from the TDWM delay queue to utilize these resources. This column is set to 'T' if the request was released due to the Flex Throttle feature. Otherwise, the column is NULL.

InternalRequestNum

The internal request number.

For commands other than those within a stored procedure the internal request number and the number in the RequestNum field will be the same. For stored procedures invoked within a session, the internal request number increments by 1 for every request made by the stored procedure. This means that RequestNum will be the request number of the CALL and the value in InternalRequestNum will continue to increment for all other queries issued by the session.

LogonSource

Teradata recommends using alternative columns instead of the LogonSource column, if available. For information about the recommended columns for LogonSource, see "LogonSource Column Fields and Examples."

MaxAMPSpool

If you are using DBQL CPU/IO Collection algorithm 3, MaxAMPSpool is the largest peak spool usage of any AMP for this step. For other algorithms, MaxAMPSpool is the highest spool usage on any AMP at the end of the step.

MinAMPSpool

If you are using DBQL CPU/IO Collection algorithm 3, MinAMPSpool is the smallest peak spool usage of any AMP for this step. For other algorithms, MinAMPSpool is the lowest spool usage of any AMP at the end of the step.

SessionTemporalQualifier

When a DML or SELECT request refers to a temporal table but omits a temporal qualifier, the system uses the value of the session temporal qualifier. This is the session temporal qualifier in effect when the query is logged.

For example, this column uses the ANSIQUALIFIER value if the session temporal qualifier is set to ANSIQUALIFIER for ANSI temporal tables.

For more information about the session temporal qualifiers, see Teradata Vantage™ Temporal Table Support , B035-1182 and Teradata Vantage™ ANSI Temporal Table Support , B035-1186 .

Possible Values for CacheFlag

This column is blank if the query is not found in the request cache. Possible values include:

Value Description
T Query execution plan is generic and found in the request cache.
S Query plan is specific (either a static or dynamic plan).
G Query plan is generic and not in the request cache but is a candidate for caching in subsequent runs of the same query.
A

If a Specific Always decision is taken. That is, for each query USING values are peeked during request parsing. The optimizer may choose a dynamic plan with results feedback.

D Generic plan from the request cache is used for the request execution.
P Request is parsed to generate the plan by peeking the literal values.
N Request is parsed to generate the plan without peeking the literal values (generic plan).
I PRCE infrastructure has decided specific plan is always better and this request is ineligible for Dynamic Parametrization of Literals feature.

For more information on specific plans and generic plans, see “Peeking at Parameterized Values in the Data Parcel” in Teradata Vantage™ SQL Request and Transaction Processing, B035-1142.

Possible Values for CPUDecayLevel

Value Description
0 Indicates that the request is running at the default priority.
1 Indicates the priority level is reduced and the resource allotment is halved.
2 Indicates that the priority level is reduced again, the resource allotment is halved again, and it will not be reduced more.

Possible Values for DataCollectAlg

Value Description
1 Use the classic algorithm with step adjustments.
2 Use AMP algorithm 2 (diagnostic only).
3 Use AMP algorithm 3 (includes information on aborted and parallel steps).

Possible Values for IODecayLevel

Value Description
0 Indicates that the request is running at the default priority.
1 Indicates the priority level is reduced and the resource allotment is halved.
2 Indicates that the priority level is reduced again, the resource allotment is halved again, and it will not be reduced more.

Possible Values for LockLevel

Locklevel is the highest lock level for locks associated with this request. For explicit transactions, LockLevel is the lock level for the transaction. LockLevel does not include row hash locks. Possible LockLevel values:

  • NOLOCK
  • ACCESS
  • READ
  • WRITE
  • EXCLUSIVE
  • UNKNOWN

Possible Values for QueryBand

Value Description
T Transaction query band pairs.
S Session query band pairs.
P Profile query band pairs.

Possible Values for QueryRedriven

Value Description
E The request was reparsed due to an internal Parser error.
M The request was reparsed due to an internal Parser memory limit.
R The request was redriven due to a database restart.
N The request was not redriven or reparsed.

Possible Values for RedriveKind

Value Description
' ' Not participating in redrive.
MN Memory-based protection, no fallback spool.
MF Memory-based protection, with fallback spool.
DN Dictionary-based protection, no fallback spool.
DF Dictionary-based protection, with fallback spool.

Possible Values for RequestMode

Value Description
P Prep: Prepare mode (P).
E Exec: Execute mode (E).
B Both: Prepare and execute mode (B).
S PrepS: Prepare, supporting parameterized SQL (S).

Possible Values for StatementGroup

If there is a DDL statement in a request, StatementGroup reports which type:

  • DDL ALTER
  • DDL CREATE
  • DDL GRANT

If the statement has only one DML statement or multiple DML statements that are all of the same type, StatementGroup will indicate the type. For example if there are three DELETE statements in a request, StatementGroup will report:

DML DELETE

Similarly, for requests with individual or multiple INSERT, INSERT/ SELECT, UPDATE, MERGE INTO, or SELECT statements, StatementGroup will report:

  • DML INSERT
  • DML INSERT/SELECT
  • DML UPDATE
  • DML MERGEINTO
  • SELECT

In a multistatement request with different types of DML statements, you will see a list showing the number of statements of each type in the request. For example, a request with one insert and two update statements will appear as:

DML Del=0 Ins=1 InsSel=0 Upd=2 Sel=0 MergeInto=0

Possible Values for ThrottleBypassed

ThrottleBypassed indicates whether an active request is active solely due to the ThrottleBypass ruleset attribute. This attribute overrides the throttle limits if the session owning the request has an object lock higher than the Access level. Possible values include:

Value Description
0 ThrottleBypass ruleset attribute is not set.
1 ThrottleBypass ruleset attribute is set.

Possible Values for TTGranularity

Value Description
LOGICALROW Row is timestamped with the time the row is processed by the AMP.
REQUEST Row is timestamped with the time the request is submitted.
TRANSACTION Row is timestamped with the time when the first non-locking reference is made to a temporal table, or when the built-in function TEMPORAL_TIMESTAMP is first accessed during the transaction.

Possible Values for UtilityInfoAvailable

UtilityInfoAvailable indicates whether an SQL request has utility information available in DBQLUtilityTbl. Possible values include:

Value Description
Y SQL request has utility information (for example, from DSA or the control SQL session of a load or export utility) logged in the DBQLUtilityTbl table.
N SQL request does not have utility information logged in the DBQLUtilityTbl table.