The Statistics Advisor recommends RUNSTATS commands that
you can use to collect or repair statistics. The collected statistics
might help the optimizer to choose a more efficient access path. However,
it is possible for the optimizer to choose the same access path even
after you collect the recommended statistics.
About this task
In many situations, statistics can be stale or conflict
with each other. There are also situations where statistics might
not have been collected for a table or index. Collecting column-group
statistics can be expensive. The 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.
Procedure
To get and act on recommendations for collecting statistics:
- Capture the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select one of the captured statements
and click the Invoke Advisors and Tools button.
The Run Single-Query Advisors and Analysis Tools page
in the Invoke section opens.
- If you need to set the schema
to use for unqualified objects that the SQL statement references,
or (if the statement runs on DB2 for z/OS) change the SQL ID, specify
these values in the Schema and SQLID fields above the SQL statement
on this page. For information about setting global defaults for schemas
and SQL IDs, see Setting the schema to use for unqualified
objects, the SQL ID, or both.
- Optional: Modify values for the options for
the Statistics Advisor. Click Set Advisor Options on
the left side of the Invoke section. Then,
click the Statistics Advisor tab to display
the options that you can modify. After you are finished, in the left
side of the Invoke section click Run
Advisors and Analysis Tools to return to the Run
Single-Query Advisors and Analysis Tools page.
- Click the Select What
to Run button.
- In the Select Activities window, select
the Statistics check box. Then, click OK. After the Statistics Advisor runs, the Review Advisor
Recommendations page of the Review section opens. The Summary section
shows whether there are any recommendations for fixing statistics.
- High
- Recommendations of this type indicate that important statistics
are missing or obsolete, or that conflicts exist among the statistics.
- Maintenance
- Recommendations of this type have a low priority, and indicate
that the catalog statistics are complete and accurate. The recommendation
provides a RUNSTATS job that you might want to include periodically
in your maintenance cycle to maintain the health of the relevant statistics.
- In the Review Single-Query Advisor Recommendations page,
double-click the Statistics row in the Summary table.
- For DB2 for z/OS® only: Choose whether to
run the Repair version or the Complete version of the recommended
RUNSTATS commands.
- Repair version
- This version of the RUNSTATS commands repairs the problems that
the Statistics Advisor found. Run this version to conserve time and
CPU resources.
- Complete version
- This version of the RUNSTATS commands collects a full set of statistics
for the objects that are related to the SQL statement. In the process
of collecting the full set, these commands repair any problems that
the Statistics Advisor found.
- In the Statistics Advisor Report section, review the findings
about the statistics for the referenced tables. There are
two different versions of the report, which you can select by clicking
the left-most icons in the toolbar.
- Summary report
- Click the Summary report icon
(the first icon in the toolbar above the RUNSTATS commands) to display
only the problems that the Statistics Advisor found and that the RUNSTATS
commands fix.
- Detailed report
- Click the Detailed report icon
(the second icon in the toolbar above the RUNSTATS commands) to display
a report of all of the statistics for the objects that the SQL statement
references. The report shows the problematic statistics together with
the non-problematic statistics.
- Look in the Conflicts Detail section to learn of any conflicts
that the advisor found in the statistics.
- Select any of these options:
Note: Before you
select any of the options to run RUNSTATS commands, ensure that you
have the appropriate authorities and privileges on the data server.
- Run
- Runs all of the listed RUNSTATS commands.
- Run and Save to History of Recommended RUNSTATS Commands
- Runs all of the listed RUNSTATS commands and
saves them for future reference.
- Run and Save in Statistics Profiles
- Runs all of the listed RUNSTATS commands.
Each command generates and stores a specific statistics profile in
the system catalog tables.
- For DB2 for z/OS: Retrieve RUNSTATS Commands from the Database
Server
- Displays the RUNSTATS commands
that the Statistics Advisor recommended previously for the objects
that the SQL statement references, if you chose to save those previous
recommendations to the history of recommended RUNSTATS commands.
- For DB2 for Linux, UNIX,
and Windows: Retrieve Statistics
Profiles
- Retrieves the RUNSTATS commands that are
stored in the statistics profiles for the current tables. These commands
are displayed to the right of the current recommended commands.
- For DB2 for z/OS: Save in History of Recommended RUNSTATS
Commands
- Saves the listed RUNSTATS commands on the database. If you retune the same SQL statement, or tune another
statement that references the same objects, you can display the RUNSTATS
commands that the Statistics Advisor recommended previously. You can
then compare the current recommendations with the previous recommendations.
- For DB2 for Linux, UNIX,
and Windows: Save in Statistics
Profiles
- Saves the settings for all listed RUNSTATS commands
in statistics profiles for the tables that the commands are for.
- DB2 10 for z/OS only: Create a RUNSTATS Profile For Referenced
Tables
- Saves the options in the selected
RUNSTATS command as a RUNSTATS profile for the corresponding tables.
A RUNSTATS profile is a saved set of options for the RUNSTATS utility
that apply for a particular table. DB2 uses RUNSTATS profiles for
autonomic statistics maintenance. You can also use RUNSTATS profiles
to quickly invoke the RUNSTATS utility with a predefined set of options.
- DB2 10 for z/OS only: Update the RUNSTATS Profile for Referenced
Tables
- Update the current RUNSTATS profile
for the referenced tables with the options in the selected RUNSTATS
commands. A RUNSTATS profile is a saved set of options for the RUNSTATS
utility that apply for a particular table. DB2 uses RUNSTATS profiles
for autonomic statistics maintenance. You can also use RUNSTATS profiles
to quickly invoke the RUNSTATS utility with a predefined set of options.
What to do next
Before moving on to other advisors, run the Statistics Advisor
again, and collect the recommended statistics until you have resolved
all high recommendations. The other single-query advisors rely on
accurate statistics to make recommendations. You might also want to
generate a new access plan graph and compare it to the original access
plan graph to learn whether the access path has changed.
To run
the Statistics Advisor again, follow these steps:
- Ensure
that the local cache of the system catalog for the connected database
or subsystem is refreshed.
- Click the Invoke tab on the left side of
the workflow assistant.
- On the Run Single-Query Advisors and Tools page,
select the Re-explain the query option.
- Click the Select What to Run button.
- In the Select Activities window, select the Statistics option
and click OK.