Statistics Advisor

The Statistics Advisor gives expert advice about collecting statistics to improve the performance of your SQL statements.

Database statistics are important for query performance because most database management systems have a query optimizer that uses statistics about the data in a database to determine the most efficient execution strategy for processing SQL statements. The statistics commonly include information about the number of rows in a table and the number of distinct values, the most frequent values, and the distribution of values in a column. The optimizer uses these statistics to compute the cardinality, or number of rows processed, for each step in an access plan. Consequently, missing, outdated, or conflicting statistics can lead the optimizer to inaccurate estimates for the costs of the steps in a query plan. The result is often that the optimizer selects a poor access plan that causes long query processing times.

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.

Each recommendation has a priority level. The priority levels have the following meanings:
High
Recommendations of this type indicate that important statistics are missing, obsolete, or that conflicts exist among the statistics.
Maintenance
Recommendations of this type have the lowest 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.

If you have the required authorities and privileges to invoke the RUNSTATS utility on the data server, you can capture the statistics directly from the query tuner client. You can also copy or save the recommended RUNSTATS statements to a file, or save it into a profile table on the data server to be retrieved and run later or by another member of your team. The RUNSTATS command that is stored in statistics profile is also shown for comparison. If you run and save the RUNSTATS command every time, you can always see the last RUNSTATS command that was run for a specific table.

Before moving on to other advisors, run the Statistics Advisor again, and capture 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.


Feedback