QPTune includes a panel called Tuning Tasks that displays existing tuning tasks on every qualifying server. A Wizard guides you through QPTune’s tuning cycle. The definition of a tuning task is stored on the client machine where ASEP runs, and may only be accessed on that machine.
QPTune includes several stages in the tuning cycle for applications or queries. For more information on QPTune’s cycle for application or query tuning, see “Using QPTune to tune queries or applications”.
To create a new tuning task for a server:
Connect to the server on which you wish to tune your query.
Click on your server name. You see the Tuning tasks tab.
If you do not see the Tuning Task tab, please check
that your environment variables are set correctly and that your
installation contains all the required files and directories.
Click on the Tuning Tasks tab, and then right-click in the window to bring up the “New”-> “TuningTask” menu item.
Select the “New”-> “TuningTask” menu item. The QPTune Wizard opens.
Alternately, you can bring up the Wizard using the “Tuning Task” creation button that is provided on the toolbar.
You must have sa_role and sso_role to
use the menu item and the creation toolbar button.
The QPTune wizard includes these screens corresponding to the different stages in tuning the Adaptive Server:
Setup:
Name and Configuration
Specify the task name and the configuration file associated with the task. When these are both specified, the Next and Finish buttons ar enabled. If the configuration file already exists, the wizard indicates this by displaying a note under the file name.
You may select the Verbose Mode option to generate more detailed output.
Server Configuration
You can view or edit server configuration commands issued during the different stages of running QPTune. Changes to the commands are written to the configuration file right before execution on the Comparison page.
Mode Selection
You may select different modes to run QPTune. All three pre-programmed modes are selected by default:
Decision Support System (DSS)
Online Transaction Processing (OLTP)
Mixed Workload (MIX)
To define a customized mode, click the Add button. To change the order of the modes, use the Up and Down buttons. Two or more modes must be selected for the tuning tasks in order to ensure at least two collected results for later comparison.
A customized mode is a collection of tuning parameters grouped under an optimization goal for a set of queries. The OK button is enabled only if a name, at least one rule, and the result file are specified.
To add or edit a rule, use the pop-up text input box. To delete a rule, use the Remove button.
Collect:
Application
You can specify an executable or a script file to include before the collection phase begins.
Collection
You can specify collection settings on this page. By default QPTune only collects optimization goal settings with no collection delay, and evaluates the average elapsed time for collection.
Compare:
Comparison
Specify comparison threshold setting (percentage and absolute values) and the output filename on this page.
Click Finish to save the task definition and the configuration file.
If you click Execute, QPTune executes all the specified modes, collects the metrics, and compares and saves the results into the output file. You may use the Preview button to list the commands that are about to be issued.
Results
This page displays the output of the tuning process. Comparison results depict the performance improvement if the best plans are chosen for each of the queries. The output XML file contains the best plans or optgoal settings for each of the queries.
You may also apply the fixes to the server: Click Fix to apply the best plans or optgoal setting to the queries. This generates entries in the sysqueryplans table for the queries that are being fixed.
Select Apply Default Optimization Goal if you want to apply the default optimization goal to the server during the Fix operation. The default optimization goal is the optgoal setting that most queries selected as their best optgoal during the Compare operation.
If Apply Default Optimization Goal is selected, subsequent Fix operations apply the best result to the rest of the queries that have not selected this default optgoal setting as their best optgoal.
If Apply Default Optimization Goal is not selected, the Fix operation is applied to all the queries in the result file.
