16.10 - View of the Query Step Information Log Table: QryLogSteps[V] - Teradata Database

Teradata Database Administration
Product
Teradata Database
Release Number
16.10
Published
April 2018
Language
English (United States)
Last Update
2018-04-26
dita:mapPath
qjg1509413559832.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
ujp1472240543947

The QryLogSteps[V] view of DBQLStepTbl is populated if you specify the WITH STEPINFO option. When the query completes, the system logs one row for each query step, including parallel steps.

QryLogSteps[V] Field Description
CollectTimestamp Time that the rows were written to the database. This field is also part of the multi-column NUPI (see also ProcID).

For more information on CollectTimeStamp, see Comparing CollectTimeStamp Value Between Tables.
CPUtime This is the CPU seconds for this step.
For requests using algorithm 1, the CPUTime shown in the first parallel step represents usage for the entire set of parallel steps.
CPUTimeNorm This is the normalized AMP CPU seconds for this step (used for co-existence systems).
For requests using algorithm 1, the CPUTimeNorm shown in the first parallel step represents usage for the entire set of parallel steps.
DestMapNo The map number for the output table in the step.
DestMapSlot Map slot of the destination table(s) in the step.
DispatchSeq This field is populated only for requests that use algorithm 3 of DBQL CPU/IO Collection.

Dispatch sequence, the order in which the step was handled by the dispatcher itself or dispatched to the AMPs, beginning with '1'.
DMLLoadID Indicates the load id value used by a modification step on a load-isolated table. The value is NULL for modification steps on non-load-isolated tables.
ElapsedTime The difference between the time when the step was sent to the AMP and when the step was returned from the AMP (to the nearest micro second).
EstCPUCost An estimate of the milliseconds of CPU time for the step, as determined by the Optimizer.
EstHRCost An estimate of other costs for the step. There is no comparable actual data for this estimate.
EstIOCost An estimate of service time in milliseconds for 
I/O for this step. There is no comparable actual data for this estimate.
EstNetCost An estimate of the BYNET service time in milliseconds for the step. There is no comparable actual data for this estimate.
EstProcTime The estimated processing time as determined by the Optimizer.
This time is reported in seconds.
EstRowCount The estimated row count for this step.

For a Partial Redistribution and Partial Duplication (PRPD) plan split step, EstRowCount is the estimated row counts for all split spools.

A split step is a RETRIEVE or JOIN step with “split into” appearing in the EXPLAIN when target spools are generated.
EstRowCountSkew In PRPD, the estimated row count for the skew split spool (which contains the rows with skewed values of the relation in this step).
EstRowCountSkewMatch In PRPD, the estimated row count for the skew match split spool (which contains the rows with skewed values of the other relation to be joined with the relation in this step).
EstRowCountSkew In PRPD, the estimated row count for the skew split spool (which contains the rows with skewed values of the relation in this step).
EstRowCountSkewMatch In PRPD, the estimated row count for the skew match split spool (which contains the rows with skewed values of the other relation to be joined with the relation in this step).
FragmentNum Indicates the plan fragment to which the current step belongs. For a static plan, this is set to NULL.
IOcount This is the number of logical I/Os used by this step.
For requests using algorithm 1, the IOCount shown in the first parallel step represents the entire set of parallel steps.
IOKB Logical I/Os in KB per step.
LockDelay Maximum wait time to get a lock on an object in centiseconds. The maximum value is 32,767 centiseconds.
LSN The Logon Sequence Number used by load utility.
MaxAmpCPUTime CPU seconds of the highest CPU utilized AMP in the step.
MaxAmpCPUTimeNorm Normalized maximum CPU seconds for an AMP.
Normalized columns, such as MaxAmpCPUTimNorm, are for co-existence systems only.
MaxAmpIO The highest logical I/O count on the AMP with the highest logical 
I/O usage for the step.
MaxAMPPerm The maximum permanent space added or deleted by an AMP in the step.
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.
MaxCPUAmpNumber The number of the AMP with the highest CPU usage for the step.
MaxCPUAmpNumberNorm The number of the AMP with the maximum normalized CPU seconds for co-existence systems.
MaxIOAmpNumber The number of the AMP with the highest logical I/O usage for the step.
MaxNumMapAMPs The number of AMPs in the largest map used by the request.
MaxOneMBRowSize The number of AMPs in the largest map used by the request.
MaxPermAMPNumber The AMP number that added or deleted the maximum permanent space in the step.
MaxSpaceDelayAMPNumber The AMP number that had the maximum wait on space allocation.
MaxSpoolAmpNumber The actual size in bytes of the largest 1 MB row in the set of rows returned for the query.
MinAmpCPUTime CPU seconds of the lowest CPU utilized AMP in the step.
MinAmpCPUTimeNorm Normalized minimum CPU seconds for an AMP.
Normalized columns, such as MinAmpCPUTimeNorm, are for co-existence systems only.
MinAmpIO Logical I/O count of the lowest logical I/O utilized AMP in the query.
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.
MinNumMapAMPs The number of AMPs in the smallest contiguous map used by this request.
NumCombinedPartitions If there is static partition elimination for the step or if a noPI table or join index has column partitions, the number of combined partitions accessed (not eliminated). Otherwise, the column is null.
NumContexts For column-partitioned tables or join indexes: the number of contexts allocated to access partitions at the same time as a source or target relation that has partitioning. Otherwise, the column is null.
NumCPReferences For column-partitioned tables or join indexes: the number of column partitions referenced in a column-partitioned source or target. Otherwise, the column is null.
NumOfActiveAMPs The number of AMPs involved with the step.
OneMBRowCount The number of 1 MB rows in the set of rows returned by the step.
PermUsage Perm space in bytes added or deleted by the step.
PersistentSpool Persistent portion of the SpoolUsage column.
PhysIO Physical I/O count per step.
PhysIOKB Physical I/Os in KB per step.
ProcID Unique processor ID of the Dispatcher and part of the multi-column NUPI (see also CollectTimeStamp).
QueryID Internally generated identifier of the query.
RowCount For:
  • The MRM (Merge Row Multiple) or EXE (MultiLoad) steps, RowCount is the number of rows inserted.
  • All other steps, RowCount is the actual number of rows the step returns (indicating activity count). For a PRPD plan split step, RowCount includes the rows from all split spools.
    If “split into” appears in the EXPLAIN of a RETRIEVE or JOIN step, Teradata Database is using PRPD.

If the row count for a step is 18,446,744,073,709,551,615, the number is logged in the RowCount column as 1.8446744073709552e+19.

The value formatted as a decimal value is 18,446,744,073,709,551,616 due to float arithmetic.
RowCount2 For:
  • The MRM (Merge Row Multiple) or EXE (MultiLoad) steps, RowCount2 is the number of updated rows.
  • An LFI (FastLoad) step, RowCount2 is the number of rows loaded.
  • A PRPD plan split step, RowCount2 is the actual number of rows in the skew split spool.
RowCount3 For:
  • The EXE (MultiLoad) step, RowCount3 is the number of rows deleted.
    For the number of rows inserted by the EXE step, see RowCount. For the number of rows updated by the EXE step, see RowCount2.
  • A PRPD plan split step, RowCount3 is the actual number of rows in the skew match split spool.
RowsWComprColumns Number of rows with compressed columns.
SourceMapNo The map number for the source table in the step.
SpaceDelay The maximum wait time in centiseconds to get space from the global space accounting system.
SpoolUsage Spool usage varies during a step. If you are using DBQL CPU/IO Collection algorithm 3, SpoolUsage is the peak spool usage in bytes for this step. For other algorithms, SpoolUsage is the amount of spool usage for the AMP at the end of the step.
SSRReceiverCount The number of Single-Sender Redistribution (SSR) receiver AMPs that the step uses to redistribute rows. If the step does not use SSR for row redistribution, the value is zero.
StatementNum Statement number with which the step is associated. If associated with multiple statements (for example, array insert), this is the highest statement number associated with this step.
StepInstance This field is populated only for requests that use algorithm 3 of DBQL CPU/IO Collection.

Step instance number for steps that are repeated. If a step is dispatched 3 times, there will be one summarized step logged for the 3 instances of the same step number and the step instance field will be 3, indicating that 3 steps instances are represented. This field will be 1 for non-repeated steps.
StepLev1Num Step number. If this row is for a parallel step, the number is repeated for as many parallel steps as were used for this step (for examples, see StepLev2Num).
StepLev2Num If this row is for a parallel step, this is the second-level number of the step that spawned it.

For example, if the value of StepLevl1Num is 4 and this row logs the first parallel step for step 4, this value is 1 (and would read 04 01). If this row logs the second row generated for step 4, this value is 2 (and would read 04 02), and so forth.

If this row is not a parallel-step row, this value is 0.
StepName Abbreviation of the internal name used to identify this step (for example, DEL for a DELETE step).

For more information on the value of “RESP,” see the DBQLLogLastResp field in Utilities.
StepObjectInfo Contains binary-encoded information about tables, spools, and join indexes accessed by the step.
StepStartTime Timestamp when the step was sent to the AMP to the nearest micro second.
StepStatus This field is populated only for requests that use algorithm 3 of DBQL CPU/IO Collection.

The step status, indicating dispatcher and AMP step disposition. Values include:

  • Dispatched : Step was dispatched to the AMP.
  • AMPexec : Step was executed in the AMP.
  • AMPokay : Step completed okay in the AMP.
  • AMPerror : Step failed in the AMP and returned an error.
  • AMPReqAB : Step aborted in the AMP for request abort.
  • AMPTxnAB : Step aborted in the AMP for transaction abort.
  • Completed : Step completed by the dispatcher.
  • Skipped : Conditional step was skipped by the dispatcher.
StepStopTime Timestamp when the step returned from the AMP to the nearest micro second.
StepWD The workload definition in effect for the step.
SysDefNumMapAMPs The number of AMPs in the system-default map.
TriggerKind Trigger kind. Space-separated string of abbreviated values for the bit flags.

TriggerKind bit flags:

  • TrigNone: 0
  • GetActivityCnt: 1
  • FetchQualifyingRows: 2
  • BuildTrigUsingRow: 4
  • BeginLoop: 8
  • EndLoop: 16
  • IdColWithRowTrig: 32
  • SkipQualifyingRows: 64
  • Zero : Step was not part of a trigger
  • Nonzero : Step was part of a trigger
TriggerNestLevel Trigger nesting level.

The TriggerNestLevel can range from 0-255.
Usediota I/O tokens used by the step.
UtilityTableId The table ID for the utility.

This field is always 0.
VHLogicalIO VERYHOT logical I/O count per step.
VHLogicalIOKB VERYHOT logical I/Os in KB per step.
VHPhysIO VERYHOT physical I/O count per step (VERYHOT cylinder index and data block I/Os that were handled by physical disk I/Os.)
VHPhysIOKB Physical I/Os in KB per step.