17.00 - 17.05 - XMLPLANオプションによって生成されるXML最適化ルーチン問合わせ計画文書のサンプル - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQLリクエストおよびトランザクション処理

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
ユーザー ガイド
Publication ID
B035-1142-170K-JPN
Language
日本語 (日本)

この節では、典型的なBEGIN QUERY LOGGINGまたはREPLACE QUERY LOGGINGリクエストのXMLPLANロギング オプションによって生成される、XML文書のインスタンスのサンプルを示します。

この文書インスタンスは、次のSELECTリクエストに対して獲得された問合わせログ情報を表わしています。

      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 (&quot;(NOT (PERSONNEL.e.deptno IS NULL )) AND (PERSONNEL.e.yrsexp >= 5)&quot;) 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 (&quot;PERSONNEL.d.deptno = deptno&quot;). 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>