# US 10,372,578 B2 · Dynamically adjusting statistics collection time in a database management system > Agents: this data is available as JSON from the patentgrep API and MCP server — see https://patentgrep.com/llms.txt - Number: US 10,372,578 B2 - Document ID: US10372578B2 - Status: Granted - Kind: B2 - Application number: 14/672,858 - Filed: 2015-03-30 - Published: 2016-10-06 - Granted: 2019-08-06 - Earliest claimed: 2015-03-30 - Assignees: International Business Machines Corporation - Applicants: International Business Machines Corporation - Inventors: Rafal P. Konik; Roger A. Mittelstadt; Brian R. Muras; Chad A. Olstad - Web page: https://patentgrep.com/patent/US10372578B2 ## CPC - G06F 11/302: {where the computing system component is a software system} - G06F 11/3096: {wherein the means or processing minimize the use of computing system or of computing system component resources, e.g. non-intrusive monitoring which minimizes the probe effect: sniffing, intercepting, indirectly deriving the monitored data from other directly available data} - G06F 11/3409: {for performance assessment} - G06F 11/3452: {Performance evaluation by statistical analysis} - G06F 11/3466: {Performance evaluation by tracing or monitoring} - G06F 16/217: {Database tuning (G06F16/2282 takes precedence; database performance monitoring G06F11/3409)} - G06F 16/2379: {Updates performed during online database operations; commit processing} - G06F 16/24545: {Selectivity estimation or determination} - G06F 2201/80: Database-specific techniques - G06F: ELECTRIC DIGITAL DATA PROCESSING (computer systems based on specific computational models G06N) ## Abstract Each of one or more commit cycles may be associated with a predicted number of updates. A statistics collection time for a database table can be determined by estimating a sum of predicted updates included in one or more commit cycles. Whether the estimated sum of predicted updates is greater than a first threshold may be determined. In addition, a progress point for a first one of the commit cycles can be determined. A time to collect statistics may be selected based on the progress point of the first commit cycle. ## Claims ### Claim 1 A computing device for determining a more accurate statistics collection time for a database table, comprising: a memory to store a database and a database management system, the database management system having a statistics collection component, the statistics collection component including a data structure to store data associated with one or more commit cycles; a processor to cause the statistics collection component to perform operations comprising: estimating a sum of predicted updates included in one or more commit cycles, wherein each of the commit cycles is associated with a predicted number of updates, wherein the estimating the sum of predicted updates occurs before the end of the first phase of a given commit cycle of the one or more commit cycles; determining whether the estimated sum of predicted updates is greater than a first threshold, wherein the determination is based on tracking the progress of uncommitted updates occurring in the first phase of the given commit cycle; determining a progress point for a first one of the commit cycles; and selecting a time to collect statistics based on the progress point of the first commit cycle, wherein the selected time to collect statistics provides increased accuracy by selecting a time that reflects the number of updates of uncommitted records being greater than the predicted number of updates. ### Claim 2 (depends on 1) The computing device of claim 1, the processor further causing the statistics collection component to determine: whether the first commit cycle includes a predicted number of updates that is greater than a second threshold, wherein the determining of a progress point for the first commit cycle is performed in response to determining that the first commit cycle includes a predicted number of updates that is greater than the second threshold. ### Claim 3 (depends on 1) The computing device of claim 1, wherein the determining of a progress point for the first commit cycle includes determining whether the first commit cycle has not yet begun, and wherein the selecting a time to collect statistics is also in response to determining that the first commit cycle has not yet begun, and wherein the selecting a time to collect more accurate statistics includes selecting the current time. ### Claim 4 (depends on 1) The computing device of claim 1, wherein the determining of a progress point for the first commit cycle includes: determining that the first commit cycle has completed more updates than a lower threshold of pending updates, determining that the first commit cycle has completed fewer updates than an upper threshold of pending updates, in response to determining that the first commit cycle has completed more updates than a lower threshold of pending updates and determining that the first commit cycle has completed fewer updates than an upper threshold of pending updates, determining whether an age of a current set of statistics is less than or equal to an staleness threshold, and selecting a future time as a time to collect statistics in response to determining that the age of the current set of statistics is less than or equal to the staleness threshold. ### Claim 5 (depends on 1) The computing device of claim 1, wherein the determining of a progress point for the first commit cycle includes: determining that the first commit cycle has completed more updates than a lower threshold of pending updates, determining that the first commit cycle has completed fewer updates than an upper threshold of pending updates, in response to determining that the first commit cycle has completed more updates than a lower threshold of pending updates and determining that the first commit cycle has completed fewer updates than an upper threshold of pending updates, determining whether an age of a current set of statistics is greater than an staleness threshold; and selecting a current time as a time to collect statistics using sampling in response to determining that the age of the current set of statistics is greater than the staleness threshold. ### Claim 6 (depends on 1) The computing device of claim 1, wherein the determining of a progress point for the first commit cycle includes: determining that the first commit cycle has completed more than an upper threshold of pending updates, and selecting a future time as a time to collect statistics, wherein the future time is a time when the first commit cycle has completed. ### Claim 7 (depends on 1) The computing device of claim 1, wherein the estimating of a sum of predicted updates included in one or more commit cycles includes: monitoring the one or more commit cycles to determine whether a number of updates for a first commit cycle diverges from a predicted number updates for the first commit cycle, and revising a sum of predicted updates associated with the one or more commit cycles in response to determining that the first commit cycle diverges from the predicted number of updates for the first commit cycle. ### Claim 8 (depends on 1) The computing device of claim 1, wherein the determining a progress point for a first one of the commit cycles further comprises: determining a respective progress point for each a plurality of the one or more commit cycles, and sorting the plurality of commit cycles into a first order according to a predicted number of updates for each commit cycle, wherein the determining of a respective progress point for each of the plurality of commit cycles are made according to the first order. ### Claim 9 A computer program product for determining a statistics collection time for a database table, the computer program product comprising a computer-readable storage medium having program instructions embodied therewith, the program instructions executable by a processor to cause the processor to: estimate a sum of predicted updates included in one or more commit cycles, wherein each of the commit cycles is associated with a predicted number of updates, wherein the sum of predicted updates includes a probability that one or more commit cycles will be rolled back, and wherein the estimating the sum of predicted updates occurs before the end of phase one of the one or more commit cycles; determine whether the estimated sum of predicted updates is greater than a first threshold, and wherein the determining further includes a rollback threshold related to the probability that one or more commit cycles will be rolled back; determine a progress point for a first one of the commit cycles; and select a time to collect statistics based on the progress point of the first commit cycle, wherein the accuracy of the collected statistics is increased from collecting records from the database that were affected due to the probability that the commit cycle was rolled back in phase two of the commit cycle being greater than the rollback threshold. ### Claim 10 (depends on 9) The computer program product of claim 9, wherein the determining of a progress point for the first commit cycle includes: determining whether the first commit cycle has not yet begun, and selecting a current time as a time to collect statistics in response to determining that the first commit cycle has not yet begun. ### Claim 11 (depends on 9) The computer program product of claim 9, wherein the determining of a progress point for the first commit cycle includes: determining that the first commit cycle has completed more than an upper threshold of pending updates, and selecting a future time as a time to collect statistics, wherein the future time is a time when the first commit cycle has completed. ### Claim 12 (depends on 9) The computer program product of claim 9, wherein the estimating of a sum of predicted updates included in one or more commit cycles includes: monitoring the one or more commit cycles to determine whether a number of updates for a first commit cycle diverges from a predicted number updates for the first commit cycle, and revising a sum of predicted updates associated with the one or more commit cycles in response to determining that the first commit cycle diverges from the predicted number of updates for the first commit cycle. ### Claim 13 (depends on 9) The computer program product of claim 9, wherein the program instructions executable by the processor further cause the processor to: perform, in response to the selected collection time, the statistics collection by accessing one or more tables of the database. ### Claim 14 (depends on 1) The computing device of claim 1, wherein the processor further performs operations comprising: performing, in response to the selected collection time, the statistics collection by accessing one or more tables of the database. ### Claim 15 (depends on 1) The computing device of claim 1, wherein the processor further performs operations comprising: sampling, in response to the selected collection time, one or more statistics of the database table. ### Claim 16 A computing device for providing increased query performance in a database management system, the computing device comprising: a memory to store a database containing one or more tables of records and to store a database managements system for manipulation of the tables; a processor to perform database operations of the database management system comprising: estimating a sum of predicted updates included in one or more commit cycles, wherein each of the commit cycles is associated with a predicted number of updates; determining whether the estimated sum of predicted updates is greater than a first threshold; determining a progress point for a first one of the commit cycles; determining the age of the current set of statistics is greater than a staleness threshold; selecting a current time to collect statistics based on the progress point of the first commit cycle; and collecting, using sampling based on the selected current time and based on the determining the age is greater than the staleness threshold, statistics of a subset of data in the database.