Using show_execio_xml to diagnose query plans

show_execio_xml includes diagnostic information that you may find can be helpful for investigating problematic queries. Information from show_execio_xml includes:

• The version level of the query plan. Each version of the plan is uniquely identified. This is the first version of the plan:

  <planVersion>1.0</planVersion>
  

• The statement number in a batch or stored procedure, along with the line number of the statement in the original text. This is statement number 2, but line number 6, in the query:

  <statementNum>2</statementNum>
  <lineNum>6</lineNum>
  

• The abstract plan for the query. For example, this is the abstract plan for the query select * from titles:

  <abstractPlan>
          <![CDATA[>
                   ( i_scan titleidind titles ) ( prop titles ( parallel 1
  ) ( prefetch 8 ) ( lru ) )
  ]]>
          </abstractPlan>
  

• The logical I/O, physical I/O, and CPU costs:

   <costs>
      <lio> 2 </lio>
      <pio> 2 </pio>
      <cpu> 18 </cpu>
  </costs>
  

  You can estimate the total costs with this formula (the 25, 2, and 0.1 are constants):

  25 X pio + 2 X lio + 0.1 X cpu

• The estimated execution resource usage, including the number of threads and auxiliary scan descriptors used by the query plan.

• The number of plans the query engine viewed and the plans it determined were valid, the total time the query spent in the query engine (in milliseconds), the time the query engine took to determine the first legal plan, and the amount of procedure cache used during the optimization process.

  <optimizerMetrics>
      <optTimeMs>6</optTimeMs>
      <optTimeToFirstPlanMs>3</optTimeToFirstPlanMs>
      <plansEvaluated>1</plansEvaluated>
      <plansValid>1</plansValid>
      <procCacheBytes>140231</procCacheBytes>
  </optimizerMetrics>
  

• The last time update statistics was run on the current table and whether the query engine used an estimation constant for a given column that it could have estimated better if statistics were available. This section includes information about columns with missing statistics:

  <optimizerStatistics>
      <statInfo>
          <objName>titles</objName>
          <columnStats>
              <column>title_id</column>
              <updateTime>Oct  5 2006  4:40:14:730PM</updateTime>
          </columnStats>
          <columnStats>
              <column>title</column>
              <updateTime>Oct  5 2006  4:40:14:730PM</updateTime>
          </columnStats>
      </statInfo>
  </optimizerStatistics>
  

• An operator tree that includes table and index scans with information about cache strategies and I/O sizes (inserts, updates, and deletes have the same information for the target table). The operator tree also shows whether updates are performed in “direct” or “deferred” mode. The exchange operator includes information about the number of producer and consumer processes the query used.

  <TableScan>
      <VA>0</VA>
      <est>
          <rowCnt>18</rowCnt>
          <lio>2</lio>
          <pio>2</pio>
          <rowSz>218.5555</rowSz>
      </est>
      <varNo>0</varNo>
      <objName>titles</objName>
      <scanType>TableScan</scanType>
      <partitionInfo>
          <partitionCount>1</partitionCount>
      </partitionInfo>
      <scanOrder> ForwardScan </scanOrder>
      <positioning> StartOfTable </positioning>
      <dataIOSizeInKB>8</dataIOSizeInKB>
      <dataBufReplStrategy> LRU </dataBufReplStrategy>
  </TableScan>