15.10 - QryLogStepsV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

ProcID

DECIMAL(5,0) 

NOT NULL

-(5)9 (explicit)

DBQLStepTbl.ProcID (NUPI)

CollectTimestamp

TIMESTAMP(6) 

NOT NULL

YYYY-MM-DDBHH:MI:SS (explicit)

DBQLStepTbl.CollectTimeStamp (NUPI)

QueryID

DECIMAL(18,0) 

NOT NULL

--Z(17)9

DBQLStepTbl.QueryID

StepLev1Num

SMALLINT 

NOT NULL

---,--9 (explicit)

DBQLStepTbl.StepLev1Num

StepLev2Num

SMALLINT

---,--9 (explicit)

DBQLStepTbl.StepLev2Num

StepName

CHAR(6) LATIN

NOT CASESPECIFIC

NOT NULL

X(6)

DBQLStepTbl.StepName

StepStartTime

TIMESTAMP(6)

YYYY-MM-DDBHH:MI:SS.S(F)Z (explicit)

DBQLStepTbl.StepStartTime

StepStopTime

TIMESTAMP(6)

YYYY-MM-DDBHH:MI:SS.S(F)Z (explicit)

DBQLStepTbl.StepStopTime

ElapsedTime

INTERVAL HOUR(4) TO SECOND(6)

-h(4):mm:ss.s(6)

DBQLStepTbl.StepStartTime,

DBQLStepTbl.StepStopTime

EstProcTime

FLOAT

----,---,---,---,--9.999 (explicit)

DBQLStepTbl.EstProcTime

EstCPUCost

FLOAT

ZZ,ZZZ,ZZ9.999 (explicit)

DBQLStepTbl.EstCPUCost

CPUtime

FLOAT

ZZ,ZZZ,ZZ9.999 (explicit)

DBQLStepTbl.CPUtime

IOcount

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.IOcount

EstRowCount

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.EstRowCount

EstRowCountSkew 

FLOAT

----,---,---,---,--9

(explicit)

DBQLStepTbl.EstRowCountSkew

EstRowCountSkewMatch 

FLOAT

----,---,---,---,--9

(explicit)

DBQLStepTbl.EstRowCountSkew
Match

RowCount

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.RowCount

RowCount2

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.RowCount2

RowCount3

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.RowCount3

NumOfActiveAMPs

INTEGER

--,---,---,--9 (explicit)

DBQLStepTbl.NumOfActiveAMPs

MaxAmpCPUTime

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.MaxAmpCPUTime

MaxCPUAmpNumber

SMALLINT

---,--9 (explicit)

DBQLStepTbl.MaxCPUAmpNumber

MinAmpCPUTime

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.MinAmpCPUTime

MaxAmpIO

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.MaxAmpIO

MaxIOAmpNumber

SMALLINT

---,--9 (explicit)

DBQLStepTbl.MaxIOAmpNumber

MinAmpIO

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.MinAmpIO

SpoolUsage

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.SpoolUsage

MaxAMPSpool

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.MaxAmpSpool

MaxSpoolAmpNumber

SMALLINT

---,--9 (explicit)

DBQLStepTbl.MaxSpoolAmpNumber

MinAMPSpool

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.MinAmpSpool

StepWD

INTEGER

--,---,---,--9 (explicit)

DBQLStepTbl.StepWD

LSN

INTEGER

--,---,---,--9 (explicit)

DBQLStepTbl.LSN

UtilityTableId

BYTE(4)

X(8)

DBQLStepTbl.UtilityTableId

RowsWComprColumns

FLOAT

----,---,---,---,--9 (explicit)

DBQLStepTbl.RowsWComprColumns

EstIOCost

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.EstIOCost

EstNetCost

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.EstNetCost

EstHRCost

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.EstHRCost

CPUTimeNorm

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.CPUTimeNorm

MaxAmpCPUTimeNorm

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.MaxAmpCPUTime
Norm

MaxCPUAmpNumberNorm

SMALLINT

---,--9 (explicit)

DBQLStepTbl.MaxCPUAmpNumber
Norm

MinAmpCPUTimeNorm

FLOAT

ZZ,ZZZ,ZZ9.999

DBQLStepTbl.MinAmpCPUTime
Norm

NumCombinedPartitions

BIGINT

--,---,---,---,---,---,--9 (explicit)

DBQLStepTbl.NumCombined
Partitions

NumContexts

INTEGER

--,---,---,--9 (explicit)

DBQLStepTbl.NumContexts

NumCPReferences

INTEGER

--,---,---,--9 (explicit)

DBQLStepTbl.NumCPReferences

StepInstance

INTEGER

--,---,---,--9

DBQLStepTbl.StepInstance

StepStatus

CHAR(10) LATIN

X(10)

DBQLStepTbl.StepStatus

DispatchSeq

INTEGER

--,---,---,--9

DBQLStepTbl.DispatchSeq

StatementNum

INTEGER

--,---,---,--9

DBQLStepTbl.StatementNum

TriggerNestLevel

SMALLINT

--Z(4)9

DBQLStepTbl.TriggerNestLeve

TriggerKind

VARCHAR(128) LATIN

NOT CASESPECIFIC

X(128)

DBQLStepTbl.TriggerKind

FragmentNum

INTEGER

--,---,---,--9

DBQLStepTbl.FragmentNum

IOKB 

FLOAT 

----,---,---,---,--9.999

DBQLStepTbl.IOKB 

VHLogicalIO

FLOAT

----,---,---,---,--9.999

DBQLStepTbl.VHLogicalIO

VHPhysIO 

FLOAT

----,---,---,---,--9.999

DBQLStepTbl.VHPhysIO

VHLogicalIOKB 

FLOAT 

----,---,---,---,--9.999

DBQLStepTbl.HLogicalIOKB

VHPhysIOKB 

FLOAT

----,---,---,---,--9.999

DBQLStepTbl.VHPhysIOKB 

PhysIO

FLOAT

----,---,---,---,--9.999

DBQL.StepTbl.VHPhysIO

PhysIOKB 

FLOAT 

----,---,---,---,--9.999

DBQLStepTbl.PhysIOKB 

LockDelay 

FLOAT

----,---,---,---,--9.999

DBQLStepTbl.LockDelay

SSRReceiverCount

INTEGER

--,---,---,--9

DBQLStepTbl.SSRReceiverCount

DMLLoadId

INTEGER 

--,---,---,--9

DBQLStepTbl.DMLLoadId

ServerByteCount

Note: This column is also documented in the QueryGrid manuals.

FLOAT

----,---,---,---,--9

DBQLStepTbl.ServerByteCount

PersistentSpool

FLOAT

----,---,---,---,--9

DBQLStepTbl.ExtraField19

If step information of the query is requested, a step table is populated with a row for each step.

CPUTimeNorm

The CPUTimeNorm calculation is made for each PE in the system. It is used for systems with processors where each node may have CPUs with different scaling factors.

FragmentNum

This column is NULL for static plans only.

LockDelay

The maximum wait time to get a lock on an object in centiseconds. The maximum value is 32,767 centiseconds.

NumContexts

This column is set to the number of contexts allocated to simultaneously access partitions in a table that has partitioning. If no contexts are allocated, the value of the NumContexts column is NULL.

NumCPReferences

This column is set to the number of column partitions referenced in a column partitioned table. If there are no column partitions, the value of NumCPReferences is NULL.

RowCount

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.

Note: The value formatted as a decimal value is 18,446,744,073,709,551,616 due to float arithmetic.

ServerByteCount

The ServerByteCount column is the total number of bytes sent to or received from a foreign server for each step.

Possible Values for NumCombinedPartitions

The value of the NumCombinedPartitions column is non-zero only if:

  • There is static partition elimination for the step (for a query submitted to a Teradata Database 14.0 or later).
  • A source table has column partitions.
  • Otherwise, the NumCombinedPartitions column is NULL.

    Possible Values for TriggerKind

  • BegLoop 
  • FetchQualRows
  • BldUsingRow
  • GetActvCnt
  • IdColWithRowTrig
  • SkipQualRows
  • EndLoop
  • The following SELECT statement retrieves all rows that match the query ID 307190925762023013 from the QryLogStepsV view:

    select * from QryLogStepsV where queryid =  307190925762023013;

    The query returns the following result:

                   ProcID   30719
         CollectTimeStamp  2014-03-13 07:21:39
                  QueryID    307190925762023013
              StepLev1Num        1
              StepLev2Num        0
                 StepName  MLK
            StepStartTime  2014-03-13 07:21:35.090000
             StepStopTime  2014-03-13 07:21:35.090000
              ElapsedTime      0:00:00.000000
              EstProcTime                     0.000
               EstCPUCost           0.000
                  CPUTime           0.008
                  IOcount                     0
              EstRowCount                     0
          EstRowCountSkew                     0
     EstRowCountSkewMatch                     0
                 RowCount                     4
                RowCount2                     0
                RowCount3                     ?
          NumOfActiveAMPs               4
            MaxAmpCPUTime           0.004
          MaxCPUAmpNumber        2
            MinAmpCPUTime           0.000
                 MaxAmpIO                     0
           MaxIOAmpNumber        ?
                 MinAmpIO                     0
               SpoolUsage                     0
              MaxAMPSpool                     0
        MaxSpoolAmpNumber        ?
              MinAMPSpool                     0
                   StepWD               ?
                      LSN               ?
           UtilityTableID  ?
        RowsWComprColumns                     0
                EstIOCost           0.000
               EstNetCost           0.000
                EstHRCost           0.000
              CPUtimeNorm           0.538
        MaxAmpCPUTimeNorm           0.269
      MaxCPUAmpNumberNorm        2
        MinAmpCPUTimeNorm           0.000
    NumCombinedPartitions                           ?
              NumContexts               ?
          NumCPReferences               ?
             StepInstance               1
               StepStatus  AMPokay
              DispatchSeq               1
             StatementNum               1
         TriggerNestLevel        0
              TriggerKind  ?
              FragmentNum               ?
                     IOKB                     0.000
              VHLogicalIO                     0.000
                 VHPhysIO                     0.000
            VHLogicalIOKB                     0.000
               VHPhysIOKB                     0.000
                   PhysIO                     0.000
                 PhysIOKB                     0.000
                LockDelay                         ?
         SSRReceiverCount               ?
                DMLLoadID               ?
          ServerByteCount                     ?
          PersistentSpool                     0

    The following SELECT statement gives the user name and the elapsed time of the steps whose queries have transferred more than 10 MB of data.

    SELECT lv.username, sv.elapsedtime FROM DBC.QryLogStepsV AS sv,
    DBC.QryLogV AS lv WHERE ServerByteCount / (1024*1024) GT 10 AND
    sv.queryid = lv.queryid;

    The query returns the following result:

    username TOM
    ElapsedTime 0:10:22.220000
    username JOHN
    ElapsedTime 0:21:32.510000