16.20 - MonitorSQLSteps - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

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.

Definition

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
   )
             .
             .
             .
;

Input Parameters

Parameter Description
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.

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.

Result Rows

Column Name Description
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 Teradata 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 of 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.

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.