Function of QuerySteps
Each row in the table lists the attributes of any step executed by the system corresponding to the request. If the step has more than one attribute, then multiple rows are stored for the step, and the row set is linked by means of its common StepID and QueryID values. In this case, the individual rows are distinguished by their row type codes.
QuerySteps Table Definition
The following CREATE TABLE request defines the QuerySteps table:
CREATE TABLE QuerySteps ( StepID INTEGER NOT NULL, QueryID INTEGER NOT NULL, StepNum INTEGER, ParallelStepNum INTEGER DEFAULT 0, StepText VARCHAR(32000) CHARACTER SET LATIN NOT CASESPECIFIC, RowType CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL, StepKind CHARACTER(2) CHARACTER SET LATIN NOT CASESPECIFIC, ParallelKind CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC, AMPUsage CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC, TriggerType CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC, EstCPUCost FLOAT, EstIOCost FLOAT, EstNetworkCost FLOAT, EstHRCost FLOAT Cost FLOAT, MaxCost FLOAT, SourceRelation1 INTEGER, SourceRelation2 INTEGER, TargetRelation1 INTEGER, TargetRelation2 INTEGER, StepAttributeType CHARACTER(10) CHARACTER SET LATIN NOT CASESPECIFIC, StepAttributeValue VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC, LockType CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC, RowHashFlag CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC, NoWaitFlag CHARACTER(1) CHARACTER SET LATIN NOT CASESPECIFIC Cardinality DECIMAL(18,0) DEFAULT 0, IndexMaintCostEst FLOAT DEFAULT 0) PRIMARY INDEX (QueryID) INDEX SK_StepID (StepID);
Attribute Definitions for QuerySteps
The following table defines the QuerySteps table attributes:
|StepNum||The number of the step whose text is reported by this QuerySteps row.
|ParallelStepNum||The number of the parallel step whose text is reported by this QuerySteps row.
|StepText||Stores text describing the step.|
|RowType||Describes the type of detail this row characterizes.
|StepKind||Describes the kind of step characterized by this row.
|ParallelKind||Describes whether the step can be done in parallel with its preceding step or not.
|AMPUsage||Describes how AMPs are used to process the step.
|TriggerType||Describes triggering associated with this step.
|EstCPUCost||The CPU time for the step as estimated by the Optimizer, expressed in milliseconds.|
|EstIOCost||The I/O service time for the step as estimated by the Optimizer, expressed in milliseconds.|
|EstNetworkCost||The BYNET service time for the step as estimated by the Optimizer, expressed in milliseconds.|
|EstHRCost||Other miscellaneous costs for the step as estimated by the Optimizer, expressed in milliseconds.|
|Cost||Estimated cost of performing this step, expressed in milliseconds.
Similar to the time estimates provided by EXPLAIN reports, cost values should not be taken as absolute times, but rather as relative values that can be evaluated as proportions with respect to other cost estimates.
|MaxCost||Estimated worst case cost of performing this step.|
|SourceRelation1||Number of the principal source relation in this step.
For example, if the step is a join step and StepAttributeType is SingleRowL, then SourceRelation1 is the number of the left relation in the join.
If StepAttributeType is SingleRowR, then SourceRelation1 is null and SourceRelation2 contains the number of the right relation in the join.
|SourceRelation2||Number of an additional source relation in this step.|
|TargetRelation1||Number of the spool file or table for which the step operation results or acts upon.|
|TargetRelation2||Number of an additional spool file, if one is required, to hold additional results of this step.|
|StepAttributeType||Indicates the attribute that is described by the row.
When the StepAttributeType column contains SnglPart, PartRg, or AllParts, the StepAttributeValue column specifies the sequence (1, 2, ...) of such a lock in the LK StepKind.
|StepAttributeValue||Indicates the value of the attribute described by the row.|
|LockType||Defines the severity of the lock specified by the query plan for this step.
See Transaction Processing for information about lock severities.
|RowHashFlag||Indicates if the table is locked on a row hash.
|NoWaitFlag||Indicates if the no wait option is set for the lock step.
|Cardinality||The estimated number of output rows or affected rows estimated for this given step by the Optimizer.
This value is applicable only to steps that retrieve or modify rows.
|IndexMaintCostEst||The cost estimated to maintain the indexes affected by this step, expressed in milliseconds.
This value is applicable only to steps that modify rows.