The Workload Statistics Advisor can recommend new statistical
views and changes to existing ones. Statistical views help the DB2
optimizer to estimate cardinality better when SQL statements have
complex predicates, relationships among complex predicates, and relationships
across tables.
About this task
After the Workload Statistics Advisor generates recommendations
for statistical views, you can review the recommendations and details
that can help you decide which recommendations you want to follow.
Procedure
- In the Manage section, open
the Manage and Tune Workloads page.
- Select the query workload and 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.
- Optional: Modify values in the Statistical
View Constraints section of 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 and then the Statistical
View Constraints tab to display the options that you can
modify. For help with the options, click the ? icon. After you are
finished modifying the values for options, click Run Workload
Advisors on the left side of the Review section.
- Ensure that the Re-collect EXPLAIN
information before running workload advisors option is
selected.
- Click the Select What
to Run button.
- In the Select Activities window, select
the Statistical views check box and click OK.
- 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.
- In the Review Workload Advisor Recommendations page,
click the Statistical views tab if there are
new recommendations for statistical views.
- Review the list of recommended statistical views. These
columns appear in the table that lists the recommendations.
- State
- Shows whether the view is a new recommendation or an existing
view
- Tables in Definition
- The number of tables that the view is defined on.
- Affected Statements
- The number of statements for which the view improves performance.
- Estimated Execution Time
- The estimated sum in timerons of the execution times for all of
the affected statements, if the recommendation is followed.
- Estimated RUNSTATS Time
- The estimated time in minutes that is required to run the RUNSTATS
command that gathers statistics for the view.
When you click a recommendation, you can view these
details in the Statements Assisted by the Statistical View section
below the list.- Statement Text
- The text of the SQL statement
- Number of Executions
- The number of times that the SQL statement runs as part of the
query workload that it is in.
- Time to Execute Once
- The length of time in timerons that the statement takes to run.
- Current Execution Time
- The length of time in timerons that the statement takes to run
multiplied by the number of times that the statement runs.
The Tables Defining the Statistical View section
shows these details of the highlighted recommendation.- Owner
- The qualifier of the table.
- Table Name
- The name of the table.
- Cardinality
- The number of rows that are in the table. A value of -1 means
that the cardinality is unknown.
- Last RUNSTATS Execution
- The last time that statistics were collected for the table.
- Times Used by Existing and Recommended Statistical Views
- The number of existing and recommended statistical views that
include this table in their definitions.
- Select the check box next to each statistical view that
you want to create or modify.
- Right-click anywhere in the table of statistical views
and select View Scripts for Creating or Modifying the Selected
Statistical Views.
- In the Review Recommended Scripts for Statistical Views
wizard, run the scripts to follow the recommendations that you selected.