Collecting statistics

After preparing the system by running QPTune with the start_stats action, you may begin collecting the missing statistics with the collect_stats action. You can have QPTune either perform this action immediately, or after waiting for some period of time. This feature enables you to automate the start_stats and collect_stats steps.

collect_stats retrieves missing statistics information from the sysstatistics table for statistics that exceed a specified threshold for count of missing statistics. QPTune consolidates the missing statistics and determines a minimum set of statistics that must be updated.

The -m option indicates the threshold for count of missing statistics. When the statistics for a query have been missed as many times as the threshold value or more, they are collected and exported to an XML file. The default threshold count is 5.

The -o option indicates the output XML file that holds missing statistics. Use the output XML from collect_stats as input to the fix_stats and undo_fix_stats actions.

For example:

QPTune -A collect_stats -m 1 -o missingstats.xml -v 
        -S my_host:4816/my_database
Executing : QPTune -U sa -P [unshown] -S jdbc:sybase:Tds:my_host:4816/my_database -A collect_stats -M allrows_dss -T 0 -i null -o missingstats.xml -f null -c config.xml -l 5 -e elap_avg -d 5,5 -m 1 -n null -v
You are now connected to database: my_database
Now collecting missing statistics information from sysstatistics on "Fri Sep 26 10:08:06 PDT 2008".
<?xml version="1.0" encoding="UTF-8"?>
<server url="jdbc:sybase:Tds:my_host:4816/my_database"
file="missingstats.xml"
type="missing stats" datetime="Fri Sep 26 10:08:06 PDT 2008" >
<missingStat id="1">
<id>1068527809</id>
<stats>Y(y4,y2)</stats>
<count>2</count>
</missingStat>
<missingStat id="2">
<id>1068527809</id>
<stats>Y(y3)</stats>
<count>1</count>
</missingStat>
<missingStat id="3">
<id>1068527809</id>
<stats>Y(y2,y1)</stats>
<count>1</count>
</missingStat>
<missingStat id="4">
<id>1068527809</id>
<stats>Y(y1)</stats>
<count>1</count>
</missingStat>
</server>
The missing statistics information is written into XML file: missingstats.xml
[INFO] End config: sp_configure 'enable metrics capture', 0
[INFO] End config: sp_configure 'abstract plan dump', 0
[INFO] End config: sp_configure 'system table', 0
[INFO] End config: sp_configure 'capture missing statistics', 0
Program has restored the data source for metrics collection.
----- QPTune finished executing. ------