Query-level debugging in Adaptive Server 15.0

This section describes debugging techniques for Adaptive Server release 15.0.

Using optdiag for statistics

You can use optdiag to determine if the current statistics are stale and you need to generate new statistics. This example uses optdiag to capture the statistics for the part table of the le_01 database:

$SYBASE/ASE-15_0/bin/optdiag statistics le_01.dbo.part -Usa -P

Server name: "tpcd"

Specified database: "le_01"
Specified table owner: "dbo"
Specified table: "part"
Specified column: not specified

Table owner: "dbo"
Table name: "part"

...................................................
Statistics for column: "p_partkey"
Last update of column statistics: Sep 13 2005 7:51:39:440PM

Range cell density: 0.0010010010010010
Total density: 0.0010010010010010
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Histogram for column: "p_partkey"
Column datatype: integer
Requested step count: 20
Actual step count: 20
Sampling Percent: 0

Step Weight Value

1 0.00000000 <= 0
2 0.05205205 <= 52

.......................................................
Statistics for column: "p_brand"
Last update of column statistics: Sep 13 2005 7:51:39:440PM

Range cell density: 0.0010010010010010
Total density: 0.0010010010010010
Range selectivity: default used (0.33)
In between selectivity: default used (0.25)

Using show_final_plan_xml for statistics

With Adaptive Server 15.0 and later, you can use XML to determine when statistics were last updated. Specify a query on which you want to run statistics, and Adaptive Server displays only those statistics that are useful for the query, including when the statistics were last updated for these columns. optdiag shows statistics for all indices whether or not they are used, and requires multiple executions if more than one table is involved. The following collects statistics for the part table from the previous example:

1> set plan for show_final_plan_xml to message on
2> go
1> select count(*) from part where p_partkey > 20
2> go

-----------979
1> select showplan_in_xml(-1)
2> go

-----------
979
<?xml version="1.0" encoding="UTF-8"?>
<query>
             <planVersion> 1.0 </planVersion>
             <statementNum>1</statementNum>
             <lineNum>1</lineNum>
             <text>
                   <![CDATA[
                      SQL Text: select count(*) from part where p_partkey > 20
                   ]]>
                </text>
                   <objName>part</objName>
                   <columnStats>
                      <column>p_partkey</column>
                      <updateTime>Sep 13 2005 7:51:39:440PM</updateTime>
                   </columnStats>

Sending XML data directly to the client

This example uses trace flag 3604 and the client parameter to send the information from show_final_plan_xml to the client:

1> dbcc traceon(3604)

DBCC execution completed. If DBCC printed error messages, contact a user withSystem Administrator (SA) role.

set plan for show_final_plan_xml to client on
go
select * from part, partsupp
where p_partkey = ps_partkey and p_itemtype = ps_itemtype
go

<?xml version="1.0" encoding="UTF-8"?>
<query>
<planVersion> 1.0 </planVersion> 
<optimizerStatistics>
<statInfo>
                     <objName>part</objName>
                     <missingHistogram>
                             <column>p_partkey</column>
                             <column>p_itemtype</column>
                     </missingHistogram>
                     <missingDensity>
                             <column>p_partkey</column>
                             <column>p_itemtype</column>
                     </missingDensity>}
               </statInfo>
               <statInfo>
                     <objName>partsupp</objName>
                     <missingHistogram>
                             <column>ps_partkey</column>
                             <column>ps_itemtype</column>
                     </missingHistogram>
                     <missingDensity>
                             <column>ps_partkey</column>
                             <column>ps_itemtype</column>
                     </missingDensity>
               </statInfo>
         </optimizerStatistics>