QPTune is an Adaptive Server utility written in Java/XML. It enables users to 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 later query executions.
QPTune [-U <username>] [-P <password>] [-S <hostname:port/database>] [-A <action [start|collect(_full)|compare|fix|(start|collect|fix|undo_fix)_stats]>] [-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)]
Example:
QPTune -U sa -P -S my_host:5000/my_database -A collect -M allrows_mix -T 0 -o metrics.xml -c config.xml -e elap_avg -d 5,5 -l 5 -i metrics.xml -fa1.xml,a2.xml,a3.xml -v –s
-U username
specifies the database user name.
-P password
specifies the database password.
-S server
specifies the database server. The database server is denoted by host:port/database.
You must specify the -S option while
using any QPTune actions.
-A action
specifies the action to be taken. One of: start | collect | collect_full | compare | fix | start_stats | collect_stats | fix_stats | undo_fix_stats.
-J charset
specifies the character set used to connect to Adaptive Server. If this option is not specified, the Adaptive Server uses the server’s default character set.
If the installed JRE does not support the server's default
charset encoding, you see an error message during the login process.
Use the -J option to specify a more generic character set, such
as -J utf8.
-M mode
specifies the optimization goal or custom mode for an application. One of: allrows_oltp, allrows_dss, allrows_mix. You may also define custom modes; _basic_ is a system reserved custom mode.
-T appTime
specifies the application running time, in minutes.
-o outputFile
specifies the output file.
-i inputFile
specifies the input file for the fix, fix_stats, and undo_fix_stats actions. You can also use -i to apply special rules to the specified queries for start for custom modes.
-f fileList
compares a list of files to get the best plans; use commas to separate filenames.
-c configFile
specifies the configuration file.
-l limit
specifies a limit on the number of queries that should be analyzed and applied with special rules.
-e evalField
evaluation field used for performance comparison.
-d difference
specifies the percentage and absolute value difference for performance improvement to be considered outstanding.
-N
used along with fix_stats and undo_fix_stats, -N generates a SQL script with update statistics or delete statistics statements. The update or delete statements are not executed through QPTune. The statements are written into a SQL script that is specified by the -o option.
-n login
specifies the user’s login whose query executions are collected and analyzed.
-m missingCount
specifies the threshold value for missing statistics. The default value is 5.
-v
specifies verbose mode.
-g
when used along with the fix action, applies the default goal. The default goal is the best optgoal setting that most queries used as the best plan using QPTune’s fix action. This option only generates plans for queries that do not currently use the server’s default optimization goal.
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 only percentage is specified, absolute value defaults to 0.
-l limit
-m 5
Only users with sa_role and sso_role can run actions other than compare on QPTune.
