Generating and acting on recommendations for collecting statistics for query workloads that run on DB2 for Linux, UNIX, and Windows

The Workload Statistics Advisor enables database administrators to collect statistics that are relevant to the SQL statements in an entire query workload.

Before you begin

About this task

The Workload Statistics Advisor generates a number of RUNSTATS commands to collect necessary statistics for the tables that SQL statements in a query workload reference.

There are many situations where statistics are stale or conflict with each other. There are also situations where statistics might not have been collected. Collecting column-group statistics can be expensive. The Workload Statistics Advisor can decide which statistics are needed, so that you do not waste time and resources collecting statistics that you do not need to collect.

After the Workload Statistics Advisor generates RUNSTATS commands for collecting statistics, you can review the current state of the statistics and the problems that the advisor found in them. If you have the appropriate authorities on the database, you can run the RUNSTATS statements that you select.

Procedure

  1. Create a workload of the SQL statements that you want to tune.
    1. Capture the SQL statements that you want to tune. See Locations from which you can capture SQL statements to create query workloads that run on DB2 for Linux, UNIX, and Windows.
    2. After capturing SQL statements, click the Save All to Workload button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  2. Run the Workload Statistics Advisor.
    1. Click the Invoke Advisors and Tools icon, which is on the left end of the toolbar above the list of query workloads. The Run Workload Advisors page of the Invoke section opens.
    2. Optional: Modify values for the options for the Workload Statistics Advisor.
      Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Statistics tab to display the options that you can modify. For help with the options, click the Help icon in the Statistics section. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
    3. Click the Select What to Run button.
    4. In the Select Activities window, select the Statistics check box and click OK.
    5. In the Collect EXPLAIN Information window, specify values for the runtime environment of the SQL statements that are in the query workload. The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Statistics Advisor requires up-to-date information about the SQL statements. For more information, click the Help icon in the lower-left corner of the window.
    The Review Workload Advisor Recommendations page of the Review section opens. The Summary section on this page shows whether the Workload Statistics Advisor has new recommendations for the query workload.
  3. In the Review Workload Advisor Recommendations page, click the Statistics tab if there are new recommendations for RUNSTATS commands.
  4. Use these columns in the top table to judge which objects have statistics that are the most important to correct for improving the performance of the query workload.
    References to Table
    The number of SQL statements in the query workload that reference the table.
    Cardinality
    The number of rows that are in the table. If the cardinality for a table is not known, the value is -1.
    Cumulative Total Cost
    The cumulative cost of the statements that reference a table. This column can show whether the cumulative cost of the SQL statements that reference a table is high relative to the cumulative cost of the statements that reference other tables. If the cost is higher, running RUNSTATS on the table could help to reduce this cost.
    Collection Timestamp
    The date and time that statistics for the table were last collected.
  5. For the most important objects, look up the problems that the Workload Statistics Advisor identified.
    Missing
    Indicates whether one or more statistics for the table or indexes for the table are missing. Click on the row and then look in the "Details for the selected table" section to see the statistics for the table. Look for columns with the value MISSING.
    Conflicting
    Indicates whether two or more statistics for the table, columns, or indexes on the table are in conflict. If the value for a table is YES, click on the row for the table and then look in the Conflicts section in the Table Details section. You can also look in the other sections in the "Details for the selected table" section for columns with the value CONFLICTING.
    Obsolete
    Indicates whether the statistics for the table are obsolete. Whether statistics are obsolete is determined by the age threshold that you set in the options for the Workload Statistics Advisor.
    Cardinality Unknown
    Indicates whether the cardinality for the table is unknown, which is the case when the cardinality is 0 and the statistics for the table are obsolete.
    Obsolete Statistical View
    For statistical views, this column indicates whether the statistics for a statistical view are older than the statistics for the base tables for the view.
  6. For each of the most important objects, find out whether there are columns or column groups with statistics in the catalog but that are not referenced by SQL statements in the query workload. Look in the bottom of the Column and Column Groups sections. You need to find out this information because the Workload Statistics Advisor generates two types of RUNSTATS commands that you can later choose to run:
    Merged
    Merged RUNSTATS commands contain the recommended settings for fixing table statistics and update the statistics for either a subset of or all of the non-referenced columns or column groups that have statistics in the catalog.
    Running this type of command requires more resources because of the extra columns.
    Advisor-generated
    Advisor-generated RUNSTATS commands contain the recommended settings for fixing table statistics and drop the statistics for the non-referenced columns and column groups.
  7. Select the check box next to each object that you want to run the RUNSTATS command for.
  8. 5. Click the View RUNSTATS button. The Review Recommended RUNSTATS Commands wizard opens.
  9. Follow the instructions in the wizard to run or save the merged or advisor-generated RUNSTATS command for each of the objects that you selected.

What to do next

Get recommendations for statistical views from the Workload Statistics Advisor.

Feedback