Adaptive query cursor management

• US 9,846,599 B1
• Filed: 10/31/2016
• Issued: 12/19/2017
• Est. Priority Date: 10/31/2016
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Baranczyk, Shawn J., Konik, Rafal P., Mittelstadt, Roger A., Muras, Brian R.
• Primary Examiner(s): Kamran, Mehran
• Application Number:
  US15/339,511
• Time in Patent Office: 414 Days
• Field of Search: None
• CPC Class Codes: G06F 11/30 Monitoring
• CPC Class Codes: G06F 11/3024 where the computing system component is a central processing unit [CPU]
• CPC Class Codes: G06F 11/3037 where the computing system component is a memory, e.g. virtual memory, cache accessing, addressing or allocating within memory systems or architectures G06F12/00; checking stores for correct operation G11C29/00
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/217 Database tuning G06F16/2282 takes precedence; database performance monitoring G06F11/3409
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: G06F 2201/81 Threshold
• CPC Class Codes: G06F 2201/88 Monitoring involving counting
• CPC Class Codes: G06F 9/5011 the resources being hardware resources other than CPUs, Servers and Terminals

Abstract

Techniques are provided for managing a database cursor threshold. One embodiment provides a method that includes setting a database cursor threshold, for a first database job of a plurality of database jobs, to a first value. Generally, the cursor threshold defines the maximum number of database cursors that are permitted to be open at a time in a database system. One or more system resources of the database system are monitored, and upon detecting that a current state of the one or more system resources satisfies predefined criteria, embodiments set the database cursor threshold to a second value.

Claims

1. A computer-readable storage medium containing computer program code that, when executed by operation of one or more computer processors, performs an operation comprising:

collecting usage metrics for each of a plurality of database cursors, wherein the usage metrics further comprise a number of other database cursors that are associated with a job with which each respective database cursor is associated;

calculating a cost of each respective database cursor based on the collected usage metrics, wherein the cost describes an estimated amount of time to rebuild the respective database cursor, if closed, relative to an average runtime of the respective database cursor, and wherein the cost is calculated further based on at least one of;

(a) a size in memory of each respective database cursor, and (b) an amount of resources that are required when each respective database cursor is used;

upon determining that a database cursor threshold has been exceeded, wherein the database cursor threshold defines the maximum number of database cursors that are permitted to be open at a time;

selecting, based on the calculated costs, a database cursor of the plurality of database cursors; and

closing the selected cursor.

2. The computer-readable storage medium of claim 1, wherein the cost of each respective database cursor is further calculated based on a history of each respective database cursor, wherein the history comprises at least one of:

(a) a measure of a frequency with which each respective database cursor is reused, and (b) how long each respective database cursor is likely to remain open before being closed by a respective thread or job.

3. The computer-readable storage medium of claim 1, wherein the usage metrics further comprise at least one of:

(a) a number of users who have used each respective database cursor, (b) a number of threads which have accessed each respective database cursor, (c) a number of jobs which have used each respective database cursor, and (d) a number of applications which have accessed each respective database cursor.

4. The computer-readable storage medium of claim 1, wherein selecting the database cursor of the plurality of database cursors is not based on a time at which the database cursor was created or an amount of time since the database cursor was created.

5. A computer-readable storage medium containing computer program code that, when executed by operation of one or more computer processors, performs an operation comprising:

collecting usage metrics for each of a plurality of database cursors, wherein the usage metrics further comprise a number of other database cursors that are associated with a job with which each respective database cursor is associated;

calculating a cost of each respective database cursor based on the collected usage metrics, wherein the cost describes an estimated amount of time to rebuild the respective database cursor, if closed, relative to an average runtime of the respective database cursor, and wherein the cost is calculated further based on a history of each respective database cursor, wherein the history comprises at least one of;

(a) a measure of a frequency with which each respective database cursor is reused, and (b) how long each respective database cursor is likely to remain open before being closed by a respective thread or job;

upon determining that a database cursor threshold has been exceeded, wherein the database cursor threshold defines the maximum number of database cursors that are permitted to be open at a time;

selecting, based on the calculated costs, a database cursor of the plurality of database cursors; and

closing the selected cursor.

6. The computer-readable storage medium of claim 5, wherein the cost of each respective database cursor is further calculated based on at least one of:

(a) a size in memory of each respective database cursor, and (b) an amount of resources that are required when each respective database cursor is used.

7. The computer-readable storage medium of claim 5, wherein the usage metrics further comprise at least one of:

(a) a number of users who have used each respective database cursor, (b) a number of threads which have accessed each respective database cursor, (c) a number of jobs which have used each respective database cursor, and (d) a number of applications which have accessed each respective database cursor.

8. The computer-readable storage medium of claim 5, wherein selecting the database cursor of the plurality of database cursors is not based on a time at which the database cursor was created or an amount of time since the database cursor was created.

9. A computer-readable storage medium containing computer program code that, when executed by operation of one or more computer processors, performs an operation comprising:

collecting usage metrics for each of a plurality of database cursors, wherein the usage metrics further comprise at least one of;

(a) a number of users who have used each respective database cursor, (b) a number of threads which have accessed each respective database cursor, (c) a number of jobs which have used each respective database cursor, and (d) a number of applications which have accessed each respective database cursor;

calculating a cost of each respective database cursor based on the collected usage metrics, wherein the cost describes an estimated amount of time to rebuild the respective database cursor, if closed, relative to an average runtime of the respective database cursor, wherein the cost is calculated further based on at least one of;

(a) a size in memory of each respective database cursor, (b) an amount of resources that are required when each respective database cursor is used, and (c) a history of each respective database cursor, wherein the history comprises at least one of;

(i) a measure of a frequency with which each respective database cursor is reused, and (ii) how long each respective database cursor is likely to remain open before being closed by a respective thread or job;

upon determining that a database cursor threshold has been exceeded, wherein the database cursor threshold defines the maximum number of database cursors that are permitted to be open at a time;

selecting, based on the calculated costs, a database cursor of the plurality of database cursors; and

closing the selected cursor.

10. The computer-readable storage medium of claim 9, wherein the usage metrics further comprise a number of other database cursors that are associated with a job with which each respective database cursor is associated.

11. The computer-readable storage medium of claim 9, wherein selecting the database cursor of the plurality of database cursors is not based on a time at which the database cursor was created or an amount of time since the database cursor was created.