Autonomic regulation of a volatile database table attribute

• US 10,108,622 B2
• Filed: 03/26/2014
• Issued: 10/23/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/226,095
• Publication Number:
  US 20150278304A1
• Time in Patent Office: 1,672 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 method for managing a computerized database, comprising the following executed by at least one computer system:

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;

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; and

using said at least one volatility attribute to manage access to data in said database table, wherein using said at least one volatility attribute to manage access to data in said database table comprises 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 method for managing a computerized database 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 method for managing a computerized database of claim 1, wherein said volatility attribute comprises a numerical value within a range.

4. The method for managing a computerized database 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 method for managing a computerized database 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 method for managing a computerized database 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 method for managing a computerized database of claim 1, further comprising:

receiving 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 database table volatility state of said database table is to be manually specified.

8. A computer-implemented method for managing a database, comprising:

maintaining data in a computerized database, said computerized database having data organized in at least one database table;

monitoring respective changes made to each database table of said at least one database table;

responsive to said monitoring respective changes made to each database table of said at least one database table, determining a respective database table volatility state of each database table of said at least one database table;

responsive to determining a respective database table volatility state of each database table of said at least one database table, generating a respective at least one volatility attribute representing the respective database table volatility state of each database table of said at least one database table, wherein the respective database table volatility state of each said database table is a property of the respective database table that is a function of changes to the respective database table with respect to time, independent of any queries against data in the respective database table; and

using the respective at least one volatility attribute of each database table to manage data in the respective database table, wherein using the respective at least one volatility attribute of each database table to manage data in the respective database table comprises at least one of;

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

9. The computer-implemented method for managing a database of claim 8, wherein said computerized database supports the SQL database language and each respective at least one volatility attribute comprises a respective SQL VOLATILE attribute.

10. The computer-implemented method for managing a database of claim 8, wherein each respective at least one volatility attribute comprises a numerical value within a range.

11. The computer-implemented method for managing a database of claim 8, wherein said determining a respective database table volatility state of each database table comprises computing a respective figure of merit representing volatility, said 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.

12. The computer-implemented method for managing a database of claim 8, further comprising:

generating a respective query execution strategy for each of a plurality of queries against data in said database, each query execution strategy being optimized by using at least one volatility attribute corresponding to a respective database table containing data which is the subject of the respective query.

13. The computer-implemented method for managing a database of claim 8, further comprising:

receiving a user input specifying whether a database table volatility state of a database table of said at least one database table is to be manually specified or automatically determined by a computer system;

wherein said determining a respective database table volatility state of each database table and generating a respective at least one volatility attribute representing the respective database table volatility state of each database table are not performed with respect to a database table for which said user input specifies that the volatility state of the corresponding database table is to be manually specified.