qptune

Description

QPTune is an Adaptive Server utility written in Java/XML. It enables users to fix missing statistics and identify the best query plan, optimization goals, or other configuration settings, and apply them at the query or server level. This results in optimal performance of subsequent query executions.

Syntax

qptune 
	[-U username] 
	[-P password] 
	[-S hostname:port/database]
	[-A action]
	[-M mode] 
	[-T appTime] 
	[-i inputFile]
	[-o outputFile]
	[-f fileList(,)] 
	[-c configFile]
	[-l limit] 
	[-e evalField]
	[-d <diff%(,diff_abs)>] 
	[-m missingCount] 
	[-n login] 
	[-J charset>]
	[-N (noexec)] 
	[-g (applyOptgoal)]
	[-v (verbose)] 
	[-s (sort)] 
	[-h (help)]

Parameters

Examples

Example 1

Fixes missing statistics, start the utility with the start_stats action:

QPTune -A start_stats -S my_host:4816/my_database  -v

Executing : QPTune -U sa -P [unshown] 
-S jdbc:sybase:Tds:my_host:4816/my_database 
-A start_stats -M allrows_dss -T 0 -i null 
-o metrics.xml -f null -c config.xml -l 5 
-e elap_avg -d 5,5 -m 5 -n null -v
You are now connected to database: my_database
[INFO] Config: sp_configure 'capture missing statistics', 1
[INFO] Config: sp_configure 'system table', 1
[INFO] Config: delete sysstatistics where formatid =110

Example 2

Uses collect_stats to retrieve missing statistics information from the sysstatistics table for statistics that exceed a specified threshold for count of missing statistics:

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

Example 3

After collecting missing statistics information into an XML file called missingstats.xml, updates the statistics using the fix_stats action:

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

Generates 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:

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

Example 4

Starts QPTune to apply standard optimization goal settings to queries:

QPTune -S host:port/database -A start 
        [-M {allrows_oltp, allrows_dss, allrows_mix}]

Start QPTune to apply custom rules to specified queries:

QPTune -S host:port/database -A start -M custom_1 
        -i input.xml -l 3 [-v]

Example 5

Runs your application and collect metrics into an XML file named a2.xml:

QPTune -S host:port/database -A collect -T 0 
        -o a2.xml -v

Program has configured the data source for metrics collection.
Now collecting information from sysquerymetrics on "Tue Feb 19 22:16:04 PST 2008".
<?xml version="1.0" encoding="UTF-8"?>
 <server url="jdbc:sybase:Tds:SHANGHI:5000" type="ASE" mode="custom_1" datetime="Tue Feb 19 22:16:04 PST 2008">
<query id="1">
<qtext> select count(T.title_id) from authors A, titleauthor T 
where A.au_id = T.au_id </qtext>
<elap_avg>300</elap_avg>
<bestmode> custom_1
</bestmode>
</query>
</server>

Example 6

Once metrics are collected, compares different XML files to get the best query optimization goal or criteria for each of the queries:

QPTune -A compare -f a1.xml,a2.xml -d 51,10 
     -o best.xml -S my_host:5000/my_database

This result shows a comparison between two XML metrics files: a1.xml has six queries, and a2.xml has seven queries. Comparisons can only be made between the queries that are common to both files. There are three queries that ran faster in a2.xml:

Compare all the files: | a1.xml, a2.xml|
Report generated on "Tue Aug 19 21:13:04 PST 2008"
--------------------------------------------------------------------------
File #1: [name= a1.xml  :  mode=allrows_mix]
File #2: [name= a2.xml  :  mode=custom_1]
Query count in File #1 : [mode=allrows_mix]         6
Query count in File #2 : [mode=custom_1]            7
==========================================================================
Query count improved in File #2: [mode=allrows_mix] 3

Total performance improved [from 422 to 129]: 69 %

Following queries run better in File #2:
[mode=allrows_mix]
--------------------------------------------------------------------------
Group 1: improved by no more than 25% [0 queries]
Group 2: improved by 25% to 50% [1 queries]
Query: select count(T.title_id) from authors A, titleauthors T where A.au_id = T.au_id
Average elapsed time (ms): File #1=100  File #2=50   Improvement=50.0%  Outstanding=No
Group 3: improved by 50% to 75% [0 queries]
Group 4: improved by 75% to 100% [2 queries]
Query: select count(*) from titlles T, titleauthors TA where T.title_id = TA.title_id
Average elapsed time (ms): File #1=34  File #2=7       Improvement=79.0%  Outstanding=Yes
Query: select au_lname, au_fname from authors where state in ("CA", "AZ") 
Average elapsed time (ms): File #1=9   File #2=0       Improvement=100.0% Outstanding=No

Usage

If specific values are not indicated for the parameters, the following defaults are used:

• -A : collect

• -M : allrows_dss

• -T : 0

• -o : metrics.xml

• -c : config.xml

• -e : elap_avg

• -d : 5,5. If percentage is specified but not the absolute value, then absolute value defaults to 0.

• -l limit

• -m 5

Permissions

QPtune’s compare action may be run by any user. All other actions of QPTune may only be run by users with sa_role and sso_role.

See also

For more information about the QPTune utility or the QPTune GUI, see the Migration Technology Guide.