Granted Patent B2

US 12,373,431 · App. 18/491,087 · Granted Jul 29, 2025

Inventors: Jain Sachinkumar (Shrewsbury, MA); Kapish Kumar (Bangalore, IN); Gaurav Mehrotra (Pune, IN)

Assignee: International Business Machines Corporation

G06F16/24545G06F16/2462

Abstract

Claims (69)

1. A computer-implemented method of planning and executing queries in a target database, the method comprising:

obtaining, by one or more processors, a user query;

selecting, by the one or more processors, just in time (JIT) statistics relevant for use by a cost optimizer to generate a plan file for executing the user query, wherein the selecting comprises:

determining, by the one or more processors, based on JIT metadata in a JIT statistics cache, if JIT statistics generated responsive to an earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

based on determining that the JIT statistics generated responsive to the earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query, selecting the JIT statistics generated responsive to the earlier event as the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query;

utilizing, by the one or more processors, the cost optimizer to generate the plan file, wherein the plan file is based on the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

executing, by the one or more processors, the user query in the target database based on the plan file, wherein the earlier event is generation of JIT statistics by a machine learning model and the generation by the machine learning model comprises:

determining, by the one or more processors, based on a transactional table, that a threshold number of JIT statistics have been generated over a given time period;

analyzing, by the one or more processors, the JIT statistics cache and transactional table, to detect patterns in user queries within the given time period;

training, by the one or more processors, the machine learning model with the detected patterns;

applying, by the one or more processors, the machine learning model to predict a set of JIT statistics to be generated at a future time based on anticipated user queries triggering one or more new JIT statistics queries;

generating, by the one or more processors, the set of JIT statistics; and

updating, by the one or more processors, the JIT statistics cache with JIT metadata of the one or more new JIT statistics queries.

2. The computer-implemented method of claim 1 , wherein selecting the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query further comprises:

based on determining that the JIT statistics generated responsive to the earlier event are not the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query, generating, by the one or more processors, based on the user query, the JIT statistics relevant for use by a cost optimizer, wherein the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query are relevant to one or more tables in the target database referenced in the user query, wherein generating the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprises executing one or more JIT queries in the target database and obtaining the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query as results and caching metadata of the results in the JIT statistics cache.

3. The computer-implemented method of claim 2 , wherein the determining if JIT statistics generated responsive to an earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprises:

comparing, by the one or more processors, aspects of the user query to the JIT metadata to determine if a previously executed user query that for which selecting the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprised generating the JIT statistics is similar to the user query.

4. The computer-implemented method of claim 3 , wherein the comparing comprises:

determining, by the one or more processors, one or more JIT queries triggered by the user query; and

checking, by the one or more processors, if the JIT metadata comprises the one or more JIT queries triggered by the user query, wherein based on the JIT metadata comprising the one or more JIT queries triggered by the user query, the previously executed user query is similar to the user query.

5. The computer-implemented method of claim 1 , wherein the earlier event is selected from the group consisting of: execution of one or more JIT queries based on obtaining an earlier user query and generation of JIT statistics by a machine learning model.

6. The computer-implemented method of claim 1 , wherein the analyzing comprises applying a machine learning algorithm to learn the patterns.

7. The computer-implemented method of claim 2 , wherein the determining comprises comparing aspects of the user query to the JIT metadata to determine if JIT statistics generated by a machine learning model in advance of obtaining the user query were generated based on one or more queries similar to the user query.

8. The computer-implemented method of claim 7 , wherein the comparing comprises:

determining, by the one or more processors, one or more JIT queries executed by the machine learning model; and

checking, by the one or more processors, if the JIT metadata comprises the one or more JIT queries executed by the machine learning model, wherein based on the JIT metadata comprising the one or more JIT queries, the JIT statistics generated by the machine learning model in advance of obtaining the user query were generated based on one or more queries similar to the user query.

9. The computer-implemented method of claim 1 , wherein the JIT metadata comprises values selected from the group consisting of: timestamp, table name, object identifier, JIT statistics query, and count.

10. A computer system for planning and executing queries in a target database, the computer system comprising:

a memory; and

one or more processors in communication with the memory, wherein the computer system is configured to perform a method, said method comprising:

obtaining, by the one or more processors, a user query;

selecting, by the one or more processors, just in time (JIT) statistics relevant for use by a cost optimizer to generate a plan file for executing the user query, wherein the selecting comprises:

determining, by the one or more processors, based on JIT metadata in a JIT statistics cache, if JIT statistics generated responsive to an earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

based on determining that the JIT statistics generated responsive to the earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query, selecting the JIT statistics generated responsive to the earlier event as the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query;

utilizing, by the one or more processors, the cost optimizer to generate the plan file, wherein the plan file is based on the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

executing, by the one or more processors, the user query in the target database based on the plan file, wherein the earlier event is generation of JIT statistics by a machine learning model and the generation by the machine learning model comprises:

determining, by the one or more processors, based on a transactional table, that a threshold number of JIT statistics have been generated over a given time period;

analyzing, by the one or more processors, the JIT statistics cache and transactional table, to detect patterns in user queries within the given time period;

training, by the one or more processors, the machine learning model with the detected patterns;

applying, by the one or more processors, the machine learning model to predict a set of JIT statistics to be generated at a future time based on anticipated user queries triggering one or more new JIT statistics queries;

generating, by the one or more processors, the set of JIT statistics; and

updating, by the one or more processors, the JIT statistics cache with JIT metadata of the one or more new JIT statistics queries.

11. The computer system of claim 10 , wherein selecting the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query further comprises:

based on determining that the JIT statistics generated responsive to the earlier event are not the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query, generating, by the one or more processors, based on the user query, the JIT statistics relevant for use by a cost optimizer, wherein the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query are relevant to one or more tables in the target database referenced in the user query, wherein generating the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprises executing one or more JIT queries in the target database and obtaining the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query as results and caching metadata of the results in the JIT statistics cache.

12. The computer system of claim 11 , wherein the determining if JIT statistics generated responsive to an earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprises:

comparing, by the one or more processors, aspects of the user query to the JIT metadata to determine if a previously executed user query that for which selecting the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query comprised generating the JIT statistics is similar to the user query.

13. The computer system of claim 12 , wherein the comparing comprises:

determining, by the one or more processors, one or more JIT queries triggered by the user query; and

checking, by the one or more processors, if the JIT metadata comprises the one or more JIT queries triggered by the user query, wherein based on the JIT metadata comprising the one or more JIT queries triggered by the user query, the previously executed user query is similar to the user query.

14. The computer system of claim 10 , wherein the earlier event is selected from the group consisting of: execution of one or more JIT queries based on obtaining an earlier user query and generation of JIT statistics by a machine learning model.

15. The computer system of claim 10 , wherein the analyzing comprises applying a machine learning algorithm to learn the patterns.

16. The computer system of claim 11 , wherein the determining comprises comparing aspects of the user query to the JIT metadata to determine if JIT statistics generated by a machine learning model in advance of obtaining the user query were generated based on one or more queries similar to the user query.

17. The computer system of claim 16 , wherein the comparing comprises:

determining, by the one or more processors, one or more JIT queries executed by the machine learning model; and

checking, by the one or more processors, if the JIT metadata comprises the one or more JIT queries executed by the machine learning model, wherein based on the JIT metadata comprising the one or more JIT queries, the JIT statistics generated by the machine learning model in advance of obtaining the user query were generated based on one or more queries similar to the user query.

18. A computer program product for planning and executing queries in a target database, the computer program product comprising:

one or more computer readable storage media and program instructions collectively stored on the one or more computer readable storage media readable by at least one processing circuit to:

obtain, by the one or more processors, a user query;

select, by the one or more processors, just in time (JIT) statistics relevant for use by a cost optimizer to generate a plan file for executing the user query, wherein the selecting comprises:

determine, by the one or more processors, based on JIT metadata in a JIT statistics cache, if JIT statistics generated responsive to an earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

based on determining that the JIT statistics generated responsive to the earlier event are the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query, select the JIT statistics generated responsive to the earlier event as the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query;

utilize, by the one or more processors, the cost optimizer to generate the plan file, wherein the plan file is based on the JIT statistics relevant for use by the cost optimizer to generate the plan file for executing the user query; and

execute, by the one or more processors, the user query in the target database based on the plan file, wherein the earlier event is generation of JIT statistics by a machine learning model and the generation by the machine learning model comprising additional program instructions collectively stored on the one or more computer readable storage media readable by the at least one processing circuit to:

determine, by the one or more processors, based on a transactional table, that a threshold number of JIT statistics have been generated over a given time period;

analyze, by the one or more processors, the JIT statistics cache and transactional table, to detect patterns in user queries within the given time period;

train, by the one or more processors, the machine learning model with the detected patterns;

apply, by the one or more processors, the machine learning model to predict a set of JIT statistics to be generated at a future time based on anticipated user queries triggering one or more new JIT statistics queries;

generate, by the one or more processors, the set of JIT statistics; and

update, by the one or more processors, the JIT statistics cache with JIT metadata of the one or more new JIT statistics queries.

Assignments (1)

Continuity (1)

References Cited (15)