It is well known that inferior statistics cause query performance problems. It is relatively easy to identify stale, out-of-date statistics and missing statistics, but poor quality statistics can be harder to identify: such as inconsistencies between tables and indexes, primary-key/foreign-key relationships and so on.
Inconsistencies in statistics are usually a result of not following recommended approaches, but it is not always easy to strictly adhere to these for a number of reasons. For example, Oracle continuously enhances statistics gathering features but enhancements can be overlooked post-upgrade (a good example is the recommendation to use AUTO_SAMPLE_SIZE rather than fixed percentages).
DBAs may use legacy scripts to gather statistics manually so that there is a reluctance to change “proven” procedures. Sometimes statistics gathering can be overlooked and statistics might not be maintained during batch processing and there may be a perceived lack of time in batch windows. There are many “inherited” systems too, where nobody understands the scripts that are used to maintain statistics.
From Oracle Database 12 Release 2, a feature called the Optimizer Statistics Advisor is available.
The goal of the advisor is to analyze how statistics are gathered, validate the quality of statistics already gathered and check the status of auto stats gathering (for example, checking for successful completion).
To achieve this, it examines the data dictionary with respect to a set of rules. Where exceptions to the rules are found, findings may be generated and these, in turn, may lead to specific recommendations. The advisor will generate a report that lists findings (with the associated “broken” rule), and then list specific recommendations to remedy the situation. Finally, the recommendations can be implemented using a set of actions. Actions can be output in the form of a SQL script or they can be implemented automatically.
No comments:
Post a Comment