Fixing statistics

After collecting missing statistics information into an XML file, you can update the statistics that are equal to, or exceed, the threshold for count of missing statistics specified by the -m option. Use the fix_stats action to update statistics.

The -i option specifies the input XML file that contains all missing statistics.

You can generate a SQL script for updating statistics without executing the actual updates by using the -N option to indicate “noexec”, and the -o option to indicate the output script file. The output file is created with all the generated update statistics statements but the statements are not executed. Generated scripts have a SQL file format. Using the -N option gives you the option of running the SQL script at a later time to optimize your resources.

For example:

QPTune -A fix_stats -m 1 -i 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 fix_stats -M allrows_dss -T 0 -i missingstats.xml -o metrics.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
Fix statistics on "Fri Sep 26 10:14:59 PDT 2008"
Details of statements(s) fixed:
Fixed statistics:[Update] Y(y4,y2)
[INFO] Fix Statement = update statistics Y(y4,y2)
Fixed statistics:[Update] Y(y3)
[INFO] Fix Statement = update statistics Y(y3)
Fixed statistics:[Update] Y(y2,y1)
[INFO] Fix Statement = update statistics Y(y2,y1)
Fixed statistics:[Update] Y(y1)
[INFO] Fix Statement = update statistics Y(y1)
----- QPTune finished executing. ------

For example:

QPTune -U sa -P -S my_host:5000/my_database 
       -A fix_stats -m 5 -i missingstats.xml 
        -N -o missingstats.sql