MonitorSQLSteps Function | Application Programming Reference | Vantage - 17.10 - MonitorSQLSteps - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Returns the step information (that is, a scaled-down version of the output of the EXPLAIN request modifier) of the current or running request for the specified host, session, and vproc.

Syntax

REPLACE FUNCTION SYSLIB.MonitorSQLSteps (
  HostIdIn SMALLINT,
  SessionNoIn INTEGER,
  RunVprocNo SMALLINT
) RETURNS TABLE (
    HostId SMALLINT,
    SessionNo INTEGER,
    DynamicPlan SMALLINT,
    PartialSteps SMALLINT 
    StepNum INTEGER,
    Confidence SMALLINT,
    EstRowCount FLOAT,
    ActRowCount FLOAT,
    EstRowCountSkew FLOAT,
    ActRowCountSkew FLOAT,
    EstRowCountSkewMatch FLOAT,
    ActRowCountSkewMatch FLOAT,
    EstElapsedTime FLOAT,
    ActElapsedTime FLOAT,
    SQLStep  VARCHAR(2048)CHARACTER SET UNICODE
  )
  ...
;

Syntax Elements

HostIdIn
Logical ID of a host (or client) with sessions logged on.
SessionNoIn
Session number of the SQL to monitor.
RunVprocNo
PE vproc number where the session runs.
HostId
Logical host ID associated with a PE or session. For a PE, HostId identifies one of the hosts or LANs associated with the described PE. For a session, the combination of a host ID and a session number uniquely identifies a user session on the system.
This value is NULL for AMPs. A value of zero represents the Supervisor window.
SessionNo
Number of the current session. Together with a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time.
DynamicPlan
Plan type:
  • 0 = Static plan
  • 1 = Dynamic plan
For more information on static and dynamic explanations of a request, see the EXPLAIN request modifier in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 or Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
PartialSteps
Possible values:
  • 0 = All steps are returned
  • 1 = Partial plan or no plan is returned

    If a partial plan is returned, this indicates the steps for the final plan fragment of the dynamic explanation of the request has not yet been generated.

    If no plan is returned, this indicates the request has been throttled and is in the delay queue.

A value 1 cannot occur for a static plan.
For more information on static and dynamic explanations of a request, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 or Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
StepNum
Unique number identifying the EXPLAIN step.
Confidence
Confidence level as determined by the optimizer:
  • 0 = None
  • 1= Foreign Key
  • 2 = Low
  • 3 = High
EstRowCount
Estimated row count generated from the Optimizer plan for this step.
For a PRPD plan, the EstRowCount field for the split step (that is, a RETRIEVE or JOIN step with “split into” appearing in the EXPLAIN when target spools are generated) is the estimated row counts for ALL split spools.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
ActRowCount
Actual row count returned from the AMP for this step.
For a PRPD plan, it includes rows from all split spools for a split step.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
EstRowCountSkew
Estimated row count for the skew split spool in PRPD, which contains the rows with skewed values of this spool.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
ActRowCountSkew
Actual number of rows for the skew split spool in PRPD.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
EstRowCountSkewMatch
Estimated row count for the skew match split spool in PRPD, which contains the rows with skewed values of the other relation to be joined with this relation.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
ActRowCountSkewMatch
Actual number of rows for the skew match split spool in PRPD.
For more information on PRPD, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 or Teradata Vantage™ - Database Administration, B035-1093.
EstElapsedTime
Estimated time for the query as generated from the Optimizer plan.
ActElapsedTime
Actual elapsed time calculated by the dispatcher.
SQLStep
Generated text for the step.

Usage Notes

This table function is only supported in Constant Mode.

If MONITOR SQL processing is not completed within the timeout interval, then an error is returned to the client application. When a MONITOR SQL request is timed out, the processing continues internally to its completion. If the client application submits a new MONITOR SQL request for the same timed out target session while the previous timed out one is still being processed, then an error is returned. The timeout interval can be set in the DBS Control field, PMPC_TimeoutSecs. The default timeout interval is 60 seconds. If the PMPC_TimeoutSecs field is set to zero, the MONITOR SQL timeout request will be disabled and no timeout will occur. For more information on the PMPC_TimeoutSecs field, see Teradata Vantage™ - Database Utilities, B035-1102.

The MonitorSQLSteps function provides similar functionality to the PMPC MONITOR SQL request. For information about this interface, see MONITOR SQL.

Example: Using MonitorSQLSteps

select StepNum (format '99') Num,
Confidence (format '9') C,
EstRowCount (format '-99999999') ERC,
ActRowCount (format '99999999') ARC,
EstRowCountSkew (format '-99999999') ERCS,
ActRowCountSkew (format '99999999') ARCS,
EstRowCountSkewMatch (format '-99999999') ERCSM,
ActRowCountSkewMatch (format '99999999') ARCSM,
EstElapsedTime (format '99999') EET,
ActElapsedTime (format '99999') AET,
SQLStep
from table (MonitorSQLSteps(1, 1164, 30719)) as t2;
 *** Query completed. 33 rows found. 11 columns returned.
 *** Total elapsed time was 3 seconds.
Num C       ERC      ARC      ERCS     ARCS     ERCSM    ARCSM   EET   AET SQLStep
--- - --------- -------- --------- -------- --------- -------- ----- ----- ------------------------------------------------------------
 01 0  00000000 00000004  00000000 00000000  00000000 00000000 00000 00000 First, lock [DBId=0x0407]. for exclusive.
 02 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 Next, we lock DBC.[TBId=0x0130] for write on a row hash.
 03 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We lock DBC.DBSpace for write on a row hash.
 04 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We lock DBC.Parents for write on a row hash.
 05 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We lock DBC.Owners for write on a row hash.
 06 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We lock DBC.AccessRights for write on a row hash.
 07 0  00000000 00000004  00000000 00000000  00000000 00000000 00000 00000 We lock DBC.[TBId=0x0130] for write, we lock DBC.DBSpace for write, we lock DBC.Parents for write, we lock DBC.Owners for wri
te, we lock DBC.DBase for write on a row hash, we lock DBC.DBase for write on a row hash, we lock DB
 08 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP ABORT test from DBC.DBase by way of the unique primary index. This step begins a parallel block of steps.
 08 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP ABORT test from DBC.[TBId=0x0138] by way of the unique primary index. This step is performed in parallel.
 08 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP ABORT test from DBC.DBase by way of the unique primary index. This step is performed in parallel.
 08 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP ABORT test from DBC.DBase by way of the unique primary index. This step is performed in parallel.
 08 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do an INSERT step into table DBC.DBase. This step is performed in parallel.
 08 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP UPDATE from DBC.DBase by way of the unique primary index. This step is performed in parallel.
 08 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP RETRIEVE step from DBC.Parents by way of the primary index into Spool 54, which is redistributed by hash c
ode to few AMPs. This step ends a parallel block of steps.
 09 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We do a MERGE into table DBC.Owners from Spool 54.
 10 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do an INSERT step into table DBC.Owners. This step begins a parallel block of steps.
 10 0  00000000 00000000  00000000 00000001  00000000 00000001 00000 00000 We do a Single-AMP RETRIEVE step from DBC.Parents by way of the primary index into Spool 55, which is redistributed by hash c
ode to few AMPs. This step ends a parallel block of steps.
 11 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We do a MERGE into table DBC.Parents from Spool 55.
 12 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do an INSERT step into table DBC.Parents. This step begins a parallel block of steps.
 12 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do an INSERT step into table DBC.Accounts. This step is performed in parallel.
 12 0  00000000 00000025  00000000 00000000  00000000 00000000 00000 00000 We do a Single-AMP RETRIEVE step from DBC.AccessRights accessing a single partition by way of the primary index into Spool 56
, which is redistributed by hash code to few AMPs. This step ends a parallel block of steps.
 13 0  00000000 00000021  00000000 00000000  00000000 00000000 00000 00000 We do a Single-AMP RETRIEVE step from DBC.AccessRights accessing a single partition by way of the primary index into Spool 56
, which is redistributed by hash code to few AMPs. This step begins a parallel block of steps.
 13 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We do an All-AMPs RETRIEVE step from DBC.AccessRights by way of an all-rows scan into Spool 57, which is redistributed by has
h code to all AMPs. This step ends a parallel block of steps.
 14 0  00000000 00000046  00000000 00000000  00000000 00000000 00000 00000 We do an All-AMPs JOIN step from DBC.Owners by way of an all-rows scan, which is joined to Spool 57. table Owners and Spool 5
7 are joined using a merge join . The result goes into Spool 56, which is redistributed by hash code
 15 0  00000000 00000046  00000000 00000000  00000000 00000000 00000 00000 We do a MERGE into table DBC.AccessRights from Spool 56. This step begins a parallel block of steps.
 15 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We do an INSERT step into table [TBId=0x0130]. This step ends a parallel block of steps.
 16 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We flush the DISKSPACE and AMPUSAGE caches.
 17 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We do an All-AMPs ABORT test from DBC.DBSpace by way of the unique primary index.
 18 0  00000000 00000004  00000000 00000000  00000000 00000000 00000 00000 We do an INSERT step into table DBC.DBSpace.
 19 0  00000000 00000004  00000000 00000000  00000000 00000000 00000 00000 We do an All-AMPs UPDATE from DBC.DBSpace by way of the unique primary index.
 20 0  00000000 00000000  00000000 00000000  00000000 00000000 00000 00000 We flush the DISKSPACE and AMPUSAGE caches.
 21 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00001 We Spoil the parser's dictionary cache for the database.
 22 0  00000000 00000001  00000000 00000001  00000000 00000001 00000 00000 We send out an END TRANSACTION step to all AMPs involved in processing the request.