This section provides a sample XML document instance that is generated by the XMLPLAN logging option for a typical BEGIN QUERY LOGGING or REPLACE QUERY LOGGING request.
The document instance represents the query logging information captured for the following SELECT request.
SELECT name, deptname, salary FROM department d, employee e WHERE d.deptno = e.deptno AND e.yrsexp >= 5 order by 3 desc; <?xml version="1.0" encoding="UTF-16" standalone="no" ?> <QryPlanXML xmlns="http://schemas.teradata.com/queryplan" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/queryplan http://schemas.teradata.com/queryplan/queryplan.xsd"> <Query CollectTimeStamp="2015-02-24T03:38:23.65" QCFCaptureTimestamp="2015-05-06T19:36:15.75" QueryID="307191055761854061" StartTime="2015-05-06T19:36:15.75"> <Request CheckpointNum="0" DefaultDatabase="PERSONNEL" MaxStepMemory="3.437" NumResultRows="3" QueryText="SELECT name (CHAR(10)), deptname (CHAR(10)), salaryFROM department d, employee eWHERE d.deptno = e.deptno AND e.yrsexp >= 5 order by 3 desc;"> <Statement QCFStatementType="RET" StatementGroup="Select" StatementType="Select"/> </Request> <ObjectDefs> <Database DatabaseName="PERSONNEL" Id="DBPERSONNEL"> <Relation Cardinality="4" Confidence="High" DatabaseId="DBPERSONNEL" Id="REL1" Partitioned="false" RelationKind="Permanent" TableName="department" Version="1"> <Field DataLength="4" FieldID="1025" FieldName="deptno" FieldType="I" Id="REL1_FLD1025" JoinAccessFrequency="1" RangeAccessFrequency="0" RelationId="REL1" ValueAccessFrequency="0"/> <Index Id="REL1_IDX1" IndexNum="1" IndexType="Nonpartitioned Primary" OrderBy="false" QCFIndexFlag="false" RelationId="REL1" UniqueFlag="false"> <FieldRef Ref="REL1_FLD1025"/> </Index> </Relation> <Relation Cardinality="12" Confidence="High" DatabaseId="DBPERSONNEL" Id="REL2" Partitioned="false" RelationKind="Permanent" TableName="employee" Version="1"> <Field DataLength="4" FieldID="1025" FieldName="employeeID" FieldType="I" Id="REL2_FLD1025" JoinAccessFrequency="0" RangeAccessFrequency="0" RelationId="REL2" ValueAccessFrequency="0"/> <Field DataLength="4" FieldID="1027" FieldName="deptno" FieldType="I" Id="REL2_FLD1027" JoinAccessFrequency="1" RangeAccessFrequency="0" RelationId="REL2" ValueAccessFrequency="0"/> <Field DataLength="4" FieldID="1029" FieldName="yrsexp" FieldType="I" Id="REL2_FLD1029" JoinAccessFrequency="0" RangeAccessFrequency="1" RelationId="REL2" ValueAccessFrequency="0"/> <Index Id="REL2_IDX1" IndexNum="1" IndexType="Nonpartitioned Primary" OrderBy="false" QCFIndexFlag="false" RelationId="REL2" UniqueFlag="false"> <FieldRef Ref="REL2_FLD1025"/> </Index> </Relation> </Database> <Database DatabaseName="DBC" Id="DBDBC"> <Spool Cardinality="4" Compressible="true" Confidence="No" DatabaseId="DBDBC" GeogInfo="Hash Distributed" Id="SPOOL2" Kind="Regular" Partitioned="false" SpoolNumber="2" SpoolSize="432"> <Field DataLength="4" FieldID="1027" FieldName="deptno" FieldType="I" Id="SPOOL2_FLD1027" JoinAccessFrequency="0" RangeAccessFrequency="0" RelationId="SPOOL2" ValueAccessFrequency="0"/> </Spool> <Spool Cardinality="6" Compressible="false" Confidence="No" DatabaseId="DBDBC" GeogInfo="Local" Id="SPOOL1" Kind="Regular" Partitioned="false" SpoolNumber="1" SpoolSize="312"/> </Database> </ObjectDefs> <Plan CacheFlag="False" IPEEligibility="NotEligible" NumSteps="6" PlanType="Static"> <PlanStep QCFParallelKind="Sequential" QCFStepKind="LK" QCFStepNum="1" StepLev1Num="1" StepName="MLK" StepText="1) First, we lock PERSONNEL.d for read on a reserved RowHash to prevent global deadlock." StepWD="13" TriggerType="None"> <StepDetails> <MLK> <LockOperation LockKind="Proxy" LockLevel="Row" LockSeverity="Read" NoWaitFlag="false" PLLKind="NotPLL"> <RelationRef AliasName="d" Ref="REL1"/> </LockOperation> </MLK> </StepDetails> <AmpStepUsage NumofActiveAMPs="1" QCFAmpUsage="One" StepRowsReturned="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPOtherWaitTime="0.086" MaxAMPOtherWaitTime="0.086" MaxOtherWaitTimeAMPNum="3"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47" TriggerKind="None"/> </PlanStep> <PlanStep QCFParallelKind="Sequential" QCFStepKind="LK" QCFStepNum="2" StepLev1Num="2" StepName="MLK" StepText="2) Next, we lock PERSONNEL.e for read on a reserved RowHash to prevent global deadlock." StepWD="13" TriggerType="None"> <StepDetails> <MLK> <LockOperation LockKind="Proxy" LockLevel="Row" LockSeverity="Read" NoWaitFlag="false" PLLKind="NotPLL"> <RelationRef AliasName="e" Ref="REL2"/> </LockOperation> </MLK> </StepDetails> <AmpStepUsage NumofActiveAMPs="1" QCFAmpUsage="One" StepRowsReturned="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPOtherWaitTime="0.528" MaxAMPOtherWaitTime="0.528" MaxOtherWaitTimeAMPNum="1"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47" TriggerKind="None"/> </PlanStep> <PlanStep QCFParallelKind="Sequential" QCFStepKind="LK" QCFStepNum="3" StepLev1Num="3" StepName="MLK" StepText="3) We lock PERSONNEL.d for read, and we lock PERSONNEL.e for read." StepWD="13" TriggerType="None"> <StepDetails> <MLK> <LockOperation LockKind="Real" LockLevel="Table" LockSeverity="Read" NoWaitFlag="false" PLLKind="NotPLL"> <RelationRef AliasName="d" Ref="REL1"/> </LockOperation> <LockOperation LockKind="Real" LockLevel="Table" LockSeverity="Read" NoWaitFlag="false" PLLKind="NotPLL"> <RelationRef AliasName="e" Ref="REL2"/> </LockOperation> </MLK> </StepDetails> <AmpStepUsage NumofActiveAMPs="4" QCFAmpUsage="All" StepRowsReturned="4" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPOtherWaitTime="0.175" MaxAMPOtherWaitTime="0.088" MaxOtherWaitTimeAMPNum="2"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47" TriggerKind="None"/> </PlanStep> <PlanStep QCFParallelKind="Sequential" QCFStepKind="SR" QCFStepNum="4" StepLev1Num="4" StepName="RET" StepText="4) We do an all-AMPs RETRIEVE step from PERSONNEL.e by way of an all-rows scan with a condition of ("(NOT (PERSONNEL.e.deptno IS NULL )) AND (PERSONNEL.e.yrsexp >= 5)") into Spool 2 (all_amps), which is redistributed by the hash code of (PERSONNEL.e.deptno) to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with no confidence to be 4 rows (432 bytes). The estimated time for this step is 0.03 seconds." StepWD="13" TriggerType="None"> <SourceAccess AccessPosition="1" SyncScanEligible="false"> <RelationRef AliasName="e" Ref="REL2"/> <PPIAccess TotalParts="0"/> <CPInfo NumCombinedPartitions="0" NumContexts="0" NumOfColPartsReferences="0"/> </SourceAccess> <TargetStore Cached="true" GeogInfo="Hash Distributed"> <SpoolRef Ref="SPOOL2"/> <SortKey SortKind="Rowhash"/> </TargetStore> <Predicate PredicateKind="Source" PredicateText="(NOT (PERSONNEL.e.deptno IS NULL )) AND (PERSONNEL.e.yrsexp >= 5 )"> <FieldRef Ref="REL2_FLD1027"/> <FieldRef Ref="REL2_FLD1029"/> </Predicate> <OptStepEst EstCPUCost="0.015" EstCPUTime="0.000" EstHRCost="0.000" EstIOCost="26.637" EstIOTime="0.027" EstNetCost="0.022" EstProcTime="0.027" EstRowCount="4"/> <AmpStepUsage IOCount="60" IOKB="2012.000" MaxAMPSpool="1536" MaxAmpIO="27" MaxIOAmpNumber="1" MinAMPSpool="0" MinAmpIO="4" NumofActiveAMPs="4" QCFAmpUsage="All" SpoolUsage="3072" StepRowsReturned="3" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPCPURunDelay="0.002" AMPOtherWaitTime="2.223" MaxAMPCPURunDelay="0.001" MaxAMPOtherWaitTime="0.626" MaxCPURunDelayAMPNum="2" MaxOtherWaitTimeAMPNum="2"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.47" TriggerKind="None"/> </PlanStep> <PlanStep QCFParallelKind="Sequential" QCFStepKind="MJ" QCFStepNum="5" StepLev1Num="5" StepName="JIN" StepText="5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a RowHash match scan, which is joined to PERSONNEL.d by way of a RowHash match scan. Spool 2 and PERSONNEL.d are joined using a merge join, with a join condition of ("PERSONNEL.d.deptno = deptno"). The result goes into Spool 1 (group_amps), which is built locally on the AMPs. Then we do a SORT to order Spool 1 by the sort key in spool field1 (PERSONNEL.e.salary). The size of Spool 1 is estimated with no confidence to be 6 rows (312 bytes). The estimated time for this step is 0.11 seconds." StepWD="13" TriggerType="None"> <SourceAccess AccessPosition="1" SyncScanEligible="false"> <SpoolRef Ref="SPOOL2"/> <PPIAccess TotalParts="0"/> <CPInfo NumCombinedPartitions="0" NumContexts="0" NumOfColPartsReferences="0"/> </SourceAccess> <SourceAccess AccessPosition="2" SyncScanEligible="false"> <RelationRef AliasName="d" Ref="REL1"/> <PPIAccess TotalParts="0"/> <CPInfo NumCombinedPartitions="0" NumContexts="0" NumOfColPartsReferences="0"/> </SourceAccess> <TargetStore Cached="true" GeogInfo="Local"> <SpoolRef Ref="SPOOL1"/> <SortKey SortKind="Field1"/> </TargetStore> <Predicate PredicateKind="Join" PredicateText="PERSONNEL.d.deptno = deptno"> <FieldRef Ref="REL1_FLD1025"/> <FieldRef Ref="SPOOL2_FLD1027"/> </Predicate> <StepDetails> <JIN JoinKind="Merge Join" JoinType="Inner" LeftOneRowOpt="false" RightOneRowOpt="false"/> </StepDetails> <OptStepEst EstCPUCost="0.016" EstCPUTime="0.000" EstHRCost="0.000" EstIOCost="106.559" EstIOTime="0.107" EstNetCost="0.000" EstProcTime="0.107" EstRowCount="6"/> <AmpStepUsage IOCount="63" IOKB="2147.000" MaxAMPSpool="1024" MaxAmpIO="51" MaxIOAmpNumber="1" MinAMPSpool="0" MinAmpIO="7" NumofActiveAMPs="4" QCFAmpUsage="All" SpoolUsage="2048" StepRowsReturned="3" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.48"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPCPURunDelay="0.006" AMPOtherWaitTime="0.002" MaxAMPCPURunDelay="0.003" MaxAMPOtherWaitTime="0.001" MaxCPURunDelayAMPNum="3" MaxOtherWaitTimeAMPNum="2"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.47" StepStopTime="2015-05-06T19:36:16.48" TriggerKind="None"/> </PlanStep> <PlanStep QCFParallelKind="Sequential" QCFStepKind="MS" QCFStepNum="6" StepLev1Num="6" StepName="Edt" StepText="6) Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request. -> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.13 seconds." StepWD="13"> <AmpStepUsage IOCount="36" MaxAMPSpool="1024" MaxAmpIO="26" MaxIOAmpNumber="1" MinAMPSpool="0" NumofActiveAMPs="4" QCFAmpUsage="Group" SpoolUsage="2048" StepRowsReturned="4" StepStartTime="2015-05-06T19:36:16.48" StepStopTime="2015-05-06T19:36:16.48"/> <AmpStepVHFSGUsage/> <SchedulerAmpStepUsage AMPOtherWaitTime="0.005" MaxAMPOtherWaitTime="0.002" MaxOtherWaitTimeAMPNum="3"/> <StepDBQL StatementNum="1" StepStartTime="2015-05-06T19:36:16.48" StepStopTime="2015-05-06T19:36:16.48" TriggerKind="None"/> </PlanStep> <OptPlanEst EstMaxRowCount="6" EstProcTime="0.133" EstResultRows="6"/> <PlanDBQLStats CallNestingLevel="0" DataCollectAlg="1" RequestMode="Exec" StatementCount="1" TxnMode="BTET"/> <AmpPlanUsage AMPCPUTime="0.000" AMPCPUTimeNorm="0.000" DisCPUTimeNorm="0.269" FirstRespTime="2015-05-06T19:36:16.48" FirstStepTime="2015-05-06T19:36:16.47" MaxAMPCPUTime="0.000" MaxAMPCPUTimeNorm="0.000" MaxAmpIO="53" MaxIOAmpNumber="1" MinAMPCPUTime="0.000" MinAMPCPUTimeNorm="0.000" MinAmpIO="9" NumofActiveAMPs="4" ParserCPUTime="0.024" ParserCPUTimeNorm="1.614" ReqIOKB="4419" SpoolUsage="3072" TotalIOCount="98"/> <AmpPlanVHFSGUsage/> <PlanDecayLevel CPUDecayLevel="0" IODecayLevel="0"/> <PlanTacticalException TacticalCPUException="0" TacticalIOException="0"/> <PEPlanUsage PECPURunDelay="0.008" PEIOWaitTime="0.503" PEIOWaitTimePDE="0.000" PEOtherWaitTime="0.851" PEOtherWaitTimePDE="0.000" ParserCPUKernelTime="0.004" ParserCPUKernelTimeNorm="0.000"/> <SchedulerAmpPlanUsage AMPCPUKernelTime="0.000" AMPCPUKernelTimeNorm="0.000" SeqRespTime="0.010"/> </Plan> <Configuration NumAMPs="4" NumNodes="1" NumPEs="2" PENum="30719" ReleaseInfo="15.10n.00.42" SystemName="localhost" VersionInfo="15.10n.00.42"> <TLE CostProfileName="TD15"/> </Configuration> <User AcctString="DBC" ExpandAcctString="DBC" UserID="00000504" UserName="PERSONNEL" ZoneID="00000000"/> <Session AppID="BTEQ" ClientAddr="153.65.210.211" ClientID="BS255018" InternalRequestNum="4" KeepFlag="false" LogicalHostID="1" LogonDateTime="2015-05-06T19:36:13.67" LogonSource="(TCP/IP) d027 153.65.210.211 IE1510 5516 BS255018 BTEQ 01 LSS" NumRequestCtx="1" RequestNum="4" SessionID="1281" SessionWDID="13"/> <WLMgmt FinalWDID="13" OpEnvID="1" ResponseTimeMet="true" SysConID="1" TDWMEstLastRows="6" TDWMEstMaxRows="6" TDWMEstMemory="4" TDWMEstTotalTime="133" ThrottleBypass="false" TxnUniq="-378667007" WDID="13"/> <ReDrive QueryReDriven="N"/> </Query> </QryPlanXML>