As a database administrator, I want to improve the collection and reporting of data distribution metrics by CRUISE so that I can identify potential problems in the database more easily and quickly and remediate them in a timely manner to enhance the performance of mission-critical applications. Database performance issues can result from flaws in the database design, or inefficient distribution of the data based on the selected algorithm, keys, choice of indexing scheme or suboptimal coding in the applications that access the database. Sometimes the issues arise not from the original design but rather because of database growth and unanticipated changes over time, so a DBA needs to monitor databases with periodic collection and review of the statistics provided by tools such as CRUISE.
Currently, CRUISE reports chain lengths ranging from 1 to 9, and everything else is added to the greater than 9 chains or “M” bucket. This may have been a reasonable assumption when 3 blocks were considered optimal and anything over 10 chains was excessive. These rules of thumb no longer apply. Even 100 chains may be optimal with block indexing and thousands with B+tree indexes.
Without user written utilities it would not be easy to tell how well the distribution algorithm is working and how lumpy the database might be. Over time, the growth pattern may not align with the initial design assumptions. What may have been a good and reasonable distribution at the start may end up being a poor choice because the growth did not occur as expected. For these reasons, users need a finer grained and flexible approach to assess the state of the database and validate if the initial design still meets current requirements.
Performance measurements like Data Collection and Realtime Monitoring (RTMC) help identify the “what” and “which” of application performance issues. Enhanced CRUISE metrics can help by providing more insights into the “why” and “where” in the database the problem is that is causing the deficient performance and slow response by identifying reason for the bottlenecks.
Databases are a critical requirement of modern business. Deteriorating performance can slow applications and services and have negative consequences for the business. The enhanced CRUISE statistics will provide better visibility to potential problems. The user can remediate to improve system availability and stability, optimized performance, and uninterrupted service for users and customers.
The idea is to increase the no. of buckets and make the ranges or increments more flexible and dynamic and allow the total no. of buckets to be customizable by the user. This is the most important and critical requirement. As an example, a user may decide to have 12 categories with increments of 5. The chain count buckets would go from 1 chain to 6 or fewer but more than 1, 11 or fewer but more than 6, 16 or fewer nut more than 11 etc. with M being than 66. The no. of buckets can have a fixed upper limit to keep the coding and statistical data manageable.
Add a new category similar to “M/”, say “U/” where U is a number of subfiles that exceed the value defined in the DBDEF for LONGCHAIN or it could be a new value the user can set up, so it could be anywhere from higher than M but less than LONGCHAIN, to identify chains nearing the LONGCHAIN limit.
The solution should allow for comparison between runs, e.g., the most current and previous run. The format would remain the same, except the bucket would contain the deltas or difference in counts between the two runs, with negative values for decreases.
The solution should allow for the designation of a baseline which would be permanently retained in the statistics database until replaced by a user through a functional entry. The DBA can then compare this baseline to the most recent run to track database growth and distributions changes since the baseline was established.
How these categories are set up and defined could be in a new DBDEF sub-table. The other option is to make them part of the CRUISE table definition. That would make it possible to vary the specifications from run to run for maximum flexibility and a deeper dive into a specific problem situation.
