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 database system. This value is assigned by the host (or client) at logon time. |
DynamicPlan | Plan type:
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:
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:
|
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.