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

• US 7,739,293 B2
• Filed: 11/22/2004
• Issued: 06/15/2010
• Est. Priority Date: 11/22/2004
• Status: Active Grant

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Popivanov, Ivan, Rose, Kevin R., Lightstone, Sam Sampson
• Primary Examiner(s): Fleurantin; Jean B
• Application Number:
  US10/996,197
• Publication Number:
  US 20060112093A1
• Time in Patent Office: 2,031 Days
• Field of Search: 707 1- 10, 707100-1041, 707200-206
• US Class Current: 707/765
• CPC Class Codes: G06F 16/221 Column-oriented storage; Management thereof

Abstract

The present invention relates to collecting statistics automatically for data in a database. There is provided a method for automated statistics collection comprising determining a likelihood that statistics for data have changed; and collecting statistics for data in response to the likelihood. Indicators of the likelihood that statistics have changed may be useful to trigger automated statistics collection. Tables having statistics that change significantly 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 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 collecting statistics, throttling and lock contention can minimize impact on a database user'"'"'s response experience.

Claims

1. 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 comprising:

assembling a list of tables of said data, said tables being scheduled for periodic automatic statistics collection;

for each of the tables, determining a likelihood that currently computed statistics associated with the data in each of the tables have changed;

removing from the list tables for which said determined likelihood is low;

collecting updated statistics for the data in tables which remain in said list after said removing; and

updating the scheduled periodic automatic statistics collection for each of the tables, said periodic automatic statistics collection comprising periodically performing subsequent collections of statistics associated with the data in each of the tables, wherein said updating comprises scheduling, for each of the tables, said subsequent collections of statistics more often or less often based on another likelihood that the updated statistics have changed for each of the tables,

wherein said another likelihood that the updated statistics have changed is determined by comparing said updated statistics with said computed statistics.

2. The data processing system implemented method of claim 1 wherein the determining comprises:

sampling a portion of the data to generate a sample result; and

comparing the sample result to statistics previously collected for the data whereby said likelihood is responsive to said comparing.

3. The data processing system implemented method of claim 1 wherein the determining comprises examining metadata maintained for said data whereby said likelihood is responsive to said examining.

4. The data processing system implemented method of claim 3 wherein the metadata comprises 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.

5. The data processing system implemented method of claim 1, wherein the determining is responsive to said scheduling.

6. The data processing system implemented method of claim 5 further comprising modifying the scheduling in response to the likelihood that statistics for the data have changed.

7. The data processing system implemented method of claim 5 further comprising modifying the scheduling in response to a change to the statistics collected for the data.

8. The data processing system implemented method of claim 1 wherein the collecting of updated statistics comprises throttling the collecting, reducing a consumption of resources in response to a contending need for the resources.

9. The method of claim 8 wherein throttling comprises reducing a consumption of resources in response to a first policy determined by a user and a second policy determined by an administrator.

10. The data processing system implemented method of claim 1 wherein the collecting of updated statistics comprises rejecting a resource lock for the collecting in response to a contending need for the resource lock.

11. The data processing system implemented method of claim 1 wherein the data is stored in tables formed from columns and rows and said steps of determining and collecting are performed for individual tables.

12. The data processing system implemented method of claim 1, wherein the scheduling comprises scheduling the data for the periodic automatic statistics collection to collect the statistics more often if the determined likelihood is high and less often if the determined likelihood is low.

13. A data processing system for collecting statistics associated with data stored in a database, the database operatively coupled to the data processing system operating on a computer, the data processing system comprising:

a processor;

an assembling module, executed on the processor, for assembling a list of tables of said data, said tables being scheduled for periodic automatic statistics collection;

a determining module for determining, for each of the tables, a likelihood that currently computed statistics associated with the data have changed;

a removing module for removing from the list tables for which said determined likelihood is low;

a collecting module for collecting updated statistics for the data in tables which remain in said list after said removing module removes tables for which said determined likelihood is low; and

an updating module for updating the scheduled periodic automatic statistics collection for each of the tables, said periodic automatic statistics collection comprising periodically performing subsequent collections of statistics associated with the data in each of the tables, wherein said updating comprises scheduling, for each of the tables, said subsequent collections of statistics more often or less often based on another likelihood that the updated statistics have changed for each of the tables,

wherein said another likelihood that the updated statistics have changed is determined by comparing said updated statistics with said computed statistics.

14. The data processing system of claim 13 further comprising a query optimizer for selecting a plan of execution for operation on the database from a plurality of plans, said query optimizer responsive to said statistics when selecting the plan.

15. The data processing system of claim 13 wherein the determining module comprises a sampling component for sampling a portion of the data, the determining module determining the likelihood that statistics for the data have changed in response to the sampling and the statistics previously collected for the data.

16. The data processing system of claim 13 further comprising a throttling mechanism for reducing consumption of a resource of said database management system in response to a contending need for the resource.

17. The data processing system of claim 13 further comprising a resource lock contention mechanism to reject a resource lock of a resource of said database management system in response to a contending need for the resource.

18. 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 comprising:

a program usable storage medium embodying one or more instructions executable by a processor of the data processing system, the one or more instructions comprising;

data processing system executable instructions for assembling a list of tables of said data, said tables being scheduled for periodic automatic statistics collection;

data processing system executable instructions for determining, for each of the tables, a likelihood that currently computed statistics associated with the data in each of the tables have changed;

data processing system executable instructions for removing from the list tables for which said determined likelihood is low;

data processing system executable instructions for collecting updated statistics for the data in tables which remain in said list after said removing; and

data processing system executable instructions for updating the scheduled periodic automatic statistics collection for each of the tables, said periodic automatic statistics collection comprising periodically performing subsequent collections of statistics associated with the data in each of the tables, wherein said updating comprises scheduling, for each of the tables, said subsequent collections of statistics more often or less often based on another likelihood that the updated statistics have changed for each of the tables,

wherein said another likelihood that the updated statistics have changed is determined by comparing said updated statistics with said computed statistics.

19. The article of claim 18 further comprising:

data processing system executable instructions for sampling a portion of the data to generate a sample result; and

data processing system executable instructions for comparing the sample result to statistics previously collected for the data whereby said likelihood is responsive to said comparing.

20. The article of claim 18 wherein the data processing system executable instructions for determining comprise data processing system executable instructions for examining metadata maintained for said data whereby said likelihood is responsive to said examining.

21. The article of claim 20 wherein the metadata comprises 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.

22. The article of claim 18 further comprising data processing system executable instructions for scheduling the data for periodic automated statistics collection and wherein the data processing system executable instructions for determining are responsive to said scheduling.

23. The article of claim 22 further comprising data processing system executable instructions for modifying the scheduling in response to the likelihood that statistics for the data have changed.

24. The article of claim 22 further comprising data processing system executable instructions for modifying the scheduling in response to a change to the statistics collected for the data.

25. The article of claim 18 further comprising data processing system executable instructions for throttling the collecting, reducing a consumption of resources in response to a contending need for the resources.

26. The article of claim 25 wherein the data processing system executable instructions for throttling are responsive to a first policy determined by a user and a second policy determined by an administrator for reducing the consumption of resources.

27. The article of claim 18 further comprising data processing system executable instructions for rejecting a resource lock for the collecting in response to a contending need for the resource lock.