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=0Possible 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. |