show_cached_plan_in_xml

show_cached_plan_in_xml(statement_id, plan_id, [level_of_detail])

statement_id

is the object ID of the lightweight procedure. A lightweight procedure is one that can be created and invoked internally by Adaptive 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 (see Table 2-6). level_of_detail determines which sections of showplan are returned by show_cached_plan_in_xml. The default value is 0.

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

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

This example shows enhanced <est>, <act>, and <scanCoverage> tags available in 15.7.1 and later versions of Adaptive Server:

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

• Enable the statement cache before you use show_cached_plan_in_xml.

• Use show_cached_plan_in_xml for cached statements only.

• The plan does not print if it is in use. Plans with the status of available print plan details. Plans with the status of in use show only the process ID.

• The table below shows the show_cached_plan_in_xml sections that appear for the level_of_detail values:

  Table 2-6: 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

---

Copyright © 2012. Sybase Inc. All rights reserved.

View this document as PDF