show_cached_plan_in_xml

Description

Displays, in XML, the executing query plan for queries in the statement cache.

show_cached_plan_in_xml returns sections of showplan 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 procedure that can be created and invoked internally by Adaptive Server). This is the SSQLID from monCachedStatement.

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.
opTree – contains the query plan and the optimizer estimates. The opTree section is delineated by the <opTree> and </opTree> tags.
execTree – contains the query plan with the lava operator details. The execTree section is identified by the tags <execTree> and </execTree>.

Examples

Example 1

This is an example of 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>

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.

Table 2-6 shows the show_cached_plan_in_xml sections that appear for the level_of_detail values:

**Table 2-6: Sections that appear for level_of_detail values**
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