METHOD AND SYSTEM FOR QUANTIFYING A DATA PAGE REPETITION PATTERN FOR A DATABASE INDEX IN A DATABASE MANAGEMENT SYSTEM

• US 20080243761A1
• Filed: 03/26/2007
• Published: 10/02/2008
• Est. Priority Date: 03/26/2007
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Tsuji, Yoichi, Guo, Shuanglin, Purcell, Terence P.
• Granted Patent: US 7,921,085 B2
• Time in Patent Office: Days
• US Class Current: 707/2
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 16/2462 Approximate or statistical queries

Abstract

A method and system are presented for quantifying a data page repetition pattern for a database index in a database management system. In one embodiment, the method includes identifying a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key. The method may also include collecting the data page repetition statistic based on the database index key, wherein the data page repetition statistic quantifies a data page repetition pattern associated with database queries that reference sequential entries of the database index. The method may further include optimizing a data page access process based on the data page repetition statistic. In a further embodiment, the method may utilize both cluster ratio and data page repetition statistics to evaluate data page I/O and CPU cost.

Claims

1. A method for quantifying a data page repetition pattern for a database index in a database management system, the method comprising:

identifying a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key;

collecting the data page repetition statistic based on the database index key, wherein the data page repetition statistic quantifies a data page repetition pattern associated with database queries that reference sequential entries of the database index; and

optimizing a data page access process based on the data page repetition statistic.

2. The method of claim 1, wherein collecting the data page repetition statistic further comprises:

conducting a statistic collection scan of a database index to identify a data storage page identifier for a data entry corresponding to each database index key value, the statistic collection scan examining entries in the identified database index, the identified database index ordered by database index key; and

incrementing a counter in response to a determination that a data entry corresponding to a next database index key value is stored on a different data storage page than a data entry for a current database index key value.

3. The method of claim 1, wherein collecting the data page repetition statistic further comprises:

conducting a statistic collection scan of a database index to identify a data storage page identifier for a data entry corresponding to each database index key value, the identified database index ordered by database index key, the statistic collection scan examining entries in the identified database index in order;

defining a logical window to represent a set of database pages, the logical window beginning at a first data page associated with a first index key in the database index and having a size of one database page that stores a data entry corresponding to the first index key value;

increasing the size of the logical window up to a maximum size in response to a determination that the statistic collection scan references a data page not included within the logical window; and

incrementing a counter in response to a determination that one of the size of the logical window and a position of the logical window is adjusted due to the statistic collection scan.

4. The method of claim 3, wherein the maximum size comprises a value equal to the number of data pages that are cached when a database is accessed in an operational environment, and wherein the set of data pages in the logical window do not incur a persistent memory cost.

5. The method of claim 3, wherein the position of the logical window adjusts to a subsequent database index key value and the size adjusts to a size of one database page in response to the statistic collection scan referencing a data page not included within the logical window and the size equaling the maximum size.

6. The method of claim 3, wherein the data page repetition statistic is defined by a value of the counter that exceeds a number of unique database pages referenced by entries of a scanned database index.

7. The method of claim 3, wherein a minimum value of the counter comprises a number of unique database pages scanned by the statistic collection scan, and a maximum value of the counter comprises the number of data rows of a scanned database index scanned by the statistic collection scan.

8. The method of claim 1, wherein collecting the data page repetition statistic and optimizing the data page access process is managed autonomously by a relational database management system, and wherein the collection of the data page repetition statistic is performed during a period of minimal database activity.

9. The method of claim 1, wherein optimizing the data page access process is further based on at least one of a database size statistic, a database query selectivity statistic, and a data clustering statistic.

10. The method of claim 9, wherein collecting the data page repetition statistic is conducted substantially simultaneously with a statistic collection process for at least one of the database size statistic, the database query selectivity statistic, and the data clustering statistic.

11. A computer program product comprising a computer useable medium having a computer readable program for quantifying a data page repetition pattern for a database index in a database management system, wherein the computer readable program when executed on a computer causes:

identifying a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key;

collecting the data page repetition statistic based on the database index key, wherein collecting the data page repetition statistic comprises;

conducting a statistic collection scan of a database index to identify a data storage page identifier for a data entry corresponding to the database index key value, the statistic collection scan examining entries in the identified database index; and

incrementing a counter in response to a determination that a data entry corresponding to the database index key value has been identified on a new data storage page; and

optimizing a data page access process based on the data page repetition statistic.

12. The computer program product of claim 11, wherein collecting the data page repetition statistic further comprises:

defining a logical window to represent a set of database pages, the logical window beginning at a first data page associated with a first index key in the database index and having a size of one database page that stores a data entry corresponding to the first index key value;

increasing the size of the logical window up to a maximum size in response to a determination that the statistic collection scan references a data page not included within the logical window; and

incrementing a counter in response to a determination that one of the size of the logical window and a position of the logical window is adjusted due to the statistic collection scan.

13. The computer program product of claim 12, wherein the maximum size comprises a value equal to the number of data pages that are cached when a database is accessed in an operational environment, and wherein the set of data pages in the logical window do not incur a persistent memory cost.

14. The computer program product of claim 12, wherein the position of the logical window adjusts to a subsequent database index key value and the size adjusts to a size of one database page in response to the statistic collection scan referencing a data page not included within the logical window and the size equaling the maximum size.

15. The computer program product of claim 12, wherein the data page repetition statistic is defined by a value of the counter that exceeds a number of unique database pages referenced by entries of a scanned database index.

16. The computer program product of claim 12, wherein a minimum value of the counter comprises a number of unique database pages scanned by the statistic collection scan, and a maximum value of the counter comprises the number of data rows of a scanned database index scanned by the statistic collection scan.

17. The computer program product of claim 11, wherein collecting the data page repetition statistic and optimizing the data page access process is managed autonomously by a relational database management system, and wherein the collection of the data page repetition statistic is performed during a period of minimal database activity.

18. The computer program product of claim 11, wherein optimizing the data page access process is further based on at least one of a database size statistic, a database query selectivity statistic, and a data clustering statistic.

19. The computer program product of claim 18, wherein collecting the data page repetition statistic is conducted substantially simultaneously with a statistic collection process for at least one of the database size statistic, the database query selectivity statistic, and the data clustering statistic.

20. A database management system for quantifying a data page repetition pattern for a database index in a database management system, the database management system comprising:

a relational database management system configured to;

identify a database index to provide a basis for collecting a data page repetition statistic, the database index having a database index key;

collect the data page repetition statistic based on the database index key, wherein collecting the data page repetition statistic comprises triggering a statistic collection scan of a database index to identify a data storage page identifier for a data entry corresponding to the database index key value, the statistic collection scan examining entries in the identified database index, and incrementing a counter in response to a determination that a data entry corresponding to the database index key value has been identified on a new data storage page; and

optimize a data page access request based on the data page repetition statistic;

a data manager configured to receive the data page access request from the relational database management system; and

a buffer manager in communication with the data manager, and configured to load data stored in a storage volume into a data access buffer in response to the data page access request.

21. The database management system of claim 20, wherein the relational database management system is further configured to:

define a logical window to represent a set of database pages, the logical window beginning at a first data page associated with a first index key in the database index and having a size of one database page that stores a data entry corresponding to the first index key value;

increase the size of the logical window up to a maximum size in response to a determination that the statistic collection scan references a data page not included within the logical window; and

increment a counter in response to a determination that one of the size of the logical window and a position of the logical window is adjusted due to the statistic collection scan.

22. The database management system of claim 21, wherein the maximum size comprises a value equal to the number of data pages that are cached when a database is accessed in an operational environment, and wherein the set of data pages in the logical window do not incur a persistent memory cost.

23. The database management system of claim 21, wherein the position of the logical window adjusts to a subsequent database index key value and the size adjusts to a size of one database page in response to the statistic collection scan referencing a data page not included within the logical window and the size equaling the maximum size.

24. The database management system of claim 21, wherein the data page repetition statistic is defined by a value of the counter that exceeds a number of unique database pages referenced by entries of a scanned database index.

25. The database management system of claim 21, wherein a minimum value of the counter comprises a number of unique database pages scanned by the statistic collection scan, and a maximum value of the counter comprises the number of data rows of a scanned database index scanned by the statistic collection scan.

26. The database management system of claim 20, collecting the data page repetition statistic and optimizing the data page access process is managed autonomously by a relational database management system, and wherein the collection of the data page repetition statistic is performed during a period of minimal database activity.

27. The database management system of claim 20, wherein optimizing the data page access process is further based on at least one of a database size statistic, a database query selectivity statistic, and a data clustering statistic.

28. The database management system of claim 27, wherein collecting the data page repetition statistic is conducted substantially simultaneously with a statistic collection process for at least one of the database size statistic, the database query selectivity statistic, and the data clustering statistic.