Configuration file

You can define custom modes in a configuration file. The QPTune installation includes a standard configuration file that contain some custom modes. The custom mode “_basic_” is reserved for “basic optimization”.

The configuration file for QPTune must include <start>, <start_stats>, <fix> and <end> sections. The <mode> section is optional.

The <start> section indicates the configuration settings for Adaptive Server before metrics are collected. For example:

 <start>
<!-- Recommended server settings -->
<start_config>sp_configure 'enable metrics capture', 1</start_config>
<start_config>sp_configure 'abstract plan dump', 1</start_config>

<!-- Clean up sysqueryplans & sysquerymetrics tables --> 

<start_config>sp_configure 'system table', 1</start_config>
<start_config>sp_metrics 'flush'</start_config>
<start_config>delete sysqueryplans where gid=1 or gid=2</start_config>

<!-- Optional settings that users can change or remove -->

<!-- <start_config>sp_configure 'enable literal autoparam', 1</start_config> ->
<!-- <start_config>sp_configure 'metrics elap max', 0</start_config> -->

<!-- Hint: sp_add_resource_limit can be added to limit resource usage  --> 
<!-- Specify a query plan group name to save all existing plans from ap_stdin -->
<!-- Existing plans from ap_stdout will be saved to the corresponding group name added with '_out'. -->

<save_plans_pre_start>pre_start_qpgroup</save_plans_pre_start>
</start>

The <end> section corresponds to the <start> section and includes the configurations setting to be applied after metrics are collected. For example:

<end>
<end_config>sp_configure 'enable metrics capture', 0</end_config>
<end_config>sp_configure 'abstract plan dump', 0</end_config>
<end_config>sp_configure 'system table', 0</end_config>
<end_config>sp_configure 'capture missing statistics', 0</end_config>

<!-- <end_config>sp_configure 'enable literal autoparam', 0</end_config> -->
<!-- <end_config>sp_configure 'metrics elap max', 0</end_config> -->
</end>

The <start_stats> section includes statistics settings. For example:

<start_stats>
<!-- Recommended server settings -->
<start_stats_config>sp_configure 'capture missing statistics',1</start_stats_config>
<!-- Reset counter of missing statistics -->
<start_stats_config>
sp_configure 'system table',1
</start_stats_config>
<start_stats_config>
delete sysstatistics where formatid=110
</start_stats_config>
</start_stats>

The <fix_stats>section includes:

<!-- The following set of configurations apply at "-A
fix" --> 
<fix>
<!-- Recommended server settings -->
<fix_config>sp_configure 'abstract plan load',1</fix_config>
<!-- Clean up sysqueryplans & sysquerymetrics tables -->
<fix_config>sp_configure 'system table', 1</fix_config>
<fix_config>sp_metrics 'flush'</fix_config>
<fix_config>delete sysqueryplans where gid=1 orgid=2</fix_config>
<!-- Optional settings that users can change or remove -->
<fix_config>sp_configure 'enable metrics capture',1</fix_config>
<!-- <fix_config>sp_configure 'enable literal autoparam',1</fix_config> -->
<!-- <fix_config>sp_configure 'metrics elap max',0</fix_config>-->
<!-- Specify a query plan group name to save all existing plans from ap_stdin -->
<!-- Existing plans from ap_stdout will be saved to the corresponding group name added with '_out'. -->
<save_plans_pre_fix>pre_fix_qpgroup</save_plans_pre_fix>
</fix>

The optional <mode> section allows users to specify custom optimization settings to one or more queries specified through another input file. The -M option of the start and collect actions specifies the mode setting. When the -M option specifies anything other than a standard optimization goal setting, QPTune treats the mode as customized, and retrieves the optimization goal and rules settings, for the indicated name, from the <mode> section of the configuration file. QPTune then applies the custom settings to the list of specified queries.