Method, system, and program for collecting statistics of data stored in a database

FIELD OF THE INVENTION

p-0002The invention relates to database management systems, particularly to a data processing system implemented method, a data processing system and an article of manufacture for collecting statistics associated with data stored in a database.

BACKGROUND

p-0003Many database management systems (DBMS) are configured to collect various statistics for data tables. Such statistics may be later used by a query optimizer for selecting a plan of execution for operation on the database from a plurality of plans for optimizing query access of data or for other handling of the data tables. Database administrators (DBA) often configure their databases to collect statistics using hard coded scripts run during system maintenance hours to avoid disrupting operation of the database for users. In many database management systems not having a DBA, statistics are often not collected.

p-0004In addition to DBS-controlled statistics collection, automated statistics collection is available in some DBMS. However, currently known automated operations may result in severe impact to the performance of the database. For example, automated statistics collection operations may be initiated during heavy production periods impacting user response.

p-0005Therefore a statistics collection solution that addresses at least one of these needs is desired.

SUMMARY

p-0006The present invention relates to collecting statistics automatically for data in a database. In accordance with an aspect of the invention, there is provided a method for automated statistics collection for data in a database. The method comprises determining a likelihood that statistics for the data have changed; and collecting statistics for the data in response to the likelihood. The step of determining may comprise sampling a portion of the data to generate a sample result; and comparing the sample result to statistics previously collected for the data. The likelihood is thus responsive to the comparing. The step of determining may comprise examining metadata maintained for the data whereby the likelihood is responsive to the examining. Metadata may comprise at least one of: an indicator of a change in a size of the data; an indicator of a change in a content of the data; and an indicator of a recent use of the data.

p-0007Preferably, in accordance with a feature of the method, the data is scheduled for periodic automated statistics collection and the step of determining is responsive to the scheduling. Scheduling may be modified in response to the likelihood that statistics for the data have changed or in response to a change to the statistics collected for the data.

p-0008When collecting statistics, the collecting may be throttled to reduce a consumption of resources in response to a contending need for the resources. Further, collecting statistics may comprise rejecting a resource lock for the collecting in response to a contending need for the resource lock. When the data is stored in tables formed from columns and rows, the steps of determining and collecting may be performed for individual tables.

p-0009Other aspects of the invention such as apparatus, computer system, database management system and computer readable media aspects, among others, will be apparent to those of ordinary skill in the art.

p-0010Advantageously, indicators of the likelihood that statistics have changed may be useful to trigger automated statistics collection. Tables having statistics that change significantly over time may be collected more often than statistics of tables that are stable. A preferred model is provided to facilitate the collection of statistics that are more relevant: a table is scheduled for statistics collection in accordance with observed patterns of table activity; a table is considered for collection if it meets a threshold level of activity; and a table is sampled to predict whether the statistics to be collected have changed. When all three criteria are met, automated statistics collection is indicated.

p-0011If any of the evaluated criteria (e.g. a low level of table activity or stable sample statistics) indicate a low likelihood that statistics have changed, statistics are not collected for the table and the results of the evaluations may be reflected in the observed patterns of table activity for purposes of future scheduling.

p-0012The above model seeks to minimize the number of statistic maintenance tasks by ensuring that collected statistics are very likely to have changed. An approach which only considers a level of table activity may unnecessarily collect statistics that have not changed. Indeed, if the statistics have changed, a high level of table activity must be satisfied. However, a high level of activity alone itself does not ensure that statistics have changed.

p-0013When collecting statistics, two additional methods of operation may be useful to reduce system overhead. Utility throttling may be employed to adaptively reduce the aggressiveness of the statistics collection operations and minimize impact on a database user's response experience by reducing resource consumption (e.g. processor (CPU time) and input/output resources). Lock contention avoidance, whereby all resource locks obtained by the automated statistics collection operations have a lower priority than such locks obtained for other workload operations (e.g. user initiated activity), may also be performed to lessen impact on the user experience. If lock wait or deadlock situations occur involving locks held by automated statistics collection operations, such locks are rejected and statistics collection rolled back in preference to contending needs for the resource locks.

p-0014In an aspect of the invention, there is provided a data processing system implemented method of collecting statistics associated with data stored in a database, the database operatively coupled to a data processing system, the data processing system implemented method including determining a likelihood that currently computed statistics associated with the data have changed, and collecting updated statistics for the data in response to the determined likelihood.

p-0015In another aspect of the invention, there is provided a data processing system for collecting statistics associated with data stored in a database, the database operatively coupled to the data processing system, the data processing system including a determining module for determining a likelihood that currently computed statistics associated with the data have changed, and a collecting module for collecting updated statistics for the data in response to the determined likelihood.

p-0016In another aspect of the invention, there is provided an article of manufacture for directing a data processing system to collect statistics associated with data stored in a database, the database operatively coupled to the data processing system, the article of manufacture including a program usable medium embodying one or more instructions executable by the data processing system, the one or more instructions including data processing system executable instructions for determining a likelihood that currently computed statistics associated with the data have changed, and data processing system executable instructions for collecting updated statistics for the data in response to the determined likelihood.

BRIEF DESCRIPTION OF THE DRAWINGS

p-0017The present invention will now be described in greater in reference to the figures referred to below wherein:

p-0018<figref idrefs="DRAWINGS">FIG. 1</figref> is flowchart of operations for the automated collection of statistics for data in a database in accordance to an embodiment of the present invention;

p-0019<figref idrefs="DRAWINGS">FIG. 2</figref> is a flowchart detailing a portion of the operations shown in <figref idrefs="DRAWINGS">FIG. 1</figref>, namely operations to determine a likelihood that statistics have changed for tables scheduled for automated statistics collection;

p-0020<figref idrefs="DRAWINGS">FIG. 3</figref> is a flowchart detailing a portion of the operations shown in <figref idrefs="DRAWINGS">FIG. 1</figref>, namely operations to collect statistics, in accordance with an embodiment of the invention, for those tables indicating a likelihood of changed statistics;

p-0021<figref idrefs="DRAWINGS">FIG. 4</figref> is a flowchart detailing a portion of the operations shown in <figref idrefs="DRAWINGS">FIG. 1</figref>, namely operations to update scheduling information in response to the statistics collected;

p-0022<figref idrefs="DRAWINGS">FIGS. 5 and 6</figref> are schematic diagrams of an apparatus for the automated collection of statistics in accordance with a first and a second embodiment of the invention.

DETAILED DESCRIPTION

p-0023In the following description of the embodiments, reference to the accompanying drawings are by way of illustration of an example by which the invention may be practiced. It will be understood that other embodiments may be made without departing from the scope of the invention disclosed.

p-0024The present invention will now be described with reference to <figref idrefs="DRAWINGS">FIG. 1</figref>. <figref idrefs="DRAWINGS">FIG. 1</figref> illustrates general operations <b>100</b> for automated statistics collection in accordance with an embodiment of the invention.

p-0025In accordance with features of the present embodiment, a 3-tier model to detect tables of data (typically stored in rows and columns) in a database that require statistics collection is shown. The 3-tier model may be supplemented with scheduling and prioritizing operations for periodically considering a particular table for statistics collection and ordering the collection of statistics for those scheduled tables that indicate a likelihood that their respective statistics have change and thus need collecting.

p-0026The first tier of the model examines change activity on tables since the last time statistics the tables' respective statistics were collected. Table metadata maintained for the table such as a count of row changes (updates, deletions insertions), current and prior table size, etc. may be examined. The second tier examines table distribution changes (e.g. column histograms), for example, through sampling table data. A third tier collects statistics in an asynchronous low overhead manner, exploiting utility throttling and lock contention avoidance to minimize an impact on user experience for contending resource needs.

p-0027With reference to <figref idrefs="DRAWINGS">FIG. 1</figref>, operations <b>100</b> begin at start <b>102</b>, for example, in accordance with operations for periodic automated statistics collection. At step <b>104</b>, a list of tables indicated by the scheduling operations are assembled. At step <b>106</b>, the list is pruned, removing those tables in response to a determined likelihood that statistics have changed and as discussed more fully with reference to <figref idrefs="DRAWINGS">FIG. 2</figref>. The tables in the pruned list are ranked to order the statistics collection (step <b>108</b>) and statistics collected (step <b>110</b>). At step <b>112</b>, table scheduling information may be updated in response to the collected statistics. Operations end at step <b>114</b>.

p-0028<figref idrefs="DRAWINGS">FIG. 2</figref> illustrates operations of step <b>106</b> of <figref idrefs="DRAWINGS">FIG. 1</figref>. From start <b>202</b>, for each table in the list, an examination is made to determine whether the table has been recently used (step <b>204</b>). This optional step indicates a preliminary likelihood of that statistics have changed. Recent use may be determined in a variety of manners. For example, a database management system managing the table may be examined for artefacts of recent use such as the existence of table structures in a memory of the database management system.

p-0029If the table has not been accessed recently, via No branch to step <b>206</b>, operations continue to prune the table from the list, removing the table from further current collection of statistics. If the table has been accessed, via Yes branch to step <b>208</b>, operations examine one or more measures of table activity (e.g. metadata for the table) with a view to determining the extent of recent activity in the form of significant changes to the table. Metadata that may be conveniently maintained for the table by a database management system or other means for monitoring table access may include a count of changes to rows of the table. Updates, insertions and deletions may be counted in response to user queries for example. The count may be normalized relative to table size. A threshold for changes may be defined (e.g. 10% of the number of table rows) to determine significant changes.

p-0030Similarly, a record of current and prior table size may be maintained to provide an indication of major table changes. If significant table changes have not occurred the table may be pruned at step <b>206</b> via No branch from step <b>208</b>. If significant changes have occurred, via Yes branch to step <b>210</b>, the table may be sampled to generate sampling results, for example, sample statistics similar to those defined for collection over the entire table. At step <b>212</b> the results are examined for significant changes relative to previously collected statistics for the table.

p-0031The sample statistics are compared to the table statistics, and if the two sets are found to be similar (the difference may be measured by combining several tests), then statistics collection is postponed for the table. Only when the sample and previous statistics sets are different are table statistics collected and updated for the table. Histograms collected for each column (collecting histograms is de-facto standard for all database servers) may be used for comparison purposes. However, the present invention does not put any restrictions on the methods used to decide whether the sampled and previous statistics are different or not. For example, table cardinality, the set of most frequent values could be potentially used to determine the differences.

p-0032If the sample statistics do not indicate a likelihood that the statistics have changed, via No branch to step <b>206</b>, the table is pruned. Otherwise, via Yes branch to step <b>216</b>, operations end and the table remains in the list for current statistics collection.

p-0033From step <b>206</b>, scheduling information for the table may be updated at step <b>214</b> in response to the likelihood that statistics have not changed. This scheduling information may be used to schedule the table less frequently in accordance with operations for scheduling tables.

p-0034<figref idrefs="DRAWINGS">FIG. 3</figref> shows operations <b>110</b> commencing at start <b>302</b> for the collection of statistics for tables remaining in the list following a ranking of the list (step <b>108</b>). A statistics collection utility is initiated at step <b>304</b>. For each table in the list, the table is accessed and statistics are collected (step <b>306</b>). As it executes, the collection utility will normally consume resources such as CPU time and input/output bandwidth. Performance of the production workload (i.e. user submitted queries, transactions, management utilities) typically suffers when there is resource contention with the collection utility. Therefore, it is desirable to limit the impact of the collection utility on the production workload.

p-0035A workload management system typically allows the user to partition tasks on their system so that quality of service objectives may be met. Typically this is done by controlling or limiting how the system resources are allocated amongst tasks. On a DBMS with integrated workload management capabilities with a high degree of control for specific tasks—this may involve an automated task manager always invoking the task with the lowest workload priority class. On a DBMS without workload management, this may involve using an operating system capability to lower the priority of a process/thread performing the work for the task. Yet another mechanism controls the utilities resource consumption by forcing the utility to sleep (or yield) for a portion of its time. Either mechanism can be used to limit the impact of the collection utility. The mechanism selected to limit the impact of the collection utility should not impede its progress when it is not impacting the production workload. Only restricting a utility when necessary to preserve the impact policy is often referred to as “throttling” the utility.

p-0036The configuration options will vary depending on the mechanism for limiting the impact of the collection utility. This is often referred to as a workload “policy” definition. One preferred embodiment for regulating the impact of non-production work uses a single parameter to limit how the production workload may be impacted. For example, setting an impact parameter to 10% means that the cumulative impact of background utilities on the production workload should not exceed 10%. In other words, the impact parameter defines the maximum slowdown in the workload performance a user is willing to tolerate. The advantage of this approach is simplicity—the DBA can confidently categorize tasks as either production or background with guarantees on the impact of background tasks on the production workload.

p-0037However, there may be drawbacks to a single parameter strategy since automated or autonomic utilities (such as statistics collection) or tasks may be initiated at any time. The policy for limiting the impact of background (i.e. automated or autonomic) tasks on production work may be too lenient for autonomically invoked jobs. Existing policies may have been defined by the DBA with the conscious knowledge of when the background utilities would be executed. For example, the DBA has set the policy too leniently because they know that they only explicitly execute maintenance tasks (i.e. backups, reorganizations) at night during off-peak hours. The problem is complicated because existing policies may have been defined in circumstances when automatic tasks may not have been a consideration. (i.e. An RDBMS upgrade introduces new autonomic tasks which did not exist in prior versions.) However, with autonomic utilities, a task may execute at any time—even during peak periods making the existing policy inadequate for limiting the tasks impact on the production workload. In accordance with one embodiment of the present approach, a second class of workload policies for automated jobs is used in addition to an explicitly defined user policy. If there are only automated tasks running on the system then the effective policy is the automated policy. Alternatively, the effective policy may be selected as the minimum of the explicitly defined policy and the autonomic policy. If there are non-automated tasks then the effective policy is the user-defined policy. Some DBMS implementations may elect to expose a parameter which allows the user to explicitly set how aggressively an autonomic task executes while others may choose to hide this configuration detail from the user.

p-0038At step <b>310</b>, lock contention mechanisms are preferably employed to resolve contending needs for locked database objects and other resources. This mechanism may be operated to free locks held by the collection utility to respect a priority in favour of the lock needs of user-initiated operations. Preferably, incomplete collection utility operations resulting from the lock release(s) are rolled back and may be re-initiated.

p-0039At step <b>312</b>, once the statistics are collected for a table, a determination may be made if a further table is in the list. If so, via Yes branch to step <b>306</b>, statistics are collected for the next table. If not, via No branch to step <b>314</b>, statistics collection operations <b>108</b> end.

p-0040At the end of the table processing, operations <b>112</b> to modify scheduling for the next check may be performed. <figref idrefs="DRAWINGS">FIG. 4</figref> illustrates an embodiment of such operations <b>112</b>. Following start <b>402</b>, for each table in the list of tables for which statistics were collected (i.e. per step <b>110</b>), the previous statistics for the table are compared to the current statistics (step <b>404</b>). For this comparison, an implementation may use one or more of histogram comparison and column cardinalities. As mentioned above with respect to sample and previous statistics comparisons, any metric that measures the difference between the statistic sets could be utilized. At step <b>406</b>, scheduling information for the table is updated in response to the comparison. A scheduler mechanism may be provide with results of the comparison for the table and operate to change its information in response. For example, input to the scheduler may be one of three states: “schedule more often”, “schedule less often” and “same scheduling”. As a result of the scheduler properties, statistics that change significantly over time will be collected more often than statistics that change rarely. At step <b>408</b>, a determination is made whether there is a further table to examine. If so, operations loop via Yes branch to step <b>404</b>. If no, operations end via No branch to step <b>410</b>.

p-0041Persons of ordinary skill in the art will appreciate that the embodiment described is an example and may be modified without departing from the scope of the invention. For example, though a scheduling mechanism is described to periodically schedule tables for collection in an adaptively variable manner, tables may be selected for collection in other ways. For example, the scheduling need not be adaptive. As well tables may be pre-grouped for different periodic scheduling based on anticipated collection frequencies or limited observed behaviour. As well a step of ranking tables to order the collection of tables indicated for selection is optional. Though preferred to enhance collection for prioritized tables, situations may indicate ranking provides few or limited advantages.

p-0042When pruning a table, a step of examining table artefacts indicating recent use may be omitted. Though helpful to indicate a likelihood of high table activity, such a step may be difficult in certain implementations of a DBMS. Persons of ordinary skill in the art will appreciate that various metadata maintained for a table to indicate the extent of table activity may be used. Preferably such metadata is maintained in a low overhead and easily accessible manner that will not impact database performance. Metadata may be stored in a memory closely coupled to the database management system, for example.

p-0043<figref idrefs="DRAWINGS">FIG. 5</figref> illustrates a schematic diagram of an apparatus <b>500</b> for automated statistics collection for a single node database. An Auto Stats component <b>502</b> (for example program instructions and data for configuring a computer system) is coupled via an application program interface (API) <b>506</b> and communications queue (<b>508</b>) for operation to a database management system (DBMS) <b>510</b> operating on a single computer system. DBMS <b>510</b> is communicatively coupled to data in a database <b>516</b>. Metadata <b>512</b> may be maintained by DBMS <b>510</b> and stored in a local memory. DBMS <b>510</b> may include a collection utility <b>514</b> for automatic periodic operation to collect statistics for tables of data in the database <b>516</b>. Auto stats component <b>502</b> may include a scheduler having scheduler information <b>504</b> for use to control the automatic periodic operation of the collection utility as previously described. As illustrated in <figref idrefs="DRAWINGS">FIG. 6</figref>, for multiple-node database configurations <b>600</b> comprising a plurality of coupled DBMSs (e.g. <b>510</b><i>a</i>, <b>510</b><i>b</i>, <b>510</b><i>c</i>), a single Auto Stats component may be used. DBMS <b>510</b><i>a </i>may then communicate with the coupled DBMSs <b>510</b><i>b </i>and <b>510</b><i>c </i>as necessary to collect statistics for tables managed by these respective systems.

p-0044The model described herein advantageously reduces table operations to automatically collect statistics. The model provides an approach which will “learn” that although a table may experience significant table activity, its statistics may not be changing. Despite the table activity, the model will minimize the maintenance time to collect the tables statistics. Adaptively scheduling collection may further reduce maintenance time. Moreover, collection operations may be throttled and operated to prioritize non-maintenance (i.e. production) activities to enhance user experience.

p-0045The above-described embodiments of the invention are intended to be examples of the present invention, and alterations and modifications may be affected thereto, by a person of skill in the art, without departing from the scope of the invention which is defined by the claims appended hereto.