QPTune reference information

Description

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.

Syntax

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

Parameters

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

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

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

Permissions

Only users with sa_role and sso_role can run actions other than compare on QPTune.