Autonomic regulation of a volatile database table attribute

• US 10,114,826 B2
• Filed: 06/23/2014
• Issued: 10/30/2018
• Est. Priority Date: 03/26/2014
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Konik, Rafal P., Mittelstadt, Roger A., Muras, Brian R., Theuer, Mark W.
• Primary Examiner(s): Bibbee, Jared M
• Application Number:
  US14/312,673
• Publication Number:
  US 20150278276A1
• Time in Patent Office: 1,590 Days
• CPC Class Codes: G06F 16/116 Details of conversion of file system types or formats
• CPC Class Codes: G06F 16/119 Details of migration of file systems migration mechanisms in storage systems G06F3/0647
• CPC Class Codes: G06F 16/13 File access structures, e.g. distributed indices arrangements of input from, or output to, record carriers G06F3/06
• CPC Class Codes: G06F 16/219 Managing data history or versioning querying versioned data G06F16/2474; querying temporal data G06F16/2477
• CPC Class Codes: G06F 16/2282 Tablespace storage structures; Management thereof
• CPC Class Codes: G06F 16/2393 Updating materialised views
• CPC Class Codes: G06F 16/24542 Plan optimisation

Abstract

A database table is monitored to automatically determine its volatility, and at least one volatility attribute expressing volatility state is saved. The volatility attribute is used to manage the database, e.g., to determine an optimum query execution strategy for a query involving data in the table, and/or for determining whether to re-optimize a previously saved query execution strategy and/or for managing storage and retrieval of table data. Preferably, parameters of the database table are captured at regular sampling intervals and a figure of merit representing volatility is computed from the parameter values. Preferably, the database supports the SQL database language, and the volatility attribute is or includes the SQL VOLATILE attribute. In an additional embodiment, the database manager may express volatility of a database table as a value within a range.

Claims

1. A computer program product, comprising a plurality of computer-executable instructions recorded in a non-transitory computer-readable media, wherein said instructions, when executed by at least one computer system, cause the computer system to perform:

monitoring at least one parameter of a database table of said computerized database over at least one time interval and saving monitored parameter data with respect to said database table;

determining a database table volatility state of said database table using the saved monitored parameter data, said database table volatility state being a property of said database table that is a function of changes to data recorded in said database table with respect to time, said database table volatility state being independent of any queries against data in said database table; and

responsive to determining a database table volatility state of said database table using the saved monitored parameter data, generating and saving at least one database table volatility attribute expressing the database table volatility state of said database table, said at least one volatility attribute being used by a process executing on the at least one computer system to manage access to data in said database table, wherein said at least one volatility attribute is used to manage access to data in said database table by at least one of;

(a) using said at least one volatility attribute to determine an optimum query execution strategy for a query against data in said database table, (b) using said at least one volatility attribute to determine whether to re-optimize a previously saved query execution strategy for a query against data in said database table, (c) using said at least one volatility attribute to determine whether to collect statistical data regarding said database table, and (d) using said at least one volatility attribute to manage storage and/or retrieval of data in said at least one database table.

2. The computer program product of claim 1, wherein said computerized database supports the SQL database language and said at least volatility attribute comprises an SQL VOLATILE attribute.

3. The computer program product of claim 1, wherein said volatility attribute comprises a numerical value within a range.

4. The computer program product of claim 1, wherein said determining a database table volatility state of said database table using the saved monitored parameter data comprises computing a figure of merit representing volatility, said figure of merit using at least one of:

(a) a number of insert operations performed on the database table;

(b) a number of update operations performed on the database table;

(c) a number of delete operations performed on the database table;

(d) a size of the database table;

(e) a change in size of the database table; and

(f) a number of bytes altered in the database table.

5. The computer program product of claim 1,

wherein said monitoring at least one parameter of a database table of said computerized database over at least one time interval and saving monitored parameter data with respect to said database table comprises saving respective monitored parameter data with respect to each of a plurality of consecutive time intervals;

and

wherein said determining a database table volatility state of said database table using the saved monitored parameter data comprises determining a database table volatility state of said database table using respective saved monitored parameter data from a plurality recent consecutive time intervals.

6. The computer program product of claim 5, wherein said determining a database table volatility state of said database table using respective saved monitored parameter data from a plurality recent consecutive time intervals comprises computing a respective figure of merit representing volatility of said database table for each of the plurality of recent consecutive time intervals.

7. The computer program product of claim 1,

wherein said instructions, when executed by the at least one computer system, further cause the computer system to receive a user input specifying whether database table volatility state of said database table is to be manually specified or automatically determined by said at least one computer system;

wherein said determining a database table volatility state of said database table using the saved monitored parameter data and said generating and saving at least one volatility attribute expressing volatility of said database table are not performed if said user input specifies that the volatility state of said database table is to be manually specified.

8. A database manager computer program product, comprising a plurality of computer-executable instructions recorded in a non-transitory computer-readable media, wherein said instructions comprise:

at least one data management function for performing at least one of adding data to, removing data from, or editing data in, one or more structured database tables managed by said database manager;

a query engine for executing queries against data in said one or more structured database tables;

a volatility monitoring function which determines a respective database table volatility state of at least one database table of said one or more structured database tables using saved monitored parameter data with respect to the respective database table and generates and saves at least one volatility attribute of the respective database table expressing the respective database table volatility of the respective database table responsive to determining the respective database table volatility state, the respective database table volatility state of each said database table being a property of the respective database table that is a function of changes to data contained in the respective database table with respect to time, the respective database table volatility state being independent of any queries against data in the respective database table; and

wherein said database manager uses said at least one volatility attribute to manage access to data in said one or more structured database tables by at least one of;

(a) using said at least one volatility attribute to determine an optimum query execution strategy for a query against data in said database table, (b) using said at least one volatility attribute to determine whether to re-optimize a previously saved query execution strategy for a query against data in said database table, (c) using said at least one volatility attribute to determine whether to collect statistical data regarding said database table, and (d) using said at least one volatility attribute to manage storage and/or retrieval of data in said at least one database table.

9. The database manager computer program product of claim 8, wherein said database manager supports the SQL database language and said at least volatility attribute comprises an SQL VOLATILE attribute.

10. The database manager computer program product of claim 8, further comprising:

a query optimizer for generating optimized query execution plans for use by said query engine in executing queries, wherein said query optimizer uses said at least one volatility attribute to generate optimized query execution plans.

11. The database manager computer program product of claim 8, wherein said volatility attribute comprises a numerical value within a range.

12. The database manager computer program product of claim 8, wherein said volatility monitoring function determines a respective database table volatility state of at least one database table by computing a respective figure of merit representing volatility, each respective figure of merit using at least one of:

(a) a number of insert operations performed on the respective database table;

(b) a number of update operations performed on the respective database table;

(c) a number of delete operations performed on the respective database table;

(d) a size of the respective database table;

(e) a change in size of the respective database table; and

(f) a number of bytes altered in the respective database table.

13. The database manager computer program product of claim 8,

wherein said monitored parameter data comprises respective monitored parameter data with respect to each database table collected in each of a plurality of consecutive time intervals;

and

wherein said volatility monitoring function determines the respective database table volatility state of each database table using respective saved monitored parameter data from a plurality recent consecutive time intervals.

14. A computer system supporting a database, comprising:

a memory;

at least one processor, said at least one processor executing instructions storable in said memory;

a database table of said database storable in said memory;

a database manager embodied as instructions storable in said memory and executable on said at least one processor, said database manager managing said database and providing access to data in said database table;

said database manager including a volatility monitoring function which determines a database table volatility state of said database table using saved monitored parameter data with respect to said database table and generates and saves at least one volatility attribute of said database table expressing volatility of said database table responsive to determining the database table volatility state, the database table volatility state of said database table being a property of

the database table that is a function of changes to the database table with respect to time, independent of any queries against data in the database table; and

wherein said database manager uses said at least one volatility attribute to manage access to data in said database table by at least one of;

(a) using said at least one volatility attribute to determine an optimum query execution strategy for a query against data in said database table, (b) using said at least one volatility attribute to determine whether to re-optimize a previously saved query execution strategy for a query against data in said database table, (c) using said at least one volatility attribute to determine whether to collect statistical data regarding said database table, and (d) using said at least one volatility attribute to manage storage and/or retrieval of data in said at least one database table.

15. The computer system of claim 14, wherein said database supports the SQL database language and said at least volatility attribute comprises an SQL VOLATILE attribute.

16. The computer system of claim 14, wherein said volatility attribute comprises a numerical value within a range.

17. The computer system of claim 14, wherein said volatility monitoring function determines said database table volatility state of said database table by computing a figure of merit representing volatility, said figure of merit using at least one of:

(a) a number of insert operations performed on the database table;

(b) a number of update operations performed on the database table;

(c) a number of delete operations performed on the database table;

(d) a size of the database table;

(e) a change in size of the database table; and

(f) a number of bytes altered in the database table.

18. The computer system of claim 14,

wherein said monitored parameter data comprises respective monitored parameter data with respect to said database table collected in each of a plurality of consecutive time intervals;

and

wherein said volatility monitoring function determines said database table volatility state of said database table using respective saved monitored parameter data from a plurality recent consecutive time intervals.