show_cached_plan_in_xml

show_cached_plan_in_xml returns sections of the showplan utility output in XML format.

Syntax

show_cached_plan_in_xml(statement_id, plan_id, [level_of_detail])

Parameters

• statement_id – is the object ID of the lightweight procedure. A lightweight procedure is one that can be created and invoked internally by the SAP ASE server. This is the SSQLID column from monCachedStatement, which contains a unique identifier for each cached statement.
• plan_id – is the unique identifier for the plan. This is the PlanID from monCachedProcedures. A value of zero for plan_id displays the showplan output for all cached plans for the indicated SSQLID.
• level_of_detail – is a value from 0 – 6 indicating the amount of detail show_cached_plan_in_xml returns, and determines which sections of showplan are returned by show_cached_plan_in_xml. The default value is 0.

  Level of Detail

  level_of_detail	Parameter	opTree	execTree
  0 (the default)	X	X
  1	X
  2		X
  3			X
  4		X	X
  5	X		X
  6	X	X	X

  The output of show_cached_plan_in_xml includes the plan_id and these sections:

  • parameter – contains the parameter values used to compile the query and the parameter values that caused the slowest performance. The compile parameters are indicated with the <compileParameters> and </compileParameters> tags. The slowest parameter values are indicated with the <execParameters> and </execParameters> tags. For each parameter, show_cached_plan_in_xml displays the:

    • Number

    • Datatype

    • Value – values that are larger than 500 bytes and values for insert-value statements do not appear. The total memory used to store the values for all parameters is 2KB for each of the two parameter sets.

Examples

• Example 1 – Shows a query plan rendered in XML:

  select show_cache_plan_in_xml(1328134997,0)
  go
  -------------------------------------------

  <?xml version="1.0" encoding="UTF-8"?>
  <query>
      <statementId>1328134997</statementId>
  <text>
      <![CDATA[SQL Text: select name from sysobjects where id = 10]]>
  </text>
  <plan>
      <planId>11</planId>
      <planStatus> available </planStatus>
      <execCount>1371</execCount>
      <maxTime>3</maxTime>
      <avgTime>0</avgTime>
      <compileParameters/>
      <execParameters/>
      <opTree>
          <Emit>
          <VA>1</VA>
          <est>
              <rowCnt>10</rowCnt>
              <lio>0</lio>
              <pio>0</pio>
              <rowSz>22.54878</rowSz>
          </est>
          <act>
              <rowCnt>1</rowCnt>
          </act>
          <arity>1</arity>
              <IndexScan>
                  <VA>0</VA>
                  <est>
                      <rowCnt>10</rowCnt>
                      <lio>0</lio>
                      <pio>0</pio>
                      <rowSz>22.54878</rowSz>
                  </est>
                  <act>
                      <rowCnt>1</rowCnt>
                      <lio>3</lio>
                      <pio>0</pio>
                  </act>
                  <varNo>0</varNo>
                  <objName>sysobjects</objName>
                  <scanType>IndexScan</scanType>
                  <indName>csysobjects</indName>
                  <indId>3</indId>
                  <scanOrder> ForwardScan </scanOrder>
                  <positioning> ByKey </positioning>
                  <perKey>
                      <keyCol>id</keyCol>
                      <keyOrder> Ascending </keyOrder>
                  </perKey>
                  <indexIOSizeInKB>2</indexIOSizeInKB>
                  <indexBufReplStrategy> LRU </indexBufReplStrategy>
                  <dataIOSizeInKB>2</dataIOSizeInKB>
                  <dataBufReplStrategy> LRU </dataBufReplStrategy>
               </IndexScan>
          </Emit>
      </opTree>
  </plan>

• Example 2 – Shows enhanced <est>, <act>, and <scanCoverage> tags available in 15.7.1 and later versions of SAP ASE:

  select show_cached_plan_in_xml(1123220018, 0)
  go

  <?xml version="1.0" encoding="UTF-8"?>
  <query>
     <statementId>1123220018</statementId>
     <text>
     <![CDATA[
     SQL Text: select distinct c1, c2 from t1, t2 where c1 = d1 PLAN '( distinct_hashing ( nl_join ( t_scan t2 ) ( i_scan i1t1 t1 ) ) )']]>
     </text>
     <plan>
        <planId>6</planId>
        <planStatus> available </planStatus>
        <execCount>1</execCount>
        <maxTime>16</maxTime>
        <avgTime>16</avgTime>
        <compileParameters/>
        <execParameters/>
        <opTree>
           <Emit>
              <VA>4</VA>
              <est>
                 <rowCnt>1</rowCnt>
                 <lio>0</lio>
                 <pio>0</pio>
                 <rowSz>10</rowSz>
              </est>
              <arity>1</arity>
              <HashDistinct>
                 <VA>3</VA>
                 <est>
                    <rowCnt>1</rowCnt>
                    <lio>5</lio>
                    <pio>0</pio>
                    <rowSz>10</rowSz>
                 </est>
                 <arity>1</arity>
                 <WorkTable>
                    <wtObjName>WorkTable1</wtObjName>
                 </WorkTable>
                 <NestLoopJoin>
                    <VA>2</VA>
                    <est>
                       <rowCnt>1</rowCnt>
                       <lio>0</lio>
                       <pio>0</pio>
                       <rowSz>10</rowSz>
                    </est>
                    <arity>2</arity>
                    <TableScan>
                       <VA>0</VA>
                       <est>
                          <rowCnt>1</rowCnt>
                          <lio>1</lio>
                          <pio>0.9999995</pio>
                          <rowSz>6</rowSz>
                       </est>
                       <varNo>0</varNo>
                       <objName>t2</objName>
                       <scanType>TableScan</scanType>
                       <scanOrder> ForwardScan </scanOrder>
                       <positioning> StartOfTable </positioning>
                       <scanCoverage> NonCovered </scanCoverage>
                       <dataIOSizeInKB>16</dataIOSizeInKB>
                       <dataBufReplStrategy> LRU </dataBufReplStrategy>
                    </TableScan>
                    <IndexScan>
                       <VA>1</VA>
                       <est>
                          <rowCnt>1</rowCnt>
                          <lio>0</lio>
                          <pio>0</pio>
                          <rowSz>10</rowSz>
                       </est>
                       <varNo>1</varNo>
                       <objName>t1</objName>
                       <scanType>IndexScan</scanType>
                       <indName>i1t1</indName>
                       <indId>1</indId>
                       <scanOrder> ForwardScan </scanOrder>
                       <positioning> ByKey </positioning>
                       <scanCoverage> NonCovered </scanCoverage>
                       <perKey>
                          <keyCol>c1</keyCol>
                          <keyOrder> Ascending </keyOrder>
                       </perKey>
                       <dataIOSizeInKB>16</dataIOSizeInKB>
                       <dataBufReplStrategy> LRU </dataBufReplStrategy>
                    </IndexScan>
                 </NestLoopJoin>
              </HashDistinct>
           </Emit>
           <est>
              <totalLio>6</totalLio>
              <totalPio>0.9999995</totalPio>
           </est>
           <act>
              <totalLio>0</totalLio>
              <totalPio>0</totalPio>
           </act>
        </opTree>
     </plan>
  </query>

Usage

Permissions

The permission checks for show_cached_plan_in_xml differ based on your granular permissions settings.