US-12619584-B1 - Automatic prediction of and avoidance of statement pool churn

US12619584B1US 12619584 B1US12619584 B1US 12619584B1US-12619584-B1

Abstract

A computer implemented method, system, and program product are provided. In response to receiving a request for a structured query language (SQL) statement from a requestor, a current request count is incremented. A matching entry in a discards list is located, whereby the match comprises, a discarded SQL statement equaling the requested SQL statement. Based on there being the matching entry, the matching entry is removed from the discards list. A proximity between the current request count and a previous request count is created. A threshold is created based on a configurable number of times of a maximum SQL statement cache size. The poolability of the requested SQL statement is determined. The requested SQL statement is returned to the requestor. Periodically, maintenance is performed on the discards list.

Inventors

Nathan Jon RAUH
Mark Swatosh
James Stephens
Alex Seitzinger Motley

Assignees

INTERNATIONAL BUSINESS MACHINES CORPORATION

Dates

Publication Date: 20260505
Application Date: 20250131

Claims (20)

1 . A computer implemented method comprising: in response to receiving a request for a structured query language (SQL) statement from a requestor, directing the request to a statement pool configured in Java Data Base Connectivity (JDBC) for connections to a database that responds to the requested SQL; incrementing in the statement pool a current request count of total requested SQL statements, identifying the requested SQL statement as the current request count appended to the requested SQL (S.SQL), and locating a matching discards list entry in a discards list in the statement pool, wherein, a discards list entry comprises the discarded SQL (D.SQL) to which is appended a request number upon which the discard happens, and wherein the matching entry is a case sensitive comparison of string text of the discarded SQL statement to the requested SQL statement; based on there being the matching entry, removing the matching entry from the discards list, and creating a proximity between the requested SQL statement (S.SQL) and the discarded SQL (D.SQL); computing a threshold, wherein the threshold is a configurable number times a maximum SQL statement cache size; determining whether a poolability hint override exists for the requested SQL statement, based on searching a poolability overrides list in the statement pool for the requested SQL statement (S.SQL), wherein the poolability hint is a true/false indicator of whether the requested SQL statement (S.SQL) should be pooled in the statement pool; providing the requested SQL statement to the requestor; and periodically performing the discards list maintenance.
2 . The computer implemented method of claim 1 , wherein determining the poolability further comprises: locating the requested SQL statement in a poolability overrides list; based on there being a match in the poolability overrides list, locating a previous request count paired with and the requested SQL statement in the poolability overrides list; pairing the match in the poolability overrides list with the current request count; based on the current request count minus the previous request count being less than a threshold, and there being a poolability hint in the requested SQL statement, removing the match from the poolability overrides list, incrementing an unwanted discards count, and incrementing a maximum SQL statement pool size; and locating the requested SQL statement in a SQL statement pool.
3 . The computer implemented method of claim 1 , wherein providing the requested SQL statement to the requestor further comprises: locating the requested SQL statement in a SQL statement pool; and based on the requested SQL statement being in the SQL statement pool, and there being a poolability hint in the requested SQL statement, and a maximum SQL statement pool size: discarding a least recently used SQL statement from the SQL statement pool, and adding the least recently used SQL statement to the discards list.
4 . The computer implemented method of claim 1 , wherein periodically performing discards list maintenance further comprises: based on the current request count modulo the threshold being zero: for each entry in the discards list, removing the entry based on a difference between the current request count and a request count when the entry was added to the discards list, and adding the removed entry to the poolability overrides list.
5 . The computer implemented method of claim 1 , wherein providing the requested SQL statement to the requestor further comprises; receiving the requested SQL statement from the requestor; and based on the requested SQL statement being poolable adding the requested SQL statement to a SQL statement pool.
6 . The computer implemented method of claim 1 , wherein the proximity is the current request count minus a request count when the matching entry was discarded.
7 . The computer implemented method of claim 1 , wherein a request number of a SQL statement is a value of a request count paired with the SQL statement.
8 . A computer system comprising: a number of processor units, wherein the number of processor units executes program instructions to: in response to receiving a request for a structured query language (SQL) statement from a requestor, directing the request to a statement pool configured in Java Data Base Connectivity (JDBC) for connections to a database that responds to the requested SQL; incrementing in the statement pool a current request count of total requested SQL statements, identifying the requested SQL statement as the current request count appended to the requested SQL (S.SQL), and locating a matching entry in a discards list in the statement pool, wherein, a discards list entry comprises the discarded SQL (D.SQL) to which is appended a request number upon which the discard happens, and wherein the matching entry is a case sensitive comparison of string text of the discarded SQL statement to the requested SQL statement; based on there being the matching entry, removing the matching entry from the discards list, and creating a proximity between requested SQL statement (S.SQL) and the discarded SQL (D.SQL); computing a threshold, wherein the threshold is a configurable number times a maximum SQL statement cache size; determining whether a poolability hint override exists for the requested SQL statement, based on searching a poolability overrides list in the statement pool for the requested SQL statement (S.SQL), wherein the poolability hint is a true/false indicator of whether the requested SQL statement (S.SQL) should be pooled in the statement pool; providing the requested SQL statement to the requestor; and periodically performing the discards list maintenance.
9 . The computer system of claim 8 , wherein determining the poolability further comprises: locating the requested SQL statement in a poolability overrides list; based on there being a match in the poolability overrides list, locating a previous request count paired with the requested SQL statement in the poolability overrides list; pairing the match in the poolability overrides list with the current request count; based on the current request count minus the previous request count being less than a threshold, and there being a poolability hint in the requested SQL statement, removing the match from the poolability overrides list, incrementing an unwanted discards count, and incrementing a maximum SQL statement pool size; and locating the requested SQL statement in a SQL statement pool.
10 . The computer system of claim 8 , wherein providing the requested SQL statement to the requestor further comprises: locating the requested SQL statement in a SQL statement pool; and based on the requested SQL statement being in the SQL statement pool, and there being a poolability hint in the requested SQL statement, and a maximum SQL statement pool size: discarding a least recently used SQL statement from the SQL statement pool, and adding the least recently used SQL statement to the discards list.
11 . The computer system of claim 8 , wherein periodically performing discards list maintenance further comprises: based on the current request count modulo the threshold being zero: for each entry in the discards list, removing the entry based on a difference between the current request count and a request count when the entry was added to the discards list, and adding the removed entry to the poolability overrides list.
12 . The computer system of claim 8 , wherein providing the requested SQL statement to the requestor further comprises; receiving the requested SQL statement from the requestor; and based on the requested SQL statement being poolable adding the requested SQL statement to a SQL statement pool.
13 . The computer system of claim 8 , wherein the proximity is the current request count minus a request count when the matching entry was discarded.
14 . The computer system of claim 8 , wherein a request number of a SQL statement is a value of a request count paired with the SQL statement.
15 . A computer program product, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, the program instructions executable by a computer system to cause the computer system to perform a method of: in response to receiving a request for a structured query language (SQL) statement from a requestor, directing the request to a statement pool configured in Java Data Base Connectivity (JDBC) for connections to a database that responds to the requested SQL; incrementing in the statement pool a current request count of total requested SQL statements, identifying the requested SQL statement as the current request count appended to the requested SQL (S.SQL), and locating a matching entry in a discards list in the statement pool, wherein, a discards list entry comprises the discarded SQL (D.SQL) to which is appended a request number upon which the discard happens, and wherein the matching entry is a case sensitive comparison of string text of the discarded SQL statement to the requested SQL statement; based on there being the matching entry, removing the matching entry from the discards list, and creating a proximity between the requested SQL statement (S.SQL) and the discarded SQL (D.SQL); computing a threshold, wherein the threshold is a configurable number times a maximum SQL statement cache size; determining whether a poolability hint override exists for the requested SQL statement, based on searching a poolability overrides list in the statement pool for the requested SQL statement (S.SQL), wherein the poolability hint is a true/false indicator of whether the requested SQL statement (S.SQL) should be pooled in the statement pool; providing the requested SQL statement to the requestor; and periodically performing the discards list maintenance.
16 . The computer program product of claim 15 , wherein determining the poolability further comprises: locating the requested SQL statement in a poolability overrides list; based on there being a match in the poolability overrides list, locating a previous request count paired with the requested SQL statement in the poolability overrides list; pairing the match in the poolability overrides list with the current request count; based on the current request count minus the previous request count being less than a threshold, and there being a poolability hint in the requested SQL statement, removing the match from the poolability overrides list, incrementing an unwanted discards count, and incrementing a maximum SQL statement pool size; and locating the requested SQL statement in a SQL statement pool.
17 . The computer program product of claim 15 , wherein providing the requested SQL statement to the requestor further comprises: locating the requested SQL statement in a SQL statement pool; and based on the requested SQL statement being in the SQL statement pool, and there being a poolability hint in the requested SQL statement, and a maximum SQL statement pool size: discarding a least recently used SQL statement from the SQL statement pool, and adding the least recently used SQL statement to the discards list.
18 . The computer program product of claim 15 , wherein periodically performing discards list maintenance further comprises: based on the current request count modulo the threshold being zero: for each entry in the discards list, removing the entry based on a difference between the current request count and a request count when the entry was added to the discards list, and adding the removed entry to the poolability overrides list.
19 . The computer program product of claim 15 , wherein providing the requested SQL statement to the requestor further comprises; receiving the requested SQL statement from the requestor; and based on the requested SQL statement being poolable adding the requested SQL statement to a SQL statement pool.
20 . The computer program product of claim 15 , wherein the proximity is the current request count minus a request count when the matching entry was discarded.

Description

BACKGROUND The disclosure relates generally to an improved computer system and more specifically to managing statement pool churn. Whether it is done directly by the application or indirectly by other layers of software in between, data access applications typically involve running parameterized structured query language (SQL) statements against a database. Many of the same SQL statements tend to be executed over and over again, often with differing parameters. Other SQL statements are executed infrequently, and usage patterns can vary over time. Pooling and reusing SQL statements would improve performance. However, pooling too many statements can lead to consuming too much resource, particularly memory, especially when combined with connection pooling. Some performance tuning options, such as capping statement pools, may result in frequently used statements being discarded in favor of those that are run less frequently. It would be advantageous to automatically identify SQL statements that cause churn so that they can be kept out of statement pools, thereby achieving lower total statement pools sizes without the byproducts of churn and excessive memory consumption. SUMMARY According to one illustrative embodiment, a computer implemented method is provided. In response to receiving a request for a structured query language (SQL) statement from a requestor, a current request count is incremented. A matching entry in a discards list is located, whereby the match comprises, a discarded SQL statement equaling the requested SQL statement. Based on there being the matching entry, the matching entry is removed from the discards list. A proximity between the current request count and a previous request count is computed. A threshold is created based on a configurable multiple of a maximum SQL statement cache size. The poolability of the requested SQL statement is determined. The requested SQL statement is returned to the requestor. Periodically, maintenance is performed on the discards list. Embodiments are further directed to computer systems, servers and computer program products having substantially the same features as the above-described computer implemented method. BRIEF DESCRIPTION OF THE DRAWINGS FIG. 1 is a block diagram of a computing environment in which illustrative embodiments can be implemented; FIG. 2 is a high-level diagram of automatically adjusting statement pool size and adding poolability overrides, in accordance with an illustrative embodiment; FIG. 3 is a diagram illustrating determining poolability in accordance with an illustrative embodiment; FIG. 4 is a diagram illustrating obtaining a statement in accordance with an illustrative embodiment; FIG. 5 is a diagram illustrating an application using and returning the statement obtained in FIG. 4 in accordance with an illustrative embodiment; and FIG. 6 is a diagram illustrating periodically cleaning up discarded statements in accordance with an illustrative embodiment. DETAILED DESCRIPTION Various aspects of the present disclosure are described by narrative text, flowcharts, block diagrams of computer systems and/or block diagrams of the machine logic included in computer program product (CPP) embodiments. With respect to any flowcharts, depending upon the technology involved, the operations can be performed in a different order than what is shown in a given flowchart. For example, again depending upon the technology involved, two operations shown in successive flowchart blocks may be performed in reverse order, as a single integrated step, concurrently, or in a manner at least partially overlapping in time. A computer program product embodiment (“CPP embodiment” or “CPP”) is a term used in the present disclosure to describe any set of one, or more, storage media (also called “mediums”) collectively included in a set of one, or more, storage devices that collectively include machine readable code corresponding to instructions and/or data for performing computer operations specified in a given CPP claim. A “storage device” is any tangible device that can retain and store instructions for use by a computer processor. Without limitation, the computer readable storage medium may be an electronic storage medium, a magnetic storage medium, an optical storage medium, an electromagnetic storage medium, a semiconductor storage medium, a mechanical storage medium, or any suitable combination of the foregoing. Some known types of storage devices that include these mediums include: diskette, hard disk, random access memory (RAM), read-only memory (ROM), erasable programmable read-only memory (EPROM or Flash memory), static random access memory (SRAM), compact disc read-only memory (CD-ROM), digital versatile disk (DVD), memory stick, floppy disk, mechanically encoded device (such as punch cards or pits/lands formed in a major surface of a disc) or any suitable combination of the foregoing. A computer readable storage medium, as that term is used in the pre