Updating SQL tuning settings

Edit online

SQL tuning settings influence the collection of top resource‑consuming SQL statements. The settings also affect how IBM® Z Database Assistant compares the performance of different access paths and resolves the regressed access paths. You can keep the default settings or tailor them to achieve the behavior you want.

About this task

The default behavior of IBM Z Database Assistant is to identify and collect the 20 SQL statements that consume the most CPU in every 1-hour interval for both static and dynamic SQL statements. You can adjust the number of top SQL statements to be collected and the collection interval. You can also selectively activate either static SQL statement or dynamic SQL statement only for the top SQL statement collection. You can also temporarily deactivate the top SQL statement data collection if needed.

Regarding the top SQL statement collection:
  • It is initiated after SQL tuning is started.
  • The top SQL collection time interval starts from the time SQL tuning starts.
  • The collected top SQL statements can be optimized later if the statements are eligible for explore.
Note: The following restriction applies to the collection of top SQL statements:
  • CONCENTRATE with literals statement

In determining the right mix of CPU vs. elapsed time weighting for your environment, an important factor to consider is the nature of your application. While a 50/50 balance of CPU vs. elapsed time weighting would work when the environment contains a broad mix of online and batch processes, if your environment contains a predominance of one or the other, you might want to change the setting. If the response time of your queries is important, as, for example, with an application that relies upon user responses (as opposed to a batch program), you might tend to give greater weight to elapsed time. In cases where you are running a batch process, where it might be more important to reduce CPU usage, you might want to weight CPU more heavily.

To reduce the possibility of unintended results, avoid setting either CPU or elapsed time weight to 100.

Procedure

To update the SQL tuning settings:

  1. Open Manage connections / Connections details / SQL tuning settings.

Top SQL data collection settings

  1. Click Top SQL data collection to display the top SQL data collection settings.
    1. To enable top SQL data collection, click the toggle to the on state.
    2. Select which statement types for which to collect top SQL data.
    3. Set the number of top queries and the interval for data collection.
  2. Click Apply to save your settings.
  3. Click the Db2 data collection tab.
  4. Stop SQL tuning by clicking the icon.
  5. Restart SQL tuning by clicking the icon.

Access path regression settings

  1. Click Access path regression settings to set the access path regression modes for static statements and dynamic statements.
  2. For static and dynamic statements, select a regression resolution mode from the drop-down menu in the appropriate section.
    The regression resolution mode options are:

    dba-sqlopt-db2-indexe

    • Resolve manually
    • Resolve IBM Z Database Assistant recommendations only
    • Resolve automatically
  3. Click Apply to save your settings.

Access path performance settings

  1. Click Access path performance settings to open the access path performance settings.
  2. Click Static statements or Dynamic statements to expand the access path performance settings for static statements or dynamic statements.
    1. Set the CPU consumption weighting % and Elapsed consumption weighting %.
    2. Enable Confidence level by clicking the toggle to the on state. When enabled, IBM Z Database Assistant will determine the degree of confidence in the result of access path comparisons. Confidence level is enabled by default.
    3. Set the Confidence level %, which is the threshold value below which IBM Z Database Assistant takes no action. The default threshold value is 90%. This means that an access path comparison that determines that a regression occurred does not cause IBM Z Database Assistant to take action to resolve the regression unless the confidence level is at least 90% that an access path regression has actually occurred.
    4. Set the Improvement performance threshold %, which is the smallest reduction in the weighted cost of the query that will still be considered an improvement. The default value is -10%, meaning that if the reduction in the weighted cost of the query is less than 10%, IBM Z Database Assistant takes no action.
    5. Set the Regression performance threshold %, which is the smallest increase in the weighted cost of a query that will be considered a regression. The default value is 10%, meaning that if the reduction in the weighted cost of the query is less than 10%, IBM Z Database Assistant takes no action.
  3. Click Apply to save your settings.

What to do next

The recommended next step is to create an SQL exploration schedule to automatically collect relevant performance data.