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. ------
